Post reply

Timeout Expired

  • February 12, 2004 at 4:07 am

    #359106

    I have an application that export images to Sql server database.

    So, I have very images, I think that have 5.5GB.

    This application join the images in a Table, I exported 2.5 GB, but now, the sql server show the error "Timeout Expired". I must to use ADO connection string.

    In Enterprise Manager I set "remote query timeout" to 0 (unlimited) and in connection string put CONNECTIO TIMEOUT=999999, but I can't exported to database.

    Can somebody help me?

     

    Thanks

     

    Luiz Fernando

  • February 12, 2004 at 11:59 pm

    #493945

    Try out with latest MDAC ....We have also faced the same situation.After installed the latest MDAC the problem is over

  • February 13, 2004 at 10:32 am

    #494036

    What you need is "query timeout" or "command timeout".  ADO Connection has "CommandTimeout" property, or ADO Command object has a "Timeout" property.  Either increase these to larger value or set to zero, should solve your problem.

  • February 13, 2004 at 11:50 am

    #494042

    I have installed MDAC 2.8...

    And about commandTimeout property, there is some equivalent to connection string? Because I don't have source code of application.

    I tryed to use Connect Timeout=999999 in connection string, but it didn't work.

     

  • February 13, 2004 at 11:58 am

    #494043

    No way. ConnectTimeout controls just that "connection timeout." How long it takes to establish a connection, nothing to do with how long it takes to run a query. There are also remote connection timeout settings, and remote query time out settings, neither of which apply here. 

    You need "query timeout" or "command timeout"

    I don't believe SQL has a default for either of these, that is to say SQL's default is infinite. 

    Your problem is that ADO does have a default, maybe 2 mins (you should be able to tell from experience).  So even if you were able to change this in SQL somehow, ADO would override it.  You have to tell ADO to use a different value, I think only way is w/the source. 

    A different MDAC might also do the trick, if one of the differences was implementation of a different default query timeout, but I've never read anything about this.

  • February 14, 2004 at 9:08 am

    #494091

    PS - best solution is leave timeout as-is and focus on speeding up whatever it is that's slow.  We have very active 100GB db w/text data being inserted/updated/queried all day long, nothing takes a minute to run, you should be able to tune your problem away?

  • February 17, 2004 at 4:45 am

    #494325

    How Do I this?

    Can you help me?

     

    Thanks

     

    Luiz Fernando

  • February 17, 2004 at 4:54 am

    #494326

    Maybe.  I think this is one of  the most fun parts of the job. 

    It might turn out that you need to do the tuning in the sourcecode too (same place you'd adjust the timeout), but good chance there are things you can do outside of that:  a) if your app calls stored procs, then you can pretty much do any tuning that's required; or else b) you might be able to improve things by adding (or dropping) indexes; or c) you might be able to improve things by modifying your maintenance approach (adding missing stats, rebuilding indexes, etc.)

    But first you're going to need to know what the query/SQL your app is running that is timing out.  Do you already know this?  If you don't have sourcecode then maybe you'll need to run SQLProfiler to capture it. 

  • February 17, 2004 at 5:37 am

    #494330

    I analyzed in Management in Process Info (I think) and the timeout occurs in

    DELETE from TABLE1 where factor > 1.

    So all registers have factor > 1. I think that my table have 25000 registers.

     

    Thanks.

     

    Luiz Fernando

     

     

  • February 17, 2004 at 5:46 am

    #494331

    Ok, only way I know to speed this up is if you can alter the SQL being executed.  You won't see this documented very much, and i fact will often see advice to the contrary, but if you can tolerate these 25k rows being deleted in separate adjacent transactions rather than in one distinct unit or work, simple solution is open a cursor & delete 1 row at a time.  I have similar requirements in a couple places; simple DELETE stmt trying to delete a few thousand rows in busy prod environment never finishes, but the cursor delete will finish in less than 1 second. 

    Anyway, this only will work for you if you can change the query that's being run.

    If not, I think you might want to post new question that asks if this stmt can be optimized, given that you cannot modify this source code.  I cannot think of a solution, but there may be one.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply