@Variable in OR clause affecting execution time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

@Variable in OR clause affecting execution time

Author

Topic

Mak
Starting Member

7 Posts

Posted - 2006-02-13 : 07:55:52

Hi,

I have got a Select statement which returns number of columns and the Where clause to filter, (pretty much normal stuff)

I get results back within 20 seconds (in query analyser) if use this where clause:

WHERE TBL.ClientID IN (SELECT ClientID FROM @Clients)
AND TBL.SchemeID IN (SELECT SchemeID FROM @Schemes)

However, if I change the clause in following way, it takes double time or even longer (up to 2 minutes)

WHERE (TBL.ClientID IN (SELECT ClientID FROM @Clients)
OR @ClientID IS NULL)
AND (TBL.SchemeID IN (SELECT SchemeID FROM @Schemes)
OR @SchemeID IS NULL)

In other words using @variable in OR clause affects the execution drastically

Does anyone know any way around it?

Your help is greatly appreciated

Mak

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-13 : 09:20:10

actually, the 'IS NULL' part is what is slowing down your query.

look at the execution plans of both queries and you will see the difference.

Your best bet is to use an OUTER JOIN to achieve the same thing. look that up in BOL for examples.

-ec

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-13 : 09:27:04

I was going to say its because of having an OR in the WHERE clause. That forces a table scan. Try to come up with logic that eliminates the OR. One possibility could be the following but you will know best how the different criteria should behave.


select	<columnList>
from	<fromclause>
WHERE	TBL.ClientID IN (SELECT ClientID FROM @Clients) 
and	@ClientID IS NOT NULL

union all

select	<columnList>
from	<fromclause>
where	TBL.SchemeID IN (SELECT SchemeID FROM @Schemes) 
and	@SchemeID IS not NULL

Be One with the Optimizer
TG

Mak
Starting Member

7 Posts

Posted - 2006-02-13 : 09:50:13

Thanks very much for prompt replies

TG's idea works and certainly fixes the problem, i would wonder why AND works differently/faster then OR

Many Thanks
Mak

Mak
Starting Member

7 Posts

Posted - 2006-02-13 : 12:33:05

Hi again,

Referring back to my original post, the stored procedure which contains this Select statement gets called by two different applications.

As suggested by TG:
select <columnList>
from <fromclause>
WHERE TBL.ClientID IN (SELECT ClientID FROM @Clients)
and @ClientID IS NOT NULL

Changing the logic certainly have improved the performance significantly BUT only when the call is made by the application which provides the @ClientID (I mean when its not null).

When the call is made by other application which supplies @ClientID as NULL, the whole execution is ignored and no data is returned.

I have also tried using CASE statement, which seems to be quite slow as well.

I would imagine the use of AND with IS NOT NULL works, but may not be appropriate in this scenario.

Any other suggestions please ?

Regards,

Mak

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-13 : 13:34:16

when @clientid is null, what should be returned?
My code above kind of assumed either @clientid or @schemeid would always be passed (or both).

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-02-13 : 13:58:28

Not sure if its relevant, or accurate!, but I have seen folk say that putting a "NULL TEST" first helps - so change

WHERE (TBL.ClientID IN (SELECT ClientID FROM @Clients)
OR @ClientID IS NULL)

to

WHERE (@ClientID IS NULL
OR TBL.ClientID IN (SELECT ClientID FROM @Clients))

this is especially where a number of parameters are past, some/many of which are NULL [i.e. representing tests that should be treated as a wildcat and match anything] - so if that doesn't jell with what you are doing its probably irrelevant!

Kristen

Mak
Starting Member

7 Posts

Posted - 2006-02-14 : 03:29:40

TG:
Actually @SchemeID and @ClientID are independent and I do require both in Where clause.
I should have had mentioned this in my previous posts, my apologies.

If I re-phrase my question I would say that we can take @ClientID into account (as both are same)

So, when @Clients is has got value(s) its returns data much faster then when it’s passed in as NULL (I have also tried passing in –1, or other numeric values, makes no difference).

When changed to:
select <columnList>
from <fromclause>
WHERE TBL.ClientID IN (SELECT ClientID FROM @Clients)
and @ClientID IS NOT NULL

It returns no data when passed in as NULL, but a lot quicker when @Clients has been populated

Kristen: I have already tried this:-
WHERE (@ClientID IS NULL
OR TBL.ClientID IN (SELECT ClientID FROM @Clients))

It seems to make no difference in performance

Regards,

Mak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources