How to improve SELECT performance

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 2008 Forums

Transact-SQL (2008)

How to improve SELECT performance

Author

Topic

Buho
Starting Member

2 Posts

Posted - 2011-02-09 : 10:26:23

Hi everyone. I'm a longtime ASP.NET developer who has years experience in T-SQL (not a DBA), but I'm stumped here. Help appreciated.

I have a table Chemistry with a million records. It has three foreign keys that together create a composite key that has a clustered index on it.

Chemistry(Sample_ID, Parameter_ID, Method_ID, Data)

A raw SELECT Parameter_ID FROM Chemistry takes five seconds. I need to use this table in an inner join with the Parameter table, but this query also takes five seconds:

SELECT Parameter_ID FROM Parameter p INNER JOIN Chemistry c ON p.Parameter_ID = c.Parameter_ID

I am using the join to "filter" the results of Parameter to only those that are in use in Chemistry. How can I get this select to run in under one second? I need this performance for an interactive web app I'm creating. Help appreciated!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-09 : 10:37:42

Try putting a non-clustered index on Parameter_ID

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-09 : 11:34:46

or switch the order of the index columns
from
Chemistry(Sample_ID, Parameter_ID, Method_ID, Data)
to
Chemistry(Parameter_ID, Sample_ID, Method_ID, Data)

and maybe you need to consider
SELECT Parameter_ID FROM Parameter p
where exists (select * from Chemistry c where p.Parameter_ID = c.Parameter_ID)

Buho
Starting Member

2 Posts

Posted - 2011-02-09 : 14:04:21

Thanks Russell and Andrew. Using WHERE EXISTS brought my select to under a second. Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources