none
Waiting Until The Stored Procedure Updates Before Executing Next Statement

    Question

  • I have a database with about 8 million rows.  I am preforming updates which are speratic through out the database. (they are in order but it may skip to the next 10th or 100th record before the next update)  The database has a clustered index on row_id.

    I read the data in with a data reader and only select the records I actually need into the reader.


    at the top i have declared a global stored procedure parameter such as this
    private SqlParameter m_sqlSPparm_1;
                    m_sqlSPparm_1 = sqlCmd.Parameters.AddWithValue(sarStoreProcParmValuesNames[i], string.Empty);



    Each time the function is called (For each record) I assign the stored procedure parameters

    m_sqlSPparm_1.value = "value i want to update with";
    After I assign all the stored procedure parameters I call this for each update.

                        sqlCmd.CommandText = stored_procedure_name;
                        sqlCmd.CommandType = CommandType.StoredProcedure;
                        sqlCmd.ExecuteNonQuery();


    Eventually i think SQL server is getting bogged down and eventually stops and results in a timeout error.  How can I wait until the stored procedure has updated before moving on to the next block?   How do I check to see if sql (queue) is being over loaded?  Is it possible to increase the queue size?




    here is the stored procedure

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

       ALTER PROCEDURE [dbo].[my_sp]
          @parm_1 bigint,
          @parm_2 char(40) ,
          @parm_3 char(40) ,
          @parm_4 char(30) ,
          @parm_5 char(2) ,
          @parm_6 char(10) ,
          @parm_7 char(40) ,
          @parm_8 char(4) 
    AS
    BEGIN
       SET NOCOUNT ON;

    UPDATE table_name_goes_here SET
          sql_field_1 = @parm_1,
          sql_field_2 = @parm_2,
          sql_field_3 = @parm_3,
          sql_field_4 = @parm_4,
          sql_field_5 = @parm_5,
          sql_field_6 = @parm_6,
          sql_field_7 = @parm_7
          WHERE @parm_row_id = row_id

       END
    • Moved by nobugzMVP Saturday, September 19, 2009 10:55 AM (From:Visual C# General)
    Saturday, September 19, 2009 3:27 AM

Answers

  • Hello,

    If you know the amount of rows that suppose to update you may consider a duration to delay the execution of .ExecuteNonQuery(), I guess you can guesstimate the duration and use 'System.Threading.Thread.Sleep' to delay it, you may do that on another thread, so the application main thread won't be blocked.

    That's just one way to optimize it.


    Eyal, Regards.

    blog.eyalsh.net
    Saturday, September 19, 2009 9:46 AM

All replies

  • Could you post the actual procedure, as this doesn't do a database update that you think it might be doing. It's just doing the ALTER PROCEDURE.

    Ron Whittle - If the post is helpful or answers your question, please mark it as such.
    Saturday, September 19, 2009 5:05 AM
  • my appolgies as I just opened up the query with modify stored procedure via sql

    the real code just calls that sp. the stored procedure is all ready created and is called over and over again.  eventually, it seems that sql server just gives up and everything times out

    I have the timeout set to 5000 which should be plenty of time to update a record

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

       CREATE PROCEDURE [dbo].[my_sp]
          @parm_1 bigint,
          @parm_2 char(40) ,
          @parm_3 char(40) ,
          @parm_4 char(30) ,
          @parm_5 char(2) ,
          @parm_6 char(10) ,
          @parm_7 char(40) ,
          @parm_8 char(4) 
    AS
    BEGIN
       SET NOCOUNT ON;

    UPDATE table_name_goes_here SET
          sql_field_1 = @parm_1,
          sql_field_2 = @parm_2,
          sql_field_3 = @parm_3,
          sql_field_4 = @parm_4,
          sql_field_5 = @parm_5,
          sql_field_6 = @parm_6,
          sql_field_7 = @parm_7
          WHERE @parm_row_id = row_id

       END
    Saturday, September 19, 2009 5:06 AM
  • Eventually i think SQL server is getting bogged down and eventually stops and results in a timeout error.  How can I wait until the stored procedure has updated before moving on to the next block?   How do I check to see if sql (queue) is being over loaded?  Is it possible to increase the queue size?

    To answer your original question, ExecuteNonQuery already waits for the database to finish before returning. I suspect you are doing something else that is causing the problem. Are some of the changes actually being made to the database, and by this I mean you can open the database in some other tool and see the changes? Can you give more of your code, particularly the sections that deal with the opening, closing, reading and setup of your SQL commands.

    Ron Whittle - If the post is helpful or answers your question, please mark it as such.
    Saturday, September 19, 2009 6:23 AM
  • yes the changes are being applied to the database.  its alot of code so Ill try to copy just the sql stuff into here


    connection string

    sConnection = @"server=" + m_sSqlServer + ";user id=" + sWindowsLoggedOnUser + ";Connect Timeout=5000;database=" + m_sSqlDatabase + ";integrated security = true";
    sqlConn = new SqlConnection(sConnection);
    sqlConnUpdates = new SqlConnection(sConnection);
    sqlCmd = new SqlCommand(sSql.ToString(), sqlConn);
    sqlCmdStoredProcedure = new SqlCommand(sSql.ToString(), sqlConnUpdates);
    sqlConn.Open();                                
    sqlConnUpdates.Open();
    sqlCmd.CommandTimeout = 5000;
    sqlCmdStoredProcedure.CommandTimeout = 5000;
    sqlCmdStoredProcedure.Parameters.Add(sStoredProcRowIdParm, SqlDbType.BigInt);
    sqlCmdStoredProcedure.Parameters.Add(sarStoreProcParmValuesNames[iMapCounter], SqlDbType.Char); // this line continues through all the parameters but i did not duplicate on this page.  all values have the correct parm names

    sqlCmd.CommandText = sSqlReaderSelect.ToString();    // load only the affected records into the reader
    sqlReader = sqlCmd.ExecuteReader();
    sqlCmdStoredProcedure.CommandText = sStoredProcName;
    sqlCmdStoredProcedure.CommandType = CommandType.StoredProcedure;

    // here is where i use the reader to pump data into a black box
    // the black box returns the formatted information and then i write it back out to the database updating the rows
    // the black box takes in text and output text only.  it has no sql interaction


    sqlCmdStoredProcedure.Parameters["parm_" + m_sMapUniqueRowId].Value = blackbox.rowId; // this line is replicated for the rest of the stored procedure parms

    sqlCmdStoredProcedure.ExecuteNonQuery();   // it keeps calling this (about 10k times a minute)


    thats all for the sql side


    Saturday, September 19, 2009 6:58 AM
  • Hello,

    If you know the amount of rows that suppose to update you may consider a duration to delay the execution of .ExecuteNonQuery(), I guess you can guesstimate the duration and use 'System.Threading.Thread.Sleep' to delay it, you may do that on another thread, so the application main thread won't be blocked.

    That's just one way to optimize it.


    Eyal, Regards.

    blog.eyalsh.net
    Saturday, September 19, 2009 9:46 AM