CODEWITHSUNDEEP
sql-serverselectwhere-clausesqlperformancetimeoutexception
Farhad Aliyev
Farhad Aliyev

Reputation: 9

SELECT with WHERE drops performance

In my application I use queries like

SELECT column1, column2 
FROM table 
WHERE myDate >= date1 AND myDate <= date2

My application crashes and returns a timeout exception. I copy the query and run it in SSMS. The results pane displays ~ 40 seconds of execution time. Then I remove the WHERE part of the query and run. This time, the returned rows appear immediately in the results table, although the query continues to print more rows (there are 5 million rows in the table).

My question is: how can a WHERE clause affect query performance?

Note: I don't change the CommandTimeOut property in the application. Left by default.

Upvotes: 0

Views: 195

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Without a WHERE clause, SQL Server is told to just start returning rows, so that's what it does, starting from the first row it can find efficiently (which may be the "first" row in the clustered index, or in a covering non-clustered index).

When you limit it with a where clause, SQL Server first has to go find those rows. That's what you're waiting on, because you don't have an index on myDate (or date1/date2, which I'm not sure are columns or variables), it needs to examine every single row.

Another way to look at it is to think of a phone book, which is an outdated analogy but gets the job done. Without a WHERE clause, it's like you're asking me to read you off all of the names and numbers in the book. If you add a WHERE clause that is not supported by an index, like read me off the names and numbers of every person with the first name 'John', it's going to take me a lot longer to start returning rows because I can't even start until I find the first John.

Or a slightly different analogy is to think of the index in a book. If you ask me to read off the page numbers for all the terms that are indexed, I can do that from the index, just starting from the beginning and reading through until the end. If you ask me to read off all the page numbers for all the terms that aren't in the index, or a specific unindexed term (like "the"), or even all the page numbers for indexed terms that contain the letter a, I'm going to have a much harder time.

Upvotes: 1

Related Questions