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

[#1]

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!

[rbecher]

Hi,

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

- Ralf

[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

[#4]

Many thanks, Ralf.

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

[rbecher]

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

- Ralf

[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]

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]

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

[#9]

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;