Where vs Exists
Daniel Rozental Jul 18, 2010 9:40 PMNot sure if this has been covered before in a discussion, sorry if it was.
While I was doing a few high volume tests I ran into what I believe might be an exists clause glitch and I wanted to share it hoping someone at QT will pick it up or at least It might be useful to someone.
I'm using QV9 SR4 (not the latest, I know) and a version of the 10 Million rows application that has 50 Million.
1. First I did a simple load using a Where clause, it took 58 secs to load 945.501 rows.
LOAD ShipperID, OrderDate, CustomerID, Discount, ProductID, Quantity, UnitPrice, sales, COS
FROM R00.QVD (qvd)
where ProductID = 40;
drop table R00;
2. Then I thought a where exists clause would be much faster, since the load would be optimized, and it was. It took only 4 secs to load the same information as the code above
load 40 as ProductID autogenerate(1);
LOAD ShipperID, OrderDate, CustomerID, Discount, ProductID, Quantity, UnitPrice, sales, COS
FROM R00.QVD (qvd)
where Exists(ProductID);
drop table R00;
So far nothing out of the ordinary.
3. Then I added a transformation, this way neither the simple where nor the where exists clauses will have the benefit of the optimized load.
I only added "Quantity*UnitPrice  Discount as NetSales". The load took 0.59 secs
LOAD ShipperID, OrderDate, CustomerID, Discount, ProductID, Quantity, UnitPrice, sales, COS, Quantity*UnitPrice  Discount as NetSales
FROM R00.QVD (qvd)
where ProductID = 40;
drop table R00;
4. Same thing, I added
load 40 as ProductID autogenerate(1);
LOAD ShipperID, OrderDate, CustomerID, Discount, ProductID, Quantity, UnitPrice, sales, COS,
Quantity*UnitPrice  Discount as NetSale
FROM R00.QVD (qvd)
where Exists(ProductID);
drop table R00;
5. I tried doing it in two steps, first the optimized where exists load, and then the transformation. It took 16 secs
load 40 as ProductID autogenerate(1);
R00_Temp:
LOAD ShipperID, OrderDate, CustomerID, Discount, ProductID, Quantity, UnitPrice, sales, COS
FROM R00.QVD (qvd)
where Exists(ProductID);
R00:
LOAD ShipperID, OrderDate, CustomerID, Discount, ProductID, Quantity, UnitPrice, sales, COS
, Quantity*UnitPrice  Discount as NetSale
RESIDENT R00_Temp;
drop table R00_Temp;
drop table R00;
Hopefully some else might have a view on this.

where vs exists.qvw 115.2 K