locked
How do I set the timeout value for VB? RRS feed

  • Question

  • User-1809947162 posted

    (My environment:  Visual Studio 2008 Standard with SQL Server 2005)

    I have developed a web site (www.nzgdb.co.nz) that includes some long-running SQL processes.   Currently this is managed by a SQL script, run in the early hours of the morning directly under the control of SQL.   I want to move these processes to a background windows service, and I am now well through the process of writing the worker task (in VB) to be controlled by the windows service controller program but I am getting timeouts on some tasks that invoke a long SQL query.  I need a way of saying "no timeout", or if that is not possible setting a large timeout value.

    The simpler long-running queries are written directly as SQL within a queriestableadaptor, while the more complex queries are written as SQL procedures invoked from the queriestableadaptor.

    I tried adding "Connect Timeout=0" to the connection string in the relevant app.config, but this didn't make any difference.

    Thank you, Robert Barnes. 

     

     

    Tuesday, July 29, 2008 2:10 AM

Answers

  • User436930739 posted

    Is this something I write into the project settings?   What are the keywords that I should use? 

    As I said, you can create a key in the config file to hold the command timeout value that you want to set for your command object in your code.  Below are some reference links for this.

    http://forums.asp.net/t/1293504.aspx

    http://forums.asp.net/t/1203576.aspx

    I'm not too sure about the QueryTableAdaptor that you said you're working with, but I think if you get access to the underlying command object of this object then you can easily increase the timeout value.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 29, 2008 5:19 AM

