Slow Loading of Data from SQL DB via QlikView

[#1]

Hi everyone,

I'm new to QlikView, so apologies if this question seems to reflect a lack of understanding. I run a SQL process through an application that connects to a database and populates various tables on that database (based on a set of queries) with data from a source table.

Recently, I've used QlikView to connect to the source table, but when trying to perform the first query (selecting all records before a certain date), it takes a very long time to return/load the result, which consists of about 3million+ records. From this I assume QlikView is not really designed to load very large datasets, which must therefore be broken down in stages on the SQL server, before import of, say, several thousand records into the tool.

Please can anyone confirm if this is the right assumption?

Thank you

[kaushiknsolanki]

Hi,

     I guess you need to understand the process of QlikView data fetching before arriving at any decision.

     When you fire query from qlikview on database to get the data, which is big in size following things makes it time consuming.

     1. The bandwidth of your database to through big data.

     2. Number of column you are trying to fetch.

     3. If the columns you are fetching is Text Columns.

     Apart from this,

     While fetching the data QlikView tries to compress it, which takes a bit time.

     For more understanding on QlikView architecture you can visit following links.

     http://community.qlik.com/thread/28340

Regards,

Kaushik Solanki

[flipside]

Hi vcoder1,

Welcome to the world of Qlikview!

The number of records you mention isn't unusual for Qlikview, indeed it can handle a lot more, however the actual number of rows will depend on various things such as number of columns, column datatypes, system specification (eg RAM) etc., very similar to considerations with most databases but focus is more on RAM than disk storage. There is plenty of advice and links to documentation on this site.

The slow return of data you describe can be affected by other factors such as what type of driver you are using, data contention or source data table schema.  Your SQL process running through an application will probably have efficient indexes set up to speed up queries.  It's difficult to pinpoint a problem but it may be as simple as adding your own indexes on the date column you are using in the WHERE clause of your query.  Of course if this is a 3rd party application table you could affect other processes so generally it is wise to ask them to set up a scheduled data transfer to another table for you to use.

flipside

[#4]

Thank you both for your replies. I do have many columns, and with different data types (including text). When I just restrict to 3 or 4 columns containing only numerical data, the speed of loading is comparable or faster than through SQL.

Now that I know the limitations, I'll experiment and look for ways to speed things up.

Many thanks,

vcoder

[#5]

Thank you both for your replies. I do have many columns, and with different data types (including text). When I just restrict to 3 or 4 columns containing only numerical data, the speed of loading is comparable or faster than through SQL.

Now that I know the limitations, I'll experiment and look for ways to speed things up.

Many thanks,

vcoder

[#6]

Thank you both for your replies. I do have many columns, and with different data types (including text). When I just restrict to 3 or 4 columns containing only numerical data, the speed of loading is comparable or faster than through SQL.

Now that I know the limitations, I'll experiment and look for ways to speed things up.

Many thanks,

vcoder

[#7]

Thank you both for your replies. I do have many columns, and with different data types (including text). When I just restrict to 3 or 4 columns containing only numerical data, the speed of loading is comparable or faster than through SQL.

Now that I know the limitations, I'll experiment and look for ways to speed things up.

Many thanks,

vcoder

[#8]

Hi,

New to the Forum , but I have had this issue some of the pointers that helped were

1) to set the Packet Size according to what the SQL server, Network and your host server can handle.

For me Packet Size=24576 worked best. (use ping from QV serevr to SQL server with packet size to get the best result )

2) I got a table created for the data I wanted to select woth Index on the fields that needed to be in the Where or join conditions.

I get about 60mill rows of data with 100 columns in under 3 hours daily

🙂

Best of luck.