I received reports that the reporting application did not work. After my initial investigation, I discovered that the SQL transaction was disconnected. I am surprised why a request for a smaller selection of items will take much longer to return results.
Quick request (on average 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-term request (on average 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 just increase the wait time on SqlCommand, but that does not change the fact that the request takes longer than necessary.
Why does a query for a subset of elements take longer than a query that returns more data? How can I optimize this query?