topic Re: Long load times - can't replicate in QlikView

Long load times - can't replicate

Wed, 12 Nov 2014 16:40:51 GMT

Hi All,

I have a load script that deals with large amounts of data. It runs weekly for an incremental load process.

Recently, it has been taking an extremely long time to load. I have analysed the logs and found the points in the process that are taking the longest and I'm trying to replicate them in isolation by manually reloading certain parts in a stand-alone QVW.

One step in particular takes 1day 1hour 28mins. This simply reads a large QVD with minimal changes then does an inner join with another QVD. I have loaded the first QVD which took only 38mins using the same portion of code. Then I thought perhaps the time related to the inner join, so I ran this in isolation also and that took a total of 42 mins using the same code as the main script. So obviously there is something else at play here.

The regular reload is kicked off using command-line (qlikview /r xx.qvw).

Has anyone experienced this type of behaviour before? My understanding is that synthetic keys aren't built until the end of a load process, so that is not the problem because all tables are dropped once dealt with and stored out. I've also made sure I hadn't set up any regular processes that might be having an impact on resources.

Any ideas would be greatly appreciated!

Cheers,

GPC

Re: Long load times - can't replicate

Gysbert_Wassenaar — Wed, 12 Nov 2014 17:55:56 GMT

My guess would be that the join (or joins) are the cause. Joining tables can be resource intensive and will take longer the more records and tables are involved.

Re: Long load times - can't replicate

Thu, 13 Nov 2014 10:47:27 GMT

Hi Gysbert,

Thanks for your response. However, I have replicated the same join in isolation of the full ETL process and it takes only 42 mins as opposed to 1 day 1 hour 28 mins. So I'm completely confused!

Re: Long load times - can't replicate

Gysbert_Wassenaar — Thu, 13 Nov 2014 10:50:46 GMT

Did you monitor the ram usage of the server? If it runs out of physical ram and needs to use the page file it will slow down to a crawl. Running only the part of the script that involves the join may not hit that limit. If the app has already loaded a lot of data and then needs to join tables it may hit that limit.

Re: Long load times - can't replicate

Thu, 13 Nov 2014 12:17:28 GMT

Good idea - I'll set up a performance monitor log and see if I can see anything going on.

Re: Long load times - can't replicate

sasiparupudi1 — Thu, 13 Aug 2015 13:15:58 GMT

hi Gareth

Have you managed to find a solution to your problem? if yes, could you please explain?

Sasi