May 15, 2009 at 6:19 am
Hi,
hope that somebody can help me.
I've written a stored procedure that queries table called L_ITEMS with around 7 500 000 records. I have several variables (@SenderOperator) to limit the output. To use that variable I need to query another table,(EDI_ADDRESSES). For performance reason I am not joining.
My query looks something like this:
select * (...) from L_ITEMS itm (nolock)
where
((@Operator is null) or (@Operator is not null and itm.SENDER in (select SENDER from EDI_ADRESSES where operator = @Operator)
If an @operator is specified I just want records for that operator otherwise i want all records.
That query takes about 14 seconds to run.
When I remove the OR-Condition for testing, the whole query takes 0 seconds to run.
But i need the condition. In the query plan sql introduces an (in my opinion unnessary L_ITEM table scan) that slows down the query.
As a workaround I use something like this:
select * (...)
from L_ITEMS itm (nolock)
where
itm.SENDER in (select SENDER from EDI_ADRESSES where operator = @Operator or @Operator is null)
Runs in 0 seconds.
This gives me a bad feeling. Is there a better way to have this?
Why does the @Operator is null or @Operator is not null has such a large impact
on the query?
Any help is appreciated.
Thanks
Matthias
May 19, 2009 at 9:06 am
Try this:
select itm.* (...)
from L_ITEMS itm (nolock)
left outer join EDI_ADRESSES EDI
on EDI.SENDER = ITM.SENDER
and EDI.operator = @operator
May 19, 2009 at 9:23 am
Having an "OR" in the Where clause often eliminates the ability for SQL Server to do an index seek. Instead, it ends up being forced to do index scans. If there's no appropriate index to scan/seek, then it ends up doing table scans.
Your second query should help with that. Another option that often works pretty well would be:
select itm.*
from L_Items itm
inner join EDI_Addresses addr
on itm.Sender = addr.Sender
where operator = @Operator
union
select *
from L_Items
where @Operator is null;
Sometimes that's faster, sometimes it's not. Only way to know for sure is testing.
If you can post the execution plans for the various versions, we can probably help more. Save them to files, zip the files, and attach the .zip file to the forum.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 9:28 am
Have a look at Gails blog about "Catch All Queries" here:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
This actually becomes one of my standard links 😀
Greets
Flo
May 20, 2009 at 1:26 pm
Thank you all for your help.
The link explains the problem very good.
Sometimes it's really hard to understand the way SQL works.
Matthias
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply