SQL query duration is longer for smaller dataset?
- by entens
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?