Select Statement Question - 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

SQL Server Development (2000)

Select Statement Question

Author

Topic

gregger
Starting Member

19 Posts

Posted - 2004-03-29 : 16:11:13

Hi all,

How would i rewrite this sql statement and be efficient?

I want to rewrite this statement to be more efficient:

SELECT ProductName from Products where
(ProductID = 1) and (Active = 1) or
(ProductID = 89) and (Active = 1) or
(ProductID = 1899) and (Active = 1) or
(ProductID = 809) and (Active = 1)

I want to select the selected products by productID only if they are active. How do you apply the Active = 1 to all of my selections when using the OR keyword?

Thanks,
Gregg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-29 : 16:20:03

WHERE ProductID IN (1, 89, 1899, 809) AND Active = 1

IN gets converted into an OR though. My version might be easier to read.

Tara

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-29 : 16:20:27

How about

SELECT ProductName from Products where
Active = 1 and
ProductID IN (1, 89, 1899, 809)

Graham

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-29 : 16:55:12

Tara, the fastest warrior princess in the west...

Anyway, When you say more effecient, I don't think (and it's ONLY Monday...damn it's going to be a long week), that the style of the query matters for effecient in these cases, the plans should be the same...

For effeciency:

CREATE INDEX Products_IX1 ON Products(ProductId, Active, ProductName)

How many rows of Data we talking about...

Also, I would go with Tara's or GPD's syntax...

Guys/Gals...would it matter here the order of the predicate?

The answer is no right?

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-29 : 18:07:12

It appears to make a difference. Look at these results.

No primary key
reads 4249
scans 1
Table Scan 3.754564
Select 0.093316

Only primary key
reads 4251
scans 1
Primary key lookup 3.754564
Select 0.093315

CREATE INDEX idx_product_1 ON product(ProductID, Active,ProductName)
reads 13
scans 4
index lookup 0.006443
Select 0.000003

CREATE INDEX idx_product_1 ON product(ProductID, ProductName, Active)
reads 13
scans 4
index lookup 0.006513
Select 0.000021

CREATE INDEX idx_product_1 ON product(Active, ProductName, ProductID)
reads 1180
scans 1
index lookup 1.067598
Select 0.031107

CREATE INDEX idx_product_1 ON product(Active,ProductID, ProductName)
reads 12
scans 4
index lookup 0.0064
Select 0.000003

CREATE INDEX idx_product_1 ON product(ProductName, ProductID, Active)
reads 3537
scans 1
index lookup 3.225675
Select 0.093315

CREATE INDEX idx_product_1 ON product(ProductName, Active, ProductID)
reads 3537
scans 1
index lookup 3.225675
Select 0.093315

CREATE INDEX idx_product_1 ON product(ProductID, Active)
reads 128
scans 4
index lookup 0.006444
bookmark lookup 0.200036
select 0.000003

CREATE INDEX idx_product_1 ON product(Active, ProductID)
reads 128
scans 4
index lookup 0.00644
bookmark lookup 0.000033
select 0.000003

CREATE INDEX idx_product_1 ON product(ProductID)
reads 328
scans 4
index lookup 0.006514
bookmark lookup 0.587606
filter 0.000046
select 0.000033

CREATE INDEX idx_product_1 ON product(Active)
reads 4251
scans 1
Primary key lookup 3.754564
Select 0.974595

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources