SQL Execution Plan Bug! - SQL Server Forums

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)

SQL Execution Plan Bug!

Author

Topic

ratatata
Starting Member

4 Posts

Posted - 2005-04-28 : 11:19:28

Dears,
I noticed the following SQL behavior and want to share my concerns with you.
I believe that this should be considered as a bug in setting the execution plan in SQL Server!

CREATE TABLE [Table1] (
[C1] [int] IDENTITY (1, 1) NOT NULL ,
[C2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C4] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
( [C1] ) ON [PRIMARY]
) ON [PRIMARY]
--GO
INSERT INTO [Table1] ([C2],[C3],[C4])VALUES('a','b','c')
INSERT INTO [Table1] ([C2],[C3],[C4])VALUES('d','e','f')
INSERT INTO [Table1] ([C2],[C3],[C4])VALUES('g','h','i')

-- APPLY THE BELOW WITH SHOW EXECUTION PLAN ON

DECLARE @X INT
SET @X = 2

SELECT C1, C2, C3, C4
FROM Table1
WHERE C1 = @X OR @X = -1
-- CLUSTERED INDEX SCAN!!!! 85.46%

SELECT C1, C2, C3, C4
FROM Table1
WHERE C1 = @X --OR @X = -1
-- CLUSTERED INDEX SEEK 14.54%

What do you think?

I know that I can solve this issue by implementing dynamic queries, but all my stored procedures are built on this principle which is making them generic.

Most of my WHERE conditions look like:

WHERE (Table.Col1 = @Col1 OR @Col1 = -1)

AND (Table.Col2 = @Col2 OR @Col2 = -1)

AND (Table.Col3 = @Col3 OR @Col3 = -1)

AND (Table.Col4 = @Col4 OR @Col4 = -1)

AND (Table.Col5 = @Col5 OR @Col5 = -1)

And so when I call this SP I can do the filter in the way I like, I can send -1 for the column I don’t like to apply the filter on.

Unfortunately, I noticed that the performance is so bad! And discovered that Indexes Scan is being done always!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-28 : 11:52:45

SELECT C1, C2, C3, C4
FROM Table1
WHERE C1 = CASE @X WHEN -1 THEN 0 ELSE @X END

This will also give you an Index Seek (14.34% for me when run with your first one)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-29 : 06:15:40

This is also a duplicate of a question posed elsewhere!

ratatata
Starting Member

4 Posts

Posted - 2005-04-30 : 07:17:04

quote:
Originally posted by DonAtWork

SELECT C1, C2, C3, C4
FROM Table1
WHERE C1 = CASE @X WHEN -1 THEN 0 ELSE @X END

This will also give you an Index Seek (14.34% for me when run with your first one)

I tried it before! it makes an index scan as well.

ratatata
Starting Member

4 Posts

Posted - 2005-04-30 : 07:27:20

quote:
Originally posted by DonAtWork

SELECT C1, C2, C3, C4
FROM Table1
WHERE C1 = CASE @X WHEN -1 THEN 0 ELSE @X END

This will also give you an Index Seek (14.34% for me when run with your first one)

I tried it before! it makes an index scan as well.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-30 : 08:24:49

This is definitely not a bug. If you pass -1 in the variable, the optimizer has to scan anyway. The OR condition guarantees that the optimizer can't force a seek.

Before you get tied up in worrying about the plan, you should test the query on actual data and see if the performance is adequate. What's that old saying about premature optimization?

ratatata
Starting Member

4 Posts

Posted - 2005-05-02 : 07:24:10

quote:
Originally posted by robvolk

This is definitely not a bug. If you pass -1 in the variable, the optimizer has to scan anyway. The OR condition guarantees that the optimizer can't force a seek.

Before you get tied up in worrying about the plan, you should test the query on actual data and see if the performance is adequate. What's that old saying about premature optimization?

I am suffering from a slow performance in my production database my friend! and that bug is doing all that slowness.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-02 : 07:26:19

If you drain the oil out of your car, and let it run until the engine seizes, is that a bug in the car manufacturing process ?

"Doctor, it hurts when I do this"

Damian
Ita erat quando hic adveni.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-02 : 14:27:33

quote:
--------------------------------------------------------------------------------
Originally posted by DonAtWork

SELECT C1, C2, C3, C4
FROM Table1
WHERE C1 = CASE @X WHEN -1 THEN 0 ELSE @X END

This will also give you an Index Seek (14.34% for me when run with your first one)

--------------------------------------------------------------------------------

I tried it before! it makes an index scan as well.

Cut and paste that into QA and check the execution plan. It SHOULD give you a seek.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources