why using "OR" in slows query

[rgayle]

I have a table that contains 231 k records, the current where clause
causes the sql to execute really slow (2 mins on 1.2ghz Amd Win2k).
removing this portion "or pgrpcode=listview.grpcode" and the query
returns in about 2 seconds.


select pqtybreak,pgrpcode,pprice,qty,grpcode,stock_code from pricing,
(select qty,partnum,stockcode,grpcode from listtbl ,pmethod, stocktbl
where pmethod.stock_code = stocktbl.stockcode
and stocktbl.partcode=listtbl.partnum
and listtbl.listid=123) as listview
where (pstockcode = listview.stock_code or pgrpcode=listview.grpcode)
and pqtybreak >= listview.qty
and pctrycode = 'c01'

 

[John Vinson]

I have a table that contains 231 k records, the current where clause
causes the sql to execute really slow (2 mins on 1.2ghz Amd Win2k).
removing this portion "or pgrpcode=listview.grpcode" and the query
returns in about 2 seconds.


select pqtybreak,pgrpcode,pprice,qty,grpcode,stock_code from pricing,
(select qty,partnum,stockcode,grpcode from listtbl ,pmethod, stocktbl
where pmethod.stock_code = stocktbl.stockcode
and stocktbl.partcode=listtbl.partnum
and listtbl.listid=123) as listview
where (pstockcode = listview.stock_code or pgrpcode=listview.grpcode)
and pqtybreak >= listview.qty
and pctrycode = 'c01'

Is this a statement or a question?

For one thing you're using a Cartesian Product query; the subquery
will return EVERY POSSIBLE COMBINATION of records in listtbl, pmethod,
and stocktbl and then filter them down. I'd strongly recommend trying
a JOIN clause instead. Try building the subquery in the query grid, or
using JOINs; make sure it works as desired. Then use JOINs to join it
to the outer query rather than using a subquery, if possible.

John W. Vinson[MVP]