Lesson Learned #180: Creating a retry-logic for TSQL command execution.

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

As you know, one of the recommendations that we have in Azure SQL Database is to have a connection retry-logic, however, I would like to share with you other recommendation for command execution.

 

I developed the following piece of code that receives, four parameters. 

  • ConnectionString: Contains the connection string.
  • QueryText: Contains the query to execute.
  • retryIntervalSeconds: how much time the command timeout will be at the begining.
  • HowManyRetries: how many time the execution will be retried until fails.

 

Basically, as you could see in the example source code the operation will be:

 

  • 1) Connect to the DB. This function has a connection retry-logic that we shared in previous article.
  • 2) Try to execute the query (in this example will be NonQuery type) using the CommandTimeout specified in the parameter value called retryIntervalSeconds. 
  • 3) If the execution failed, the next command execution timeout will be multiple by 2.5. It and will be executed again until success or fails after retrying specified in the parameter value called HowManyRetries. 
  • 4) Also, I added an useful information, about how much the execution took, connection time, execution time and ConnectionID, etc..

 

Source code example:

 

 

public bool RunQueryWithRetriesNonQuery(string ConnectionString, 
                                                string QueryText,  
                                                int retryIntervalSeconds = 5, 
                                                int HowManyRetries = 5)
        {
            bool returnBool = false;
            int totalNrRows = 0;
            Stopwatch stopWatchWholeProcess = new Stopwatch();
            Stopwatch stopWatchRealTime = new Stopwatch();
            ClsRetryLogic oClsRetry = new ClsRetryLogic();
            C.SqlConnection SqlConnection = new C.SqlConnection();
            C.SqlCommand SqlCommand = new C.SqlCommand(QueryText, SqlConnection);

            SqlConnection.StatisticsEnabled = true;

            stopWatchWholeProcess.Start();

                for (int tries = 1; tries <= HowManyRetries; tries++)
                {
                 try
                 {
                    if (oClsRetry.ConnectDBWithRetry(ConnectionString, SqlConnection, false))
                    {

                        if (tries > 1) { retryIntervalSeconds = Convert.ToInt32(retryIntervalSeconds * 2.5); }

                        SqlCommand.CommandTimeout = retryIntervalSeconds;

                        Console.WriteLine("Executing the query {0} - Attempt Nr.:{1} - CommandTimeout: {2} - ConnectionID:{3}", QueryText, tries, SqlCommand.CommandTimeout, SqlConnection.ClientConnectionId);
                        Console.WriteLine();
                        stopWatchRealTime.Start();
                        totalNrRows = (int)SqlCommand.ExecuteNonQuery();
                        stopWatchRealTime.Stop();
                        stopWatchWholeProcess.Stop();

                        TimeSpan ts = stopWatchWholeProcess.Elapsed;
                        string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds / 10);

                        ts = stopWatchRealTime.Elapsed;
                        string elapsedTimeRealTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds / 10);

                        Console.WriteLine("Query Executed Text:{0} - Total ElapsedTime:{1} - Real Time:{5} - Rows:{2} - Tries:{3} - CommandTimeout:{4}",QueryText, elapsedTime, totalNrRows, tries, SqlCommand.CommandTimeout, elapsedTimeRealTime);
                        Console.WriteLine();

                        IDictionary currentStatistics = SqlConnection.RetrieveStatistics();

                        Console.WriteLine("---- BytesReceived: {0}",(long)currentStatistics["BytesReceived"]);
                        Console.WriteLine("---- BytesSent: {0}", (long)currentStatistics["BytesSent"]);
                        Console.WriteLine("---- ExecutionTime: {0}", (long)currentStatistics["ExecutionTime"]);
                        Console.WriteLine("---- ConnectionTime: {0}", (long)currentStatistics["ConnectionTime"] );

                        returnBool = true;
                        SqlConnection.Close();
                        break;
                    }
                    else
                    {
                        Console.WriteLine("Connectivity Error..");
                    }
                }
                catch (C.SqlException sqlExc)
                {
                    if (sqlExc.Number == -2)
                    {
                        Console.WriteLine("Query Error Exec:{0} Error Code:{1} Error Message:{2}", QueryText, sqlExc.Number, sqlExc.Message);
                        Console.WriteLine();
                        SqlConnection.ResetStatistics();
                        SqlConnection.Close();
                        stopWatchRealTime.Reset();
                        continue;
                    }
                    else
                    {
                        Console.WriteLine("---- Unknown Error: " + sqlExc.Number.ToString() + '-' + sqlExc.Message);
                        Console.WriteLine();
                        break;
                    }
                }
               }
            return returnBool;
        }

 

 

Output example: 

 

Jose_Manuel_Jurado_0-1640703375935.png

 

Enjoy!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.