topic Re: Performance on load table from SQL Server in QlikView

Performance on load table from SQL Server

Sun, 04 May 2014 14:29:22 GMT

Hi,

I suppose to load all rows from SQLServer table, It's supposed to be ~5 Million Rows.

In the table there are 20 columns, and i need to pull 10 of them.

In a matter only of traffic, and overload on the system what is the efficient way to load:

Load* or load field1,Field2....

Thanks,

Jacob

Re: Performance on load table from SQL Server

tresB — Sun, 04 May 2014 14:52:01 GMT

Load

field1,

field2,

field10

From <> ;

This should be the better option as this loads less number of fields. And if you have doubt about optimized load, let me tell you, this still remains optimized.

Re: Performance on load table from SQL Server

maxgro — Sun, 04 May 2014 15:00:56 GMT

I think is better to limit the sql part to limit the network traffic

table:

sql

select

field1,

.....,

field10

from yoursqltable;

Re: Performance on load table from SQL Server

rbecher — Sun, 04 May 2014 17:19:49 GMT

One should never use SELECT * or LOAD * ...

Re: Performance on load table from SQL Server

Joseph_Musekura — Sun, 04 May 2014 17:28:25 GMT

Hi,

You can optimize your loading by

(1) Loading your data and save them in a QVDs file

(see QlikView reference manual C:\ProgramData\QlikTech\QlikView Documentation\Reference Manual).

(2) Then loading data from the QVDs.

Using QVDs will increase the performance

Regards

Re: Performance on load table from SQL Server

rbecher — Sun, 04 May 2014 18:46:59 GMT

..this doesn't speed up the data load from the database.

Re: Performance on load table from SQL Server

its_anandrjs — Sun, 04 May 2014 18:58:12 GMT

If you load fields and data directly from the SQL then you can try some thing like below

1. First opproach

LOAD

Field1,

Field2

..;

SQL SELECT *

FROM Categories;

DROP Fields Field10,Field11.....Field20;

2. Second you can try but comment then like //Field10, // //Field11 and so on and you can try a resident load also and then comment the fields on the load.

Re: Performance on load table from SQL Server

rbecher — Sun, 04 May 2014 19:20:24 GMT

But SELECT * loads the full data set from the database also...

Re: Performance on load table from SQL Server

maxgro — Sun, 04 May 2014 19:46:53 GMT

this is the reason I suggested to extract only the needeed 10 fields in the sql (database)

table:

sql

select

field1,

.....,

field10

from yoursqltable;

Re: Performance on load table from SQL Server

Mon, 05 May 2014 05:46:20 GMT

Thanks all for the approaches..

I'll further my question...

If there is where sentence involved: i.e. WHERE year = 2014

is still select the specific fields instead of select* is the way that cost less in terms of DB traffic?

Re: Performance on load table from SQL Server

tresB — Mon, 05 May 2014 05:50:26 GMT

With WHERE you reduce the number of rows(records) while using Load/Select Field1, Filed2 ....Field10 you reduce number of fields(attributes). Hope it helps.

Re: Performance on load table from SQL Server

Mon, 05 May 2014 05:53:06 GMT

I know it's reduce number of rows.. the question is about the DB is there a load difference between two approaches on his side..

Re: Performance on load table from SQL Server

rbecher — Mon, 05 May 2014 06:12:38 GMT

It depends. Usually a where clause reduces processing time on a database. This can lead into faster query results if you have an index on the column..

Re: Performance on load table from SQL Server

ashfaq_haseeb — Mon, 05 May 2014 06:57:16 GMT

Hi,

I would prefer you to use proper Index on the table to give you better performance.

Regards

ASHFAQ