topic Re: Extremely slow Excel export in QlikView

Extremely slow Excel export

Jason_Michaelides — Wed, 10 Oct 2012 08:59:23 GMT

Hi all,

I have a pretty large data model (almost 200m rows across over 100 fields, many of which are financial figures with high levels of distinct values). The performance in charts etc is generally OK (I've used all best practice for the model and expressions, although I'm going to try replacing some of the set analysis for flags) - my problem is the Excel export.

My user-base are primarily accountants. It'll take time to shift the culture to realise they can do most things in QV and don't need to use Excel so much, so in the meantime I need to provide decent export functionality. My issue is I just don't understand why the Excel export is performing so shockingly slowly. I may have 200m rows of data, but if my chart only has 10k rows and maybe 10 columns, it still takes around 10-15 minutes to export the chart.

I am using v11 SR2 and the issue is there whether I export from the desktop client or the web client.

Am I missing something? It almost feels like QV is calcaulating the chart for the full number of possible rows and expressions, before deciding to only export 10k of the rows. (The chart has over 50 expressions, but they are conditionally enabled/disabled and this issue is seen with only 5 of them enabled.)

I've tried using various macros instead of the XL button on the cpation bar to no avail,

Has anyone else had this issue and have you resolved it!?

Thanks,

Jason

Re: Extremely slow Excel export

Thu, 18 Oct 2012 14:03:36 GMT

Thanks for posting this problem, I am having the same issue. The functionality of conditional dimensions and expressions is great and our clients need it for their reports but exports or copy of full table data is impossibly slow - I am attemting to revert back to macros and other techniques for the functionality of adding selected expressions (and/or dimensions).

Please inform us if there is progress on this point.

Thank you.

Re: Extremely slow Excel export

Jason_Michaelides — Thu, 18 Oct 2012 14:05:40 GMT

I have raised a ticket with QlikTech. I received a call yeaterday about it but was unable to take it - will get an update hopefully today.

Re: Extremely slow Excel export

Jason_Michaelides — Tue, 23 Oct 2012 13:03:47 GMT

All,

I am delighted to report that QlikTech have a customer patch for this recognised bug in v11 and for us it completely resolved the issue.

Unbelievable that a major new v11 feature (conditional expressions) can be so buggy and even more so that by SR2 it still is not fixed properly (it is supposedly improved in SR2 but I saw no real improvement).

So, if you are having issues with performance when using conditional expressions in tables (straight and pivot), locking scroll-bars on the same charts, and extremely slow export to Excel, then this patch may be the answer for you.

Ask QT support for build 11.0.11419.0

Hope this saves you some of the crazy amounts of time I've wasted on this.

Jason

Re: Extremely slow Excel export

Jason_Michaelides — Tue, 23 Oct 2012 13:30:17 GMT

I should just mention I haven't finished thorough testing and being a customer patch it is not warranted like a formal release...

Re: Extremely slow Excel export

Anonymous — Tue, 23 Oct 2012 14:57:59 GMT

Jason,

Thank you for the news.

I just got the patches, and was trying to update my QV Desktop, but simple double-click on the msp file didn't work. Could you please tell how to apply the patches?

Thanks,

Michael

Re: Extremely slow Excel export

Jason_Michaelides — Tue, 23 Oct 2012 15:04:50 GMT

The .msp file worked fine for me, Michael. I updated the x64 version - are you using 64-bit or 32-bit? I haven't tried the 32-bit.

Jason

Re: Extremely slow Excel export

Anonymous — Tue, 23 Oct 2012 15:08:44 GMT

It is 64. My current version is 64 as well, QV11 SR2

When I dbl-clik to apply, getting a message from Wndows installer "The upgrade patch cannot be installed ..."

Tried command line, same result.

OK, I have a theory, will try...

Re: Extremely slow Excel export

Jason_Michaelides — Tue, 23 Oct 2012 15:09:53 GMT

I was upgrading from SR2 – you probably have to have that installed first.

EDIT: Sorry - misread yor post! You already have that running...

Re: Extremely slow Excel export

Anonymous — Tue, 23 Oct 2012 15:19:29 GMT

Figured out.

