Group By Statement taking a lot of time on the Server

[sunny_talwar]

Hi Experts:

I have created a qvw file which contains multiple group by statements. When I run the qvd on my local computer it takes around 1 hour (~30 minutes for loading data from Oracle and rest 30 for running the other part of script). But the same file is taking more than 5 hours on the server. Having checked the logfile each of the Group By Statement (aggregation) took around 30 minutes which was the sole reason it is taking this time.

Additional notes:

• There is no other task running at the same time
• Server def. has more resources than my computer
• Server is restarted once a week

Is there anything else I can check to see if that makes any difference. Cause I am not sure how else I can troubleshoot this problem.

Thanks for any ideas and suggestions.

Best,

Sunny

[marcus_sommer]

Hi Sunny,

one reason might be that certain settings like the variable format-settings are different and/or the server has a different timezone or something similar - which could cause that conditions maybe applied in where-clauses not work. In short: are the number of records from the group-queries in both cases the same?

Another difference could be that the server executed those parts single-threaded and your local machine could execute it multi-threaded (AFAIK all / most of group by loads are single-threaded ???) - you might need a look on the performance log (or look in taskmanager).

Hope it helped.

- Marcus

[sunny_talwar]

Thanks for a response Marcus.

To answer your first question, yes the number of records are exactly the same. I will have to do some more research on the topic of single-threaded vs. multi-threaded as I have very little to no knowledge about these.

[Peter_Cammaert]

sSunny,

"enough available server" resources may be misleading, as you may not know what the reload is using in reality because all traces of cpu and ram usage are lost once the reload process completes. A reload always runs outside of the QVS service. If your QVS runs off with about 80% of available RAM, then this means there is only 20% left for OS, other QlikView services and reload jobs. The most probable cause of scripts that slow down to a crawl is lack of memory and as a result an OS that starts paging to disk = slow.

To get a good idea about how the different stakeholders may be fighting for memory, start a scheduled reload on the server. On the server desktop, start Task Manager and select the "Performance"-tab. Then go away and do something else for about half an hour (tables will be reloaded from Oracle). As soon as you think the first GROUP BY operation starts in memory, observe memory usage in Task Manager (and optionally the distribution of memory between QVS and QVB processes in "Processes"-tab.

If you don't feel like spending some time watching Task Manager, you can configure Performance Monitor (available on every Windows Server) to trace cpu and memory usage during a period of time, and store it in a (binary) log. Performance Monitor itself allows you to view and investigate the logging details later on.

JOIN and GROUP BY are very costly with respect to cpu and server memory usage.

Best,

Peter

[marcus_sommer]

Yes it's a good point that qvs and qvb compete to the available RAM (because this I have had already task-errors).

- Marcus

[sunny_talwar]

Peter -

Thanks for your response. I am going to perform the things you have listed out and will get back to you with the findings.

Best,

Sunny

[#7]

QVS and QVD services running on the same host? your environment is clustered or single host ?

You may introduce new qvw between to your qvd loader to application and handle most of the group by function. or modify your qvd loader to handle some part of group by requirements. The other smart way is create the transformation qvd's incrementally so you can avoid the group by statements on the larger data sets.

Are you doing any data roll up on time based ? what is your data source ? Is it possible roll up the data out side qlikview ?

I had a same situation like reload task take almost 1 hr and I wrote some procedures to roll up the data in the oracle created new DM. or reload time reduced to less than 5 mins.