Loading times from MS SQL Server

[#1]

Hello,

Does anyone have any guidelines how long time it should take to load e.g. 1 million records from a table with let's s 30 columns from a MS SQL Server database?

I am trying to pull like five million records from a sql server but 1.5 million records takes approximately 2 hours and 40 minutes to pull which seems to much. The total filesize of the qvd is less than 350 mb.

My script is pretty straight forward with just one where clause which states that the fiscal year has to be >2007

Worth noting is that I only have 11mbit between the qv server and the actual sql server.

My gut feeling tells me it should be way quicker than that but at the same time I don't know anything about this so anyone has any idea what could be wrong or is this how quick it will be?

[prieper]

When the server is within your network you should be able to load betw 500000 and 1 Mio records per minutes - with a simple query and WHERE-clause on indexed field.

Peter

[#3]

I have not had the opportunity to implement this yet ... but something for you to try.

You can try use WITH (NOLOCK) in your SQL SELECT. However you should only use this when you know for certain that no one making any writes to the DB at the tim you are loading your Qlikview model.

[#4]

@Peter

If I try to pull records inside the network and it is really slow, do you have any ideas where the typical problem would be? Would it most likely be because of server settings or because of infrastructure?

I need some advice on how to start troubleshoot and what IT-technician I should bother first at my work 🙂

[Anonymous]

The speed straight depends on network speed and server hardware performance. Also software does matter - 32bit or 64bit.

You can check technical requirements for hardware in QlikView Reference Manual both for Desktop application and Server

[#6]

First problem figured out now... The server was strapped somehow to 10mbit in the switch even though we are running gbit switchs. This probably explains why I was reloading so slow even inside the network on site.

[#7]

Another question:

What is the difference performance wise between pulling data directly from tables versus pulilng it from a view?

/F

[prieper]

Would say: Depending ...
I have learned once that it is a good practice, never to load data from even a single table, but only via views. Thus there should be nearly no difference in time, when loading from a table or from a view, if you refer to just this table. It may matter a lot, if you have a complicated join over several tables.
In such cases we experienced it much faster to download single tables and to join them lateron in QlikView in the individual applications.
My advice would be: try both and measure the times and then make your own decision.

Peter

[#9]

Ok. Thanks for your advice, I will do some benchmarking and see what turns out to be the fastest way.

/F