topic How to minimise Data Load time in QlikView from SQL Table in QlikView

How to minimise Data Load time in QlikView from SQL Table

malimohammed — Wed, 03 Jun 2015 06:13:25 GMT

Hello,

We are doing data modelling for one of our clients. The data source is MSSQL server, we have about 10-15 tables in the data model for building dashboards.

The issue I'm facing is when I perform data reload one particular table takes more than 4minutes to load while others load in a fraction of seconds. Moreover, there is more data in the other tables compared to the table that is causing delay.

I tried to query the specified table in SQL which takes 5sec for the query to return all rows i.e; 11,000+ which is normal. Here are some details of the table.

Foreign Keys: 83

Indexes: 7

Columns: 106

Can anyone highlight if the table structure that is causing the delay in qlikview load? How do I minimise and reduce the data load time?

I would appreciate if I can get some help in resolving the issue.

Re: How to minimise Data Load time in QlikView from SQL Table

jonathandienst — Wed, 03 Jun 2015 06:19:43 GMT

This is really a SQL question, rather than a QV question, and you may get more expert opinion on how to optimise your SQL query on a SQL forum.

I assume that the slow loading query has a number of joins between large tables. Are all the tables in the join properly indexed? Have the index statistics been properly maintained and updated? Is that date sorted or are you using select distinct and/or grouping? Have you looked at the query plan using SQL Management Studio? Have the tables got clustered indices.....

Re: How to minimise Data Load time in QlikView from SQL Table

Anonymous — Wed, 03 Jun 2015 06:52:31 GMT

Hi ,

You can try increasing the packet size in the SQL connection string by default it shows less package size number you can increase it to maximum

It Helped me to reduce the loading time

Regards

Harsha

Re: How to minimise Data Load time in QlikView from SQL Table

malimohammed — Wed, 03 Jun 2015 07:44:10 GMT

Thanks for your response. The increase in packet size has decreased the load time to 19-sec from 4-minutes. I have increased the packet size to 16,383 which is maximum I assume. What is the packet size in your case?

I appreciate your help. Thanks mate.

Re: How to minimise Data Load time in QlikView from SQL Table

malimohammed — Wed, 03 Jun 2015 07:45:32 GMT

Thanks for your feedback Jonathan..

Re: How to minimise Data Load time in QlikView from SQL Table

Anonymous — Wed, 03 Jun 2015 08:28:46 GMT

Try using 65535

Regards

Harsha