Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

best practice: LOAD * SQL SELECT [field1,2,3..] or LOAD [field1,2,3..] SQL SELECT *?

I am rather new to Qlikview and I'm building some report, fetching data from a ODBC database.

In the name of efficiency and diminishing the workload for servers (and also incrementing the speed of fetching data), what is the best option when just certain fields (eventually with some criteria, e.g. just date=current year) are needed?

Option A

LOAD Order,

Item,

Date

WHERE Date > 20120101;

SQL SELECT *

FROM EUrope.Orders

Option B

Load *

SQL SELECT Order, Item, Date

FROM EUrope.Orders WHERE Date > 20120101;

In words, SQL-selecting everything, and then loading what needed, or SQL-selecting what needed and loading everything (where "everything" is just equal to the SQL selection)?

Logically, I would guess that Option B is more efficient, as you are just handling, from the very beginning, what you just need. But here on QlikCommunity I see many, many scripts that are more similar to Option A...

Many thanks for helping me in shedding some light on this (elementary but, I think, critical) topic!

8 Replies
rbecher
MVP
MVP

Hi,

never use SELECT * against a database. Only use the columns you need. So, LOAD * doesn't has any (negative) effect on QlikView.

- Ralf

Astrato.io Head of R&D
Miguel_Angel_Baeyens

Hi,

Definitely, a combination of both. Why?

A.

  • Using LOAD with each field allows you to control what you are saving into the QlikView document, i.e.: how the numbers or dates will look like, creating new fields using conditionals, doing some formating and so. The WHERE will usually perform just fine.
  • If the RDBM is a DWH and even more if it's the live transactional system, using a "select *" indiscriminately will take longer to pull from the database lots of information you are not really going to use, stressing both the driver and the server, and using more storage (meaning more access to disk that is the real bottleneck here).

B.

  • Because of the same second option above, the less you make the driver work, the best the query will usually perform, not because the query itself, but because it will only take the data that is really needed and useful for your analyses.

Now: how is your network connection? How much is your data source utilized? How much do you have to save memory and CPU from the transactional even if that means that the QlikView Server has more work to do and the load process will take longer?

Answering all those questions will lead you to combine both options, and move the WHERE to the SQL when the data source is relatively "quiet", and therefore making the load process a bit faster, or moving it to the LOAD part (usually after storing the whole table into a QVD file) if the server that hosts the data source is carrying on very demanding tasks, and making the load from QlikView a bit longer...

Hope all that makes sense and helps.

Miguel

Not applicable
Author

Many thanks, Ralf.

I hope other ppl will contribute to this, maybe the ones advocating the other method...?

rbecher
MVP
MVP

I just want to add that a LOAD statement without any filtering, transformation or renaming would not be needed at all.

- Ralf

Astrato.io Head of R&D
Miguel_Angel_Baeyens

Hi,

Although that is correct, there are a lot of cases where the ODBC or driver allows case insensitive SQL statements, but the return is in a different case that which in the query spelled, which usually leads to confusion, "Field not found" errors and so.

That's why, even when doing a plain "SQL SELECT * FROM",  a LOAD statement is strongly recommended, just to know how are the actual table and field names put into QlikView.

Actually, when there is no LOAD sentence at all, what QlikView is doing is a "LOAD *;" without any transformation or renaming.

Regards.

Miguel

rotero
Creator
Creator

to all:

im thinking of doing a sql query first before i use that in script editor specially when im aggreate using sql query.

Now i would like to ask why i cant use this style of loading.

load *

sql select

code,

sum(amount)

from table1

group by code

rbecher
MVP
MVP

But you can. There is a semicolon missing to close the LOAD statement:

load *;

sql select

code,

sum(amount)

from table1

group by code;

- Ralf

Astrato.io Head of R&D
Not applicable
Author

Hi,


Option B:

use this

Here you forgot that semo colon after load, Like this

Tab1:

Load *;

SQL SELECT Order, Item, Date

FROM EUrope.Orders WHERE Date > 20120101;

Option A:

Tab1:

LOAD Order,

Item,

Date;

SQL SELECT *

FROM EUrope.Orders;

Tab2:

Load Order,Date resident Tab1 where Date > 20120101;