Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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!