Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Unanswered: ugh...timeout error driving me insane

    ok here's the deal...

    I've got 2 identical DB's on the same server, one production, one test. No we don't have a test env, but at least I'm not testing on a prod DB (some people here do, trust me).

    I've got a prod VB6 app that used SQL sp's. I've pointed the ADO connection string to the test DB while I make the changes I need to make, and I'll obviously change it back before I'm done etc.

    I made some VERY minor changes to one of the sp's (added a variable, changed some stuff), tested thoroughly in query analyzer (runs with no errors in <1 sec), altered my ADO command accordingly and when I executed I get this:

    run time error '-2147217871 (80040e31):
    [Microsoft][ODBC SQL Server Driver] timeout expired

    The connection to the server is fine, I've tested that - it trips on the execution of the command:

    Set rstCalls = conHelpArchiveConnection.Execute(SQLQ)

    so here's my question: The prod version of this app works like a charm, and the test app times out. I'd rather not toy with the connection timeout setting on the server. Any thoughts on what could be causing this?

    Any help would be appreciated, I'm ready to throw in the towel. Well at least until tomorrow morning

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    have you checked the execution plans for the sp on the 2 servers? you could be missing an index on one of the tables involved. I would also run DBCC SHOWCONTIG on the 2 tables to check for fragmentation and execute DBCC DBREINDEX as neccessary. I might also update the statistics on the test environment tables and after all of that is done I would sp_recompile the stored procedure and run it a few times to get the execution plan in procedure cache.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2006
    Posts
    4

    follow up #1

    Hey Sean,

    Thanks for the tips. Both SP's are actually hitting the same server. I checked the execution plan for both SP's and there is no difference. They're both pretty innocuous...

    Both sp's query a view, and I'll check the indexing on the tables used to build the view. I'm not a big fan of this method of getting data, but unfortunately one of our ex-programmers was...

    I'll also follow your other suggestions (check for frag, reindex if necessary, recompile etc.). I'll post a follow up after that's all done. But right now I've got another s**t storm brewing, so it may not be for another couple hours. Gotta prioritize, you know?

    Thanks again for the help!

    -Keith

  4. #4
    Join Date
    Mar 2006
    Posts
    4

    Solution!

    Alright I had to step away from this for a couple of days, and when I got back to it today I figured out what the issue was. So thought I'd share the solution to save you the headache if you're dealing with the same problem.

    The query I was testing was actually running >30 seconds when executed through the VB app (not so in Query analyzer, it runs very quickly there). So the ADO Command was timing out and generating the error. The default time out setting is 30 seconds, so I changed it to 60 and it worked fine.


    Dim comCalls As ADODB.Command
    Dim rstCalls As Recordset

    Set comCalls = New ADODB.Command
    Set rstCalls = New ADODB.Recordset

    OpenDB

    With comCalls
    .CommandTimeout = 60
    .ActiveConnection = conHelpArchiveConnection
    .ActiveConnection.CursorLocation = adUseClient
    .CommandType = adCmdStoredProc
    .NamedParameters = True

    .Parameters.Append .CreateParameter("@Store", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("@Category", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("@Computer", adVarChar, adParamInput, 10)
    .Parameters.Append .CreateParameter("@ShowOpenOnly", adInteger, adParamInput)

    .Parameters("@Store").Value = Store
    .Parameters("@Category").Value = Category
    .Parameters("@Computer").Value = Computer
    .Parameters("@ShowOpenOnly").Value = ShowOpenOnly

    .CommandText = "ListCallsTakenX"
    Set rstCalls = .Execute
    End With

    CloseDB


    So now the next logical step is to tune the query and index the 7 tables it uses so it takes less time. I'd rather the log in process not take >30 seconds!

    Thanks!

  5. #5
    Join Date
    Apr 2006
    Posts
    1
    I had to stop and restart the sql server to make the timeout errors go away. I was having some intermittent failures with an app that has been in production for some months. It has been about 3 months since a server restart.

    Guess M$ SqlServer gets tired

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Dude, this is not a solution. increasing the timeout just serves to bog down your server with bad queries. I am sorry I did not catch this earlier but I have been wicked busy lately. Post your ddl and your query or PM me with it and I will take a look.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Mar 2006
    Posts
    4

    it's a hack, I know

    oh I know this is not a permanent solution T, but right now the server's not the only thing that's bogged down =).

    So it's a temporary *hack* to make it work until I find the time to get back to tuning the query...until then I'm the company reporting biaaatch

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Love the moniker

    Just for laughs (if you don't want to fully optimise) - time your sproc with timeout set as it is then see if adding WITH RECOMPILE improves things. Very easy to test. If it does then plan is probably a bit Dodge McDodge.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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