Re: Slow query
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sun, 17 May 2009 02:02:54 -0700
Test (no@xxxxxx) writes:
When querying a table with many rows and filtering by a datetime clustered
index (and a couple of other columns), the query is fast in one case and
slow in another. Execution time is 15 seconds the slow way, and 1 second
the fast way.
And a couple of more columns?
If there is one thing I have learnt through the years of working with
SQL Server is that it's meaningless to troubleshoot performance issues
on incomplete information.
Here I can only guess that more columns help to make the query more
selective, so that a non-clustered index can be used. Or the other
way round: in the slow case, the optimizer thinks that a non-clustered
index is a good pick when it isn't. But I can imagine other explanations
as well.
Have you looked at the query plans? They are likely to be different.
If you want us to say something more useful, I would suggest that you
post:
o CREATE TABLE and CREATE INDEX statements for the tables.
o The actual queries.
o The query plans.
o Which version of SQL Server you are using.
As for the query plans, save them from Mgmt Studio (assuming you have
SQL 2005 or later) as .sqlplan files, and attach them. If you are not
available to attach them, put them on web site for download, and post
the URL.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Slow query
- From: Test
- Slow query
- Prev by Date: Multiple Select Statements
- Next by Date: Hdtv Computer Monitor
- Previous by thread: Re: Slow query
- Next by thread: Re: Slow query
- Index(es):
Relevant Pages
|
