CODEWITHSUNDEEP
c#mysqlsql.netdatabase
ParkPark
ParkPark

Reputation: 13

Sql request in a loop slow down c# app

My app is used to automatize a restore of a database.

I have created a function that get the percentage of completion of the restore.

The problem is that the more its executed the slower it gets. The performances of my computer became really bad and everything is really slow.

I think there is a problem in the resquest.

private void GetPercentRestore(string dbName, string query)
    {
            // T-SQL command to have information on the backup
            query = query.Replace("'", "''");
            string cmd =
                $"SELECT sysdb.NAME AS [Name], dmv.PERCENT_COMPLETE AS [PercentComplete] FROM sys.databases sysdb INNER JOIN sys.dm_exec_requests dmv ON sysdb.database_id = dmv.database_id CROSS APPLY sys.dm_exec_sql_text(dmv.SQL_HANDLE) dest WHERE TEXT LIKE '{query}';";
            int percent = 0;
            int lastPercent = -1;

            // Initialize the new connection : need because two query at the same time
            SqlConnection conn =
                new SqlConnection(
                    System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionCluster"].ToString());
            SqlCommand command = new SqlCommand(cmd, conn);
            command.CommandTimeout = 3600;


            try
            {

                while (percent < 100)
                {
                    conn.Open();
                    // Execute the command
                    var result = command.ExecuteReader();

                    // Count the total of the size of the backup
                    if (result.HasRows)
                    {
                        result.Read();
                        //ProgressBarViewModel e = new ProgressBarViewModel();
                        percent = Convert.ToInt32(result["PercentComplete"]);
                    }
                    else // if not result, assume that Restore is complete
                        percent = 100;


                    if (percent != 100)
                        Thread.Sleep(10000);

                    result.Close();
                    conn.Close();
            }

            }
            catch (Exception e)
            {
                LogFile.WriteError(e.Message);
            }
        conn.Close();
    }

Upvotes: 0

Views: 965

Answers (1)

Sudip
Sudip

Reputation: 687

Of course, this code is slow. You are doing these following things for each loop cycle of a while loop-

  1. Opening an SQL connection. (This takes time according to the speed of your network)

  2. Executing a query which contains INNER JOIN, CROSS APPLY and WHERE clause.

Your first option would be Opening the connection outside of the loop. That will instantly improve some performance.

the second option would be removing this inline query and writing a Stored Procedure in your Data Base itself and call that stored procedure instead.

Let me know if you have any trouble doing that.

Upvotes: 1

Related Questions