Can this be improved ? - 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

General SQL Server Forums

New to SQL Server Programming

Can this be improved ?

Author

Topic

tubbypascal
Starting Member

1 Post

Posted - 2012-02-03 : 20:09:06

Hi all, I have a query that I think can be improved but not sure how. Each company in the database can be in one, two or three separate categories. The query selects companies that are active and belong to the category passed in the parameter. The query is working ok, but my understanding is that by using 'OR' the query is unable to use the index. So perhaps it could be improved by use of "BETWEEN" somehow. ie BETWEEN 'Category1' AND 'Category3' I just can't figure out how to get it to work, or find a similar example.
Here is the code:

SELECT CompanyName, City1, City2, Phone1, Phone2, Email, Web, Description, Category1, Category2, Category3, CompanyID, City3, Logo, UserName, UserID, Facebook, AllCities, Sponsor, Active, BadStanding

FROM MemberInfo

WHERE (Active = 0) AND ( (Category1 = @category) OR
(Category2 = @category) OR
(Category3 = @category))

ORDER BY Sponsor, CompanyName

Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 23:06:18

try using IN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-04 : 02:28:42

Move the category to a child table, instead of having 3 x columns in the MemberInfo table. (That will also mean that you can have unlimited Categories, if you want to)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-04 : 02:45:21

IN is expanded to a set of ORs, so will provide no performance benefit.

Is this slow? That's the first question that has to be asked. If not, then leave it and do something more productive than worrying about a query that is performing adequately.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-02-04 : 05:03:39

I suppose it might perform better (assuming you have an index on each of the Category Columns, include the [Active] column too so the where clause is "covered") if you use a UNION:


SELECT CompanyName, ... columns ...
FROM MemberInfo
WHERE Active = 0 AND Category1 = @category
UNION
SELECT CompanyName, ... columns ...
FROM MemberInfo
WHERE Active = 0 AND Category2 = @category
UNION
SELECT CompanyName, ... columns ...
FROM MemberInfo
WHERE Active = 0 AND Category3 = @category
ORDER BY Sponsor, CompanyName

if you could have the same value in Category1 AND Category2 (etc) columns then you will need to use UNION ALL instead, which may be slower because of the de-dupe-ing step.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources