Slow query for multi condition [WHARE]

[Tengkorak]

I have a query like this:

select * from
(

select
a.storecode,a.salenumber ,a.productcode,a.quantity,c.saletime,
LEFT(a.productcode,2) as brand,
SUBSTRING(a.productcode,6,4) as coll
from
saleline as a LEFT JOIN
sale AS c ON a.salenumber = c.salenumber and a.storecode=c.storecode

)
as f
where
f.brand = 'AR'

when I execute running normally (when the process is briefly)
but when I add the condition like this:

where
f.brand = 'AR' and
f.saletime<= '2011-01-31'

very slow

i use sql server 2008

how to fix this problem ?

thank you

[wengwashere]

select
a.storecode,a.salenumber ,a.productcode,a.quantity,c.saletime,
LEFT(a.productcode,2) as brand,
SUBSTRING(a.productcode,6,4) as coll
from
saleline as a LEFT JOIN
sale AS c ON a.salenumber = c.salenumber and a.storecode=c.storecode
where
LEFT(a.productcode,2) = 'AR'
c.saletime <- '2011-01-31'

[leinad31]

Not sure how predicate pushing occurs in SQL Server but inline query can be rewritten with condition LEFT(a.productcode,2) = 'AR' . Also everyone will have to guess because we don't know how your DB is setup, e.g. indexes, and you didn't provide the query execution plans.

Anyway, I'm guessing that saleline table is small (few records) for the first SQL to complete briefly. This is because in first SQL the database engine can push condition into inline query in order to filter records from saleline before performing expensive LEFT JOIN. In short fewer records joined to sale, and PK-FK indexes can be used so no need to read entire sale table.

Possibility A:
But in second SQL such optimization is not possible due to reference on sale.saletime. Database engine decided to first retrieve result of LEFT JOIN before eliminating records (more records to exclude in final resultset instead of eliminating records upfront prior to join).

Possibility B:
An index exists on date column sale.saletime and this was used. So rather than efficient approach (filter on brand, left join using PK-FK, then filter again on saletime), inefficient execution plan was (get left hand records filtered only brand, get right hand records filtered on saletime index which can include records not related to AR brand, then join these intermediate resultsets, and finally exclude records from the final result set... more internal operation/processing performed by database).

Only way to know what happened is to review the execution plan. I suggest you research how to do that.