Results 1 to 12 of 12

Thread: ADO Timeout

  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Unanswered: ADO Timeout

    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!

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hi and welcome to the forums,

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

    Regards,

    Ax

  3. #3
    Join Date
    Sep 2009
    Posts
    7
    Unfortunately that's already set...

  4. #4
    Join Date
    May 2009
    Posts
    258
    What error message are you getting exactly?

  5. #5
    Join Date
    Sep 2009
    Posts
    7
    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.

  6. #6
    Join Date
    May 2009
    Posts
    258
    Sorry for the late response. Instead of using a command object, why not try using the Connection.Execute method?
    Code:
    objCon.Execute "DELETE FROM items3 WHERE LastActionID = 101000"
    It may or may not help out, you may be right that something is up with the SQL Server.

    Ax

  7. #7
    Join Date
    Sep 2009
    Posts
    7
    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?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2009
    Posts
    7
    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...

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Are you using ODBC or OleDB in your connection?
    Lou
    使大吃一惊
    "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


  11. #11
    Join Date
    Sep 2009
    Posts
    7
    SQLOLEDB. The informal response from buddy who works at Microsoft is "upgrade" and "rewrite in .net". Thanks...

  12. #12
    Join Date
    Sep 2009
    Posts
    7
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •