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

Load All (*) vs. load field1, field2,...,field(n)

Hi ppl!

In SQL Server, the execution plan for a

Select * from table

is different to the one for

Select field1 from table

(assuming "table" only has one field)

so the question is if that is also a concern in qlikview where we can load

Load * FROM $(vRutaDatos)Base_EXT.qvd (qvd);

or

Load field1, field2 FROM $(vRutaDatos)Base_EXT.qvd (qvd);

I'm showing here the QVD version, since I think when loading from a DB the same concept should be applied (form the sql server example) since the query is taking place in the server, not in qlikview.

any other weird cases in which you can optimize the reading are welcome too!

any light on this would be appreciatted, thx for your time

Tags (2)
1 Solution

Accepted Solutions
Luminary
Luminary

Re: Load All (*) vs. load field1, field2,...,field(n)

Hi,

You are right! In SQL Server execution plans are different for "*" compared to explicit field names. You might not use Index Seek operator if you are using "*". And you might not use planned cache with "*".

I agree with HIC and I avoid using "*" in QlikView for the following reasons. Although, there will not be performance implications if you have same number of fields.

1. You will loose control over number of new fields added while you are using "*"

2. You will never know if the table schema is changed while you are using "*"

3. It becomes difficult when you are using QlikView Joins with "*" because of Join Predicate works on matching fields

4. QlikVIew implicit joins based on common field names and it becomes difficult to understand for unwanted joins

5. If you are using "*" and you wanted to perform transformation on a field then you are duplicating the field as a new field instead of transforming the same field

6. Last but not least - It looks like a lazy programmer/developer

Cheers,

DV

www.QlikShare.com

4 Replies

Re: Load All (*) vs. load field1, field2,...,field(n)

There shouldn't be any difference between the two - unless the wildcard (*) implies a larger number of fields.

I personally try to avoid the wildcard if I know that the source file may change, e.g. columns may be added.

HIC

Luminary
Luminary

Re: Load All (*) vs. load field1, field2,...,field(n)

Hi,

You are right! In SQL Server execution plans are different for "*" compared to explicit field names. You might not use Index Seek operator if you are using "*". And you might not use planned cache with "*".

I agree with HIC and I avoid using "*" in QlikView for the following reasons. Although, there will not be performance implications if you have same number of fields.

1. You will loose control over number of new fields added while you are using "*"

2. You will never know if the table schema is changed while you are using "*"

3. It becomes difficult when you are using QlikView Joins with "*" because of Join Predicate works on matching fields

4. QlikVIew implicit joins based on common field names and it becomes difficult to understand for unwanted joins

5. If you are using "*" and you wanted to perform transformation on a field then you are duplicating the field as a new field instead of transforming the same field

6. Last but not least - It looks like a lazy programmer/developer

Cheers,

DV

www.QlikShare.com

Gabriel
Valued Contributor III

Re: Load All (*) vs. load field1, field2,...,field(n)

Hi Lopez,

First and foremost, the point stated above (from HIC & Deepak) is the best. What I would also add to that is a field might be blank and if you used * in your query, from DATA MODELLING point the field is redundant and of no use. So let us caltivate the ability of specifying fields needed.

Regards,

Gabriel

Not applicable

Re: Load All (*) vs. load field1, field2,...,field(n)

thank you for taking the time to explain so thoroughly!

yeah, I had those reaons too in the back of my head, but wanted to know if anyone had experienced differences in the performance while using one or the other method, though, general consensus seems to think that it doesn't matter

thank you again Deepak!

Community Browser