I have multiple versions of QV installed. If install QV11, it removes QV10, so hadto re-install 10 after 11. That's why the patch didn't work. So, I uninstalled 10, re-installed 11, and ran the patch.

Thank you,

Michael

Re: Extremely slow Excel export

Jason_Michaelides — Tue, 23 Oct 2012 15:20:41 GMT

Cool. Let us know if it works for you too.

Re: Extremely slow Excel export

Anonymous — Tue, 23 Oct 2012 15:27:16 GMT

It is perfect!

I have two tables, one "normal" and another with conditional expressions/dimensions. Number of rows in my test is about 12,000.

In the standard SR2, "normal" table goes to excel in 7 seconds, "conditional" with the same set of dimensions/expressions takes 6 minutes.

With the patch, both tables are exported in 6 seconds. Quite a difference 🙂

Re: Extremely slow Excel export

Thu, 25 Oct 2012 12:58:40 GMT

Here are the patches:

After having reviewed your request we would suggest the following solution:

The link to the patches has been removed by Moderator.

You have to go through support to be able to receive a patch.

Bill Britt

Re: Extremely slow Excel export

Wed, 14 Nov 2012 16:19:06 GMT

The patch you have posted is the same I received from QV support, but unfortunately it cannot be installed on SR2 with Upgrade1 that was publicly released on Nov.9. I have installed the SR2 Upgrade1 and try now to install the patch (as it turned out that this Upgrade doesn't solve the Enable Conditional scroll freeze or slow excel export) but it gives me the message that I have a newer version (The Upgrade is 11.00.11426) so it won't install the patch. Do you know if there is a patch-fix that can be installed on the newest Upgrade. I'll ask the support guys as well.

Thanks.

Re: Extremely slow Excel export

Jason_Michaelides — Wed, 14 Nov 2012 16:20:33 GMT

No idea I'm afraid.

Re: Extremely slow Excel export

Thu, 15 Nov 2012 10:56:46 GMT

A colleague just told me that QlikTech doesn't make patches (that will stay integrated with next versions/Updates) but what they do is Customer Releases only - which give a specific customer the needed functionality for the installed version, but if you Upgrade to the next version/SR/Upgrade the functionality of the Customer Release will be lost. I don't know if this is so and still am waiting for an answer from the support. Have you installed the latest Upgrade and did you lose the Customer Build 11419 functionality after that (if you have done the Upgrade)?

Re: Extremely slow Excel export

Jason_Michaelides — Thu, 15 Nov 2012 11:01:37 GMT

We haven't upgraded but not by design. Thank you for this warning!

Re: Extremely slow Excel export

erichshiino — Mon, 19 Nov 2012 19:39:08 GMT

Jason,

Do you have a bug ID for your original problem?

I checked the release notes here ( http://community.qlik.com/blogs/supportupdates/2012/11/09/version-11-service-release-2-update-1-is-now-available--build-110011426) and there are many bugs related to excel export.

I'd like to make sure before installing or requesting a patch for version 11.00.11426

Best Regards,

Erich

Re: Extremely slow Excel export

Tue, 20 Nov 2012 14:58:06 GMT

Hi Erich,

The slow export to excel from tables with Enable Conditional is Bug No 48736 , which is found in line 10 of Table 8 on p.23 in the Update 1 doc. The QV patch policy states that:

Q –I’m on a Patch but a newer Update/Service Release is available. What do you recommend?

A – Updates & Service Releases are cumulative of Patches therefore we recommend that you

move to the latest Update/Service Release as soon as possible. (from QlikView Release Management Policies)

so this would mean that the build 11419 should be integrated in SR2 Update 1; however, as I said earlier I don't see much difference after I installed the Update1 in the horizontal scroll freeze or slow export to excel from such tables. I am still experimenting and testing though, so I'll keep you informed if I see any improvemnet.

Re: Extremely slow Excel export

Wed, 21 Nov 2012 12:37:01 GMT

We have updated the server but forgot to update desktop as well, so it's my mistake. With ver. 11.00.11426 (the Update1 to SR2) Charts with Enable Conditional perform much better and faster - both in calculation and export to excel time. The Update seems for now to solve all our concerns, though I have to test further to compare with ordinary charts' export and calculation times.