SQL server c# program slows down after some time.

Question

0.00/5 (No votes)

See more:

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.

C#

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();


 }

Posted 14-Apr-13 13:36pm

nitrous_007

Add a Solution

Comments

Bernhard Hiller 15-Apr-13 2:38am

using(SQLCOnnection ...) will close and dispose the connection object automatically when the using statment is left, also in case of errors.
Rerturn codes for different error conditions are a programming style of 1980ies - better use Exceptions, they'll also provide more information on what went wrong.
For your problem proper, I'd suggest to add some Logging statements just to find out which of the steps takes too much time: connection.open or cmd.ExecuteNonQuery

nitrous_007 15-Apr-13 9:31am

You mean return an exception object? instead of an integer or just use it get error information

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2013-04-14T18:18:00

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.

PrashantSonewane · Accepted Answer · 2013-04-14T19:39:00

C#

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();
    }
}

C#

// 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 server c# program slows down after some time.

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0