SQL Server Performance

SQL Timeout in VB6 ASP ADO

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by pbasile, Sep 5, 2006.

  1. pbasile New Member

    I'm having a timout issue with SQL 2000 in a VB app.

    I have a VLDB OLTP. The table that will grow the most, continuously is t_trend.
    currently storing 3 days of info at 17 million rows, need to store 3 months data = 30 * 17 million = 510 million rows, but the rows are small.

    The inserts are slowing down already and I'm getting database timeouts in ASP ADO.

    I have 2 tables. t_PollData and t_Trend. I collect the data into t_PollData and then copy it to t_Trend, and delete from t_PollData.
    This happens about 1200 rows at a time, 24 x 7.

    Here is the ASP statement that does the insert
    strSQL = "Insert into t_Trend Select ModScanID, PDUPanelID, CTNo, PollDateTime, CTmA, CTWatts From t_PollData"
    DataCmd.Execute strSQL

    Here is the table schema, there is a single clustered index on ID.

    CREATE TABLE [dbo].[t_Trend] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [ModScanID] [int] NULL ,
    [PDUPanelID] [int] NULL ,
    [CTNo] [int] NULL ,
    [PollDateTime] [datetime] NULL ,
    [CTmA] [int] NULL ,
    [CTWatts] [int] NULL
    ) ON [PRIMARY]

    We are getting timeouts on the insert. What can I do to speed things up / extend the timeout ?

  2. ghemant Moderator

  3. Adriaan New Member

    Perhaps file growth factor is set as a percentage, and the file size is already so big that it takes a lot of time to add x% of file size.

    How many rows are you inserting at a time?
  4. pbasile New Member

    inserting about 1200 rows at a time.

  5. Adriaan New Member

    Yes, but what about the other suggestion.
  6. pbasile New Member

    SQL data file size is 300 MB and growth is set to 10%

  7. satya Moderator

    Why don't you increase the data file size to 1 or 2 gb at a time and then continue the process. (as what Adriaan referred above).

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  8. cmdr_skywalker New Member

    set the connection timeout to 0 (for infinite value) or set the timeout to acceptable value (you decide what is acceptable value to you). You can specify the timeout value on the openrecordset function. If VB programming won't work, try the SQL side. Depending on the requirements, you may try the following:
    1) Use a stored procedure, invoke it async and return to VB. The appearance of fast may not necessarily mean you have to perform all the back load before letting the user continue using the interface. Using this technique, you may let the user continue the work while working in the background. Of course, if you need the result to continue, that is another story. You decide which is which.
    2) set a working thread on the SQL Server side, add a message to que, and let the working thread pickup the message and process the information. Again, assuming you don't need the result to continue
    3) use partitioned view, try inserting records on new tables (you may determine if it is weekly/montly/quarterly/yearly). Numerous thread in this site deal with the partitioned view.

    The code below can help in creating quick table. Use alter statement to enforce the check constraint.

    SELECT TOP 0 * INTO t_Trend_200607
    FROM dbo.t_Trend

    The suggestion above may or may not help you. It depends on what your business requirements are.
    But timeouts occur when process can't get the resources needed within a specified time.
    Check if you need to:
    (1) set a longer time,
    (2) expedite the running process in releasing resources, or
    (3) let another process wait for the resource while the current process move on

    or all of the above. Hope this helps.

    May the Almighty God bless us all!

Share This Page