RE: ISAM calls/second

The two queries are hugely different because the OR clause isn't properly parenthesized. The second half of the WHERE clause evaluates the Cartesian product of the two tables! You should always use plenty of parentheses, as in: SELECT sum(price * quantity) expdate_odate FROM podetail, pomaster WHERE pomaster.number = podetail.number AND ((expdate BETWEEN "01/01/97" AND "12/31/97") OR (expdate IS NULL AND odate BETWEEN "01/01/97" AND "12/31/97") ) AND chgcode = "5065"; Your code is equivalent to: select sum(price * quantity) expdate_odate from podetail, pomaster where pomaster.number = podetail.number and expdate between "01/01/97" and "12/31/97" UNION select sum(price * quantity) expdate_odate from podetail, pomaster where (expdate is null and odate between "01/01/97" and "12/31/97") and chgcode = "5065"; Note that there is no join in the second of the two queries! I'd guess the low rate of making ISAM calls is due to the Cartesian product process, somehow. Yours, Jonathan Leffler (jleffler@visa.com) #include <disclaimer.h> ---------- From: Scott Black[SMTP:sblack@elsouth.com] Sent: Friday, February 06, 1998 10:54 AM To: informix-list@iiug.org Subject: ISAM calls/second I have the following two queries: (HP-UX 10.20, OnLine 7.22) select sum(price * quantity) expdate from podetail, pomaster where pomaster.number = podetail.number and expdate between "01/01/97" and "12/31/97" and chgcode = "5065"; select sum(price * quantity) expdate_odate from podetail, pomaster where pomaster.number = podetail.number and expdate between "01/01/97" and "12/31/97" or (expdate is null and odate between "01/01/97" and "12/31/97") and chgcode = "5065"; as you can see the second one is the same as the first except if expdate is null then drive on odate instead. 'set explain on' gives a cost of 302 for the first and, are you ready for this, 2.1 BILLION for the second. Okay, I'll just change the second one around and code a little better next time, live and learn. Here's the real question; When the second one ran it brought the entire system to a halt. So, I think, I'll just go into the handy old onperf tool and see how many ISAM/PDQ calls this is making. It must be around 30K or more. But instead it totally flat lines. My session has less than 2 calls per second and no-one else has any (less then 1 per second for every other user on the system combined!) What is the reason for this? It seems that the system would go into super access mode, not come to a dead stop. Any thoughts? TIA