SQL query duration is longer for smaller dataset?

Question

I received reports that a my report generating application was not working. After my initial investigation, I found that the SQL transaction was timing out. I'm mystified as to why the query for a smaller selection of items would take so much longer to return results.

Quick query (averages 4 seconds to return):

SELECT * FROM Payroll WHERE LINEDATE >= '04-17-2010'AND LINEDATE <= '04-24-2010' ORDER BY 'EMPLYEE_NUM' ASC, 'OP_CODE' ASC, 'LINEDATE' ASC

Long query (averages 1 minute 20 seconds to return):

SELECT * FROM Payroll WHERE LINEDATE >= '04-18-2010'AND LINEDATE <= '04-24-2010' ORDER BY 'EMPLYEE_NUM' ASC, 'OP_CODE' ASC, 'LINEDATE' ASC

I could simply increase the timeout on the SqlCommand, but it doesn't change the fact the query is taking longer than it should.

Why would requesting a subset of the items take longer than the query that returns more data? How can I optimize this query?

Answer 1

Most probably, the longer range makes the optimizer to select a full table scan with a sort instead of an index scan, which turns out to be faster.

The index traversal can take longer than a table scan for numerous reasons.

For instance, it is probable that the table itself fits completely into the cache but not the table and the index at the same time.

Answer 2

Besides not having an index on the LINEDATE column. This depends on the database server you use some maintain statistics which influence the queryplan to optimize access.

For Informix i.g. you would do a UPDATE STATISTICS statment. And could examine the costs in a query-plan using SET EXPLAIN ON.

You should check your documentation for similiar statements.