Qlikview fetching slow data with sql server OLEDB connection

[AC187]

Hi All,

recently we have migrated from Qlikview 11 to qlikview 12 latest version.

We have got our UK database and Qlikview servers in both UK and HK regions.

while we do OLED connection from qlikview 11.2 to UK databaser server the fetch speed is very high i.e almost 20 million records are fetched in 3-4 mins.

The same  OLEDB connection made via sql server native 11.0 client connection works with Qlikview 12 wherever we connect UK database server with UK qlikview server (20 million rows in 3 mins or less)

However wherever we try connecting UK database with that of HK qlikview server by OLEDB with sql server native 11.0 client connection, the data speed is too low where it takes almost 50-55 mins to fetch the data (20 million rows) . For this we have tried with all recommendations on the community related to setting up Packet size in Oledb connection string with various values 4096,8192,24576,65536 and many more .. however nothing has really benefited ,

can anyone suggest what more we can do to make this working  to get same speed what we have with UK database server and UK qlikview server??

[Brett_Bleess]

I would suggest opening a case with Microsoft SQL support or check their Community, they are the experts on their drivers and should be able to suggest if there are any settings to improve WAN related connections, but I would venture the timeframe you are getting is pretty reasonable considering the delta between the locations.  Sorry I do not have better news for you.  The more likely solution would be to reload from HK and create a QVD file you push over to your UK site for the UK jobs to use...

Regards,
Brett

[yogi_myd]

hi,

Can you add/increase the 'Fetchsize=1000000;' in the OLEDB Connection string? It gives better result :).

Note: Try in your Dev first and go for prod, usually in the production(sql server) memory partioning and load has been handled by DBA.

Regards

Yogesh.

[AC187]

Hi it throws error if we increase the fetchsize so much, it depends on bit of operating system

[yogi_myd · Accepted solution]

Hi,

Can you share the Error ? 

start with a lower fetchsize, this is from oracle doc

FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

Based on your DB properties it reacts.

regards

Yogesh.

[Brett_Bleess]

See related post:

https://community.qlik.com/t5/QlikView-Connectors/Qlikview-12-fetching-slow-data-from-SQL-server/m-p...

Regards,
Brett

[AC187]

This reDirects me to my this post only!!!  

[Mr_H_Huang]

I have the similar issue and I luckily found a solution. The issue was that we were experiencing very slow reload in the data extract qvw files studdently on a date and after. Sometimes the error appears in event log showing "fail to open in write mode". By reload the extract qvw in server and manually reload you may receive different errors or one is stop, on went through.

By changing the pack size it did help a bit like 10% improvement but not massively. 

We use include to load a connection statement in order to fetch data from database with SQL.

I have changed a syntax for my database Connection from "OLEDB CONNECT TO [Provider=SQLOLEDB..." to "OLEDB CONNECT32 TO [Provider=SQLOLEDB..." then it works 4x 5x faster than without forcing it to 32 bit. I hope this helps anyone meets the similar issue. You may change between 32bit and 64bit to see if it is work for your case. Thanks.