Explain below property

[durgabhavani]

Hi All,

Can anyone explain clearly about fetch size property in qlikview.

I need to simulate application (increasing the load of records into the qlikview) by using fetchsize property in oledb connection string. Please provide information and how to implement it.

Thanks,

Durga

[swuehl]

You might be better off asking this question in a DB specific forum, especially if you are going to 'simulate' something performance wise.

Depending on your data source, you can run a simple google query and then you should find something like:

• 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.

http://docs.oracle.com/cd/B10501_01/win.920/a95498/using.htm#1004599

[maxgro · Accepted solution]

Fetch size isn't a qlikview property. You define it in the connection to the db (above a 32 bit conn to an oracle db); you can find some info googling for oledb fetch size or (better) asking to your database admin.

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Password=xxxxx;Persist Security Info=True;User ID=xxxxxxxx;Data Source=xxxxx;Extended Properties="FetchSize=nnnn"];

When I used it (with Oracle db, don't think you can use it for every dbms) I just try (example multiply fetch size, reload some million of rec and look at the improvement, repeat, ....); I have to admit that, in some cases, the improvement was incredible (50% of the initial load time) and this just changing a parameter (fetch size) in the oledb connection.

[durgabhavani]

Hi Massimo,

Thanks for reply! When i am using fetch size i am getting unbelievable results in load time. But some time i am not pulling all records from database. I am getting confused with this, where the problem is occuring and how fetchsize working in pulling the records.

Suppose I need to pull 3,456,264 records from oracle db with fetchsize=500000, then it is not pulling the complete records.

can you tell me how can we pull records?

thanks,

Durga

[maxgro]

500000 seems to me a  big (too big?) setting

I wuold start with the default (did you read swuehl lnk?) and increase (x 2, x 2, x 2) until you see a substantial reduction of load time and you always get your 3456264 records. 

[Peter_Cammaert]

Stefan, any idea why your link shows as 'unlinked'? If you're still modifying URLs, there's no need to. You're exempt from moderation.

[swuehl]

If you're still modifying URLs, there's no need to. You're exempt from moderation.

Yes, that's what I usually do with external links.

Thanks for the update that I don't need to do this anymore

[durgabhavani]

Sorry for late response due to multiple tasks.
I read the link provided by swehl, as per my understanding if i use default fetchsize time must decrease but here the time is increasing if fetchsize is decreasing. (here it pulls completes records from DB).

Suppose if i increase fetchsize load time is decreasing, here (it doesnot pull the complete records from DB).

Please advice and find my results

Number Of Records Pulled into Qlikview from database	Elapsed Time (HH:MM:SS) Fetch Size (Default) (we use Fetch Size=" ") in extended properties	Fetch Size (100000)	Fetchsize 500000
3,109,040	1:03:09	0:16:12	0:26:58
4,508,108	1:32:19	0:19:16	0:25:09

Please advice

Thanks,

Durga

[maxgro]

AFAIK

fetchsize increase --> time decrease.

For default (100) or no fetch size you get the worst time.

There is probably a limit to the increase in fetchsize, it may be due to network, database, server; so I usually repeat some test increasing the fetch size until I see reductions of fetch time. In your case it seems your best fetchsize is between 100000 and 500000 (100000 is better). Try with a binary search between 100k and 500k.

I don't know the reason it dosesn't pull all the record from db (when fetchsize is 100, 100k, 500k?).

You can try the same thing with another tool (sqlplus) and check if there is the same problem

[Peter_Cammaert]

fetchsize is probably a parameter that controls the tradeoff between network/server overhead and transfer reliability. It has something to do with both server buffer management and network efficiency.

fetchsize = smaller -> more overhead = slower, but transmission errors are easily restarted and have lower impact on your DB platform.

fetchsize = larger -> less ovrhead = faster, but buffers may fail and transmission errors are very expensive as the transfer of the whole superblock must be restarted.

Are you able to figure out which records are skipped when you increase the fetchsize value? Is it always the same bunch?

Peter