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.

Help!

Hi and welcome to the forums,

Make sure the CommandTimeout property of the connection object is also set to 0.

Regards,

Ax

Unfortunately that's already set...

What error message are you getting exactly?

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.

Sorry for the late response. Instead of using a command object, why not try using the Connection.Execute method?
It may or may not help out, you may be right that something is up with the SQL Server.

Ax

Hi Ax, thanks for the suggestion.

Unfortunately it still times out (or hangs...) using the connection.execute rather than the command.execute.

Does anyone know why SQL Server would behave one way with ADO but another way using Management Studio? Is this something I should go to Microsoft with?

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...

Are you using ODBC or OleDB in your connection?

SQLOLEDB. The informal response from buddy who works at Microsoft is "upgrade" and "rewrite in .net". Thanks...

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...