Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

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

Hi,

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

- Ralf

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

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

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

Many thanks, Ralf.

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

MVP
MVP

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

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

- Ralf

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

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

rubyotero
New Contributor III

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

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

MVP
MVP

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

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

Not applicable

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

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;

Community Browser