SQL Efficiency problem

[Jo]

Hey people

I'd be really grateful if someone can help me with this. Could someone explain the following:
If the following code is executed, it runs instantly:

declare @SellItemID numeric (8,0)
select @SellItemID = 5296979

SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE (s.sell_itm_id = @SellItemID )

However, if I use this WHERE clause instead -

WHERE (@SellItemID = 0 OR s.sell_itm_id = @SellItemID)

- it takes 70 micro seconds. When I join a few more tables into the statement, the difference is 4 seconds!

This is an example of a technique I'm using in loads of places - I only want the statement to return all records if the filter is zero, otherwise the matching record only. I think that by using checking the value of the variable in the WHERE clause, a table scan is used instead of an index. This seems nonsensical since the variable is effectively a constant. Wrapping the entire select statement with an IF or CASE works, but when I've got 10 filters I'd have to 100 select statements.
I DON'T GET IT!! There must be a simple answer, HELP!!
Jo

PS this problem seems to occur both in 6.5 and 7.0

[Paul]

You could also use COALESCE() : coalesce(val1, val2,..., valn) takes the first non-null value.

IF @sellitemId = 0
SELECT @sellitemId = null
SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE s.sell_itm_id = coalesce(@SellItemID, sell_itm_id)




------------
Shai at 9/19/00 10:17:17 AM

Why don't you write two SELECT statements in your procedure and run them based on @SellItemID value, like

if @SellItemID = 0
SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
else
SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE (s.sell_itm_id = @SellItemID )

P

------------
Jo at 9/7/00 4:12:21 AM

Hey people

I'd be really grateful if someone can help me with this. Could someone explain the following:
If the following code is executed, it runs instantly:

declare @SellItemID numeric (8,0)
select @SellItemID = 5296979

SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE (s.sell_itm_id = @SellItemID )

However, if I use this WHERE clause instead -

WHERE (@SellItemID = 0 OR s.sell_itm_id = @SellItemID)

- it takes 70 micro seconds. When I join a few more tables into the statement, the difference is 4 seconds!

This is an example of a technique I'm using in loads of places - I only want the statement to return all records if the filter is zero, otherwise the matching record only. I think that by using checking the value of the variable in the WHERE clause, a table scan is used instead of an index. This seems nonsensical since the variable is effectively a constant. Wrapping the entire select statement with an IF or CASE works, but when I've got 10 filters I'd have to 100 select statements.
I DON'T GET IT!! There must be a simple answer, HELP!!
Jo

PS this problem seems to occur both in 6.5 and 7.0