Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Performance on load table from SQL Server

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

13 Replies
tresesco
Not applicable

Re: Performance on load table from SQL Server

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.

maxgro
Not applicable

Re: Performance on load table from SQL Server

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

table:

sql

select

     field1,

     .....,

     field10

from yoursqltable;

rbecher
Not applicable

Re: Performance on load table from SQL Server

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

Support
Support

Re: Performance on load table from SQL Server

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

rbecher
Not applicable

Re: Performance on load table from SQL Server

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

its_anandrjs
Not applicable

Re: Performance on load table from SQL Server

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.

rbecher
Not applicable

Re: Performance on load table from SQL Server

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

maxgro
Not applicable

Re: Performance on load table from SQL Server

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

table:

sql

select

     field1,

     .....,

     field10

from yoursqltable;

Not applicable

Re: Performance on load table from SQL Server

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?