SQL服务器c＃程序在一段时间后会变慢。 [英] SQL server c# program slows down after some time.

问题描述

我有一个sql C＃程序，它具有以下代码，在调用该函数超过1,000次后会减慢很多。最初每次迭代只需要100毫秒，但经过大量迭代后，每次迭代似乎需要1-2秒。

你能让我知道可能出现什么问题吗？



我没有在控制台输出上出现任何错误错误标记STB，所以它不是1秒睡眠。

I have an sql C# program that has the following code which slows down a lot after calling the function over 1,000 times. Intially it only takes only 100 milliseconds for each iteration but after a lot of iterations, each iteration seems to take 1-2 seconds.
Can you let me know what could be wrong?

I don''t get any error on the console output " Error Marking STB " , so its not the 1 second sleep.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
    }
    catch
    {

        connection.Close();

        return 5;
    }
    string headend_query = "Headend1";
   

    string query3 = string.Format(@"UPDATE [STB_Details] SET [Delete_Status]= @status WHERE ([Serial_Number]= @serial_number AND [Status]='FINISHED' AND [TimeofTansaction_End]<=@end_time  AND [Error_Code]=0 AND  [Delete_Status]!=@status) ", headend_query);
    
        SqlCommand cmd = new SqlCommand(query3, connection); 
        cmd.CommandType = System.Data.CommandType.Text;   

        try
        {
            cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.NVarChar)).Value = status;
        }
        catch
        {
            connection.Close();
            return 33331;
        }
        try
        {
            cmd.Parameters.Add(new SqlParameter("@date_time", SqlDbType.DateTime)).Value = DateTime.Now;
        }
        catch
        {
            connection.Close();
            return 33332;
        }
        try
        {
            cmd.Parameters.Add(new SqlParameter("@serial_number", SqlDbType.NVarChar)).Value = serial_number;
        }
        catch
        {
            connection.Close();
            return 33333;
        }
        try
        {
            cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.DateTime)).Value = end_time;
        }
        catch
        {
            connection.Close();
            return 33334;
        }
        try
        {
            int rowsAffected = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {

            Console.WriteLine(" Error Marking STB");
            
            try
            {
                Thread.Sleep(1000);
                int rowsAffected = cmd.ExecuteNonQuery();
               
            }
            catch
            {
               
                errormessage = ex.Message + System.Environment.NewLine + ex.InnerException + System.Environment.NewLine + " Status- " + status + " Serial Number " + serial_number + " EndTime- " + end_time.ToString();
                connection.Close();
                return 6;
            }

        }
        connection.Close();


 }

推荐答案

首先，我不确定你为什么要打电话这是1000次。

其次，如果您的要求是必须这样做而没有其他选择，那么在方法之外建立数据库连接。

在每次通话中建立连接是一项昂贵的开销。



尝试一起调用一组更新，而不是单独调用每个更新实例。这将需要重构您的代码。

First, I''m not sure why you should be calling this a 1000 times.
Second, if your requirement is such that you have to do so and there is no alternative, then establish the database connection outside of the method.
Establishing the connection in every single call is an expensive overhead.

Try and call a set of updates together rather than calling every instance of update individually. This will require refactoring of your code.

protected void MainMethod()
{
    // This is main mathod

    // Create connectionstring
    string connectionString = string.Empty;

    // Opening the connectionstring
    SqlConnection connection = new SqlConnection(connectionString);
    connection.Open();

    // Calling submethod to call the insertion 1000 times
    try
    {
        for(int i=1;i<=1000;i++){
            SaveData1000Times(connection);
        }
    }
    catch
    {
        // If something goes wrong anywhere, you should be returning custom errors based on error code/message
        string errrMessage = "Something wrong";
    }
    finally
    {
        // Finally closing the connection
        if(connection.State == System.Data.ConnectionState.Open)
            connection.Close();
    }
}

// This is submethod. Note that there is no try catch block used as it should return error back to caller
        protected void SaveData1000Times(SqlConnection connection)
        {
            using (connection)
            {

                    if(connection.State != System.Data.ConnectionState.Open)
                        connection.Open();

                    string headend_query = "Headend1";
                    string query3 = string.Format(@"UPDATE [STB_Details] SET [Delete_Status]= @status WHERE ([Serial_Number]= @serial_number AND [Status]='FINISHED' AND [TimeofTansaction_End]<=@end_time  AND [Error_Code]=0 AND  [Delete_Status]!=@status) ", headend_query);
                    SqlCommand cmd = new SqlCommand(query3, connection);
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.NVarChar)).Value = status;
                    cmd.Parameters.Add(new SqlParameter("@date_time", SqlDbType.DateTime)).Value = DateTime.Now;
                    cmd.Parameters.Add(new SqlParameter("@serial_number", SqlDbType.NVarChar)).Value = serial_number;
                    cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.DateTime)).Value = end_time;
                    int rowsAffected = cmd.ExecuteNonQuery();

        }

希望这对你有帮助！

Hope this help you!

这篇关于SQL服务器c＃程序在一段时间后会变慢。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！