I have a VB6 application that is occasionally timing out on a simple query:
DELETE FROM items3 WHERE LastActionID = 101000
The value of LastActionID can vary. The items3 table contains roughly 4 million rows, the number of items to be deleted is around 1000, the table is indexed on LastActionID.
The command is issued through an ADO Command object - the Timeout is set to 0, which means that the application hangs at that point. Setting the Timeout to any other value means the application receives a timeout error. Running the same command through SQL Server Management Studio executes in less than .1 of a second. The command is one of a sequence - the other commands above it all execute fine.
I've tried setting .Properties("Access Order") = 0 with no effect (as suggested for a similar timeout error on a stored procedure). "SELECT @@LOCK_TIMEOUT" returns -1.
I'm now struggling - as I can't think of a much simpler query to do. More baffling is that sometimes it just works.
Well, if I leave the code exactly as-is, I don't get an error message because the application just hangs waiting for a response from the database.
If I step through the code, ignoring the line which sets the command's CommandTimeout property to 0 (ie. leaving it at the default), I get the ADO timeout error message (code -214217871, description Timeout expired).
It seems as though SQL Server is getting in a mess with its execution plan, but I don't know how to force SQL Server to evaluate this simple query properly, using the pre-defined index.
Honestly I think you're attacking the wrong end of the pipe. Even with millions of records there is probably a way to optimize performance on the server side of things. You can provide index hints as you alluded to earlier using the WITH() table hint syntax. You should be able to find some info on this in BOL by searching for "table hints".
ooo - I thought Teddy had something there - the WITH statement appeared to be exactly what I needed. Then our database server broke...
Anyhow, it's back up and running again. Submitting DELETE FROM items3 WITH (INDEX(idx_items3_last_action_id)) WHERE last_action_id = 101000 gave an error indicating that a FROM clause was needed! A quick Google indicated that Microsoft weren't treating the FROM statement as a FROM statement!
The workaround which was accepted by SQL Server was DELETE FROM items3 FROM items3 WITH (INDEX(idx_items3_last_action_id)) WHERE last_action_id = 101000 - but it still hangs...
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
A further update - our server was rebuilt as a RAID 5 array, which is the same hardware configuration as the customer's server. Now, the delete (run from Management Studio) takes flipping years. Three-and-a-half hours and counting for deleting 790 records out of 4,000,000, on the indexed column. It would be quicker to do it manually in Excel!
There are sites that talk about doing deletes in chunks, but they all use stored procedures, that we (with our policy of supporting Oracle as well) cannot use.
Why on earth is SQL Server taking so, so long to delete a handful of records? Back to the drawing board...