All replies

  • User436930739 posted

    I tried adding "Connect Timeout=0" to the connection string in the relevant app.config, but this didn't make any difference.
     

    Connection timeout is different from command timeout.  The connection timeout simply suggests the amount of time for which the connection should remain open, the command timout suggests the amount of time the query/procedure should keep executing after which the time out error will be raised.  Unfortunately there is no direct way of setting the command timeout ( as it is for connection timeout by specifying the value in the connection string ).  So, you'll have to think of some alternative approach for this, like defining a key in the config for providing the value and then using that key for the timeout property of the command object.

    Which version of VB are you using ? I mean, is it the older VB 6.0 or the .Net version.

    On a side note, you may even try re-visiting your queries to find out which are the ones running longer.  You may then try and set proper indexes on the underlying tables.

    Tuesday, July 29, 2008 3:08 AM
  • User570848712 posted

    Hi  Robert,

    From  what   i have  understood ,  you  want  to  track  the   proceedings  of  the  Windows service....  Am  i  right .....

    And  one  more  thing   ....by   timeout  do  you  mean  to say  that  its  getting   stopped..... 

    Please  confirm  so  as  to  help  you  out...

    Tuesday, July 29, 2008 3:23 AM
  • User-1809947162 posted

    >So, you'll have to think of some alternative approach for this, like defining a key in the config for providing the value and then using that key for the timeout property of the command object.

    Is this something I write into the project settings?   What are the keywords that I should use? 

    >Which version of VB are you using ? I mean, is it the older VB 6.0 or the .Net version.

    I using VB.Net (and ASP.Net), with .Net 3.5. 

    >On a side note, you may even try re-visiting your queries to find out which are the ones running longer.  You may then try and set proper indexes on the underlying tables.

    Please give me some credit!   I have already spent a lot of time checking out the queries, optimizing them, adding indexes etc.  Execution times for each query are collected into a table,  and I have used this to measure times, correlate it with the number of records being inserted or updated. From the database of past results I can predict what the query times will be: they are typically a few minutes, but with a large process can run up to about 30 minutes.

    The only way that I can think of to speed up the queries, apart from faster hardware, is to break the queries into small steps, for example inserting no more than 500 records at a time.  Of course this would add to the total load, possibly significantly, even if  I succeeded in bringing each part-query under the default time.  This has to be a very poor "solution" to my problem of not knowing how to set the timeout limit.  In fact,


    Tuesday, July 29, 2008 3:49 AM
  • User-1809947162 posted

     I probably will want to track the proceedings of the windows service, but that's not the current problem, as I have built a lot of tracking into my worker task and I can see the application log and various status codes and database tables that are created as the queries execute.   The current issue is that when I execute this code in my VB worker task

                  tqBGControl.GED48CopyFacts()

    it fails with message

            Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    tqBGControl is a QueryTableAdaptor.    GED48CopyFacts is a query that will execute

        insert gdbfact (FACTID,Facttag,FactIndiid,FactDescription,Factdate,FactDateString,Factdatelastedit,FactSubTag)
                    select  FACTID,Facttag,FactIndiid,FactDescription,Factdate, FactDateString,Factdatelastedit,FactSubTag
                        from GEDFact

    The immediate problem is to allow this to run long enough to complete. 

               



     

    Tuesday, July 29, 2008 4:09 AM
  • User436930739 posted

    Is this something I write into the project settings?   What are the keywords that I should use? 

    As I said, you can create a key in the config file to hold the command timeout value that you want to set for your command object in your code.  Below are some reference links for this.

    http://forums.asp.net/t/1293504.aspx

    http://forums.asp.net/t/1203576.aspx

    I'm not too sure about the QueryTableAdaptor that you said you're working with, but I think if you get access to the underlying command object of this object then you can easily increase the timeout value.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 29, 2008 5:19 AM
  • User-1809947162 posted

     Thanks for this dhimant.  The problem has been solved with this code, replacing the Data Access Layer call

            'tqBGControl.GED48CopyFacts()  '    This times out, so is replaced by an in-line command
    with direct use of a SQLCommand: -
            Dim conn As SqlConnection = New SqlConnection(My.Settings.GDBConnectionString)
            Dim cmd As SqlCommand = New SqlCommand _
                ("insert gdbfact (FACTID,Facttag,FactIndiid,FactDescription,Factdate,FactDateString,Factdatelastedit,FactSubTag) " & _
                    "select  FACTID,Facttag,FactIndiid,FactDescription,Factdate, FactDateString,Factdatelastedit,FactSubTag " & _
                        "from GEDFact", conn)
            cmd.CommandTimeout = 0
            conn.Open()     '   Rely on overall Try/Catch in BCProcess to trap errors.
            cmd.ExecuteNonQuery()
            conn.Close()
    This works fine.  However if anybody can help me by showing me how to set the CommandTimeout for the DAL method I'd be grateful, as I'd prefer to stick with the 3-tier architecture. 
    When I open the .XSD of the table adapters then I see only the graphical view, and in contrast to the .XSD's within the web site, View Source for the XSD's within the VB project show me only VB source,
    not the XML generated from the table adapters. (In any event, one shouldn't mess around with generated code). Although you can right-click the query GED48CopyFacts and view its properties,
    there is no CommandTimeout property to reset.
    Tuesday, July 29, 2008 6:46 PM
  • User436930739 posted

    cmd.CommandTimeout = 0
     

    A bit of comment here first.  As you might already know, setting up 0 as the timeout will keep the command running for indefinite time ( or until your connection gets timed out, whichever is lesser ).

    However if anybody can help me by showing me how to set the CommandTimeout for the DAL method I'd be grateful, as I'd prefer to stick with the 3-tier architecture.
    When I open the .XSD of the table adapters then I see only the graphical view, and in contrast to the .XSD's within the web site, View Source for the XSD's within the VB project show me only VB source,
    not the XML generated from the table adapters. (In any event, one shouldn't mess around with generated code). Although you can right-click the query GED48CopyFacts and view its properties,
    there is no CommandTimeout property to reset.
     

    Well, I'm interested in finding out any scope of setting this up in you 3 tier arch using DAL.  I'm not sure upto which extant you're using the generated code in your DAL, but we can try and set it up there, if at all there is any hope/way of setting it up.

    Wednesday, July 30, 2008 1:12 AM