CODEWITHSUNDEEP
.netsql-servertimeout
richard
richard

Reputation: 12526

Query takes 1.5 seconds in SSMS, but times out when run from .NET windows app?

I have a query that is timing out when I run it from my windows application. However it runs fine, in under 2 seconds when I run it directly in SSMS. All other queries run fine in the app. I have also run profiler and confirmed that the app is indeed running the query I expect it to (when I take the code that profiler shows was run by the app and run it in SSMS, it takes less than 2 seconds).

Longer running queries do not time out in the app.

What is going on?

    string result = dbHelper.GetTypedDataTable(dtMyProjects, "sp_get_DashboardPMMyProjects", MyUtility.DWConnection, new string[] { "'" + shortNTID + "'" });

    static public string GetTypedDataTable(DataTable dt, string sSPName, string connectionName, string[] arrayParameters)
    {
        if (connectionName == "default")
        {
            SetUpConnection(sSPName, "sqlConn.ConnectionString", "SP");
        }
        else
        {
            SetUpConnection(sSPName, connectionName, "SP");
        }

        cmdSELECT.CommandText += " " + arrayParameters[0].ToString();

        int countParameters = arrayParameters.Length;

        for (int i = 1; i < countParameters; i++)
        {
            cmdSELECT.CommandText += ", " + arrayParameters[i].ToString();
        }

        return FillDataTable(dt);
    }

    private static void SetUpConnection(string sSPName, string connectionName, string sqlType)
    {
        //sqlConn.ConnectionString = ((string)(configurationAppSettings.GetValue("sqlConn.ConnectionString", typeof(string))));

        sqlConn.ConnectionString = ((string)(configurationAppSettings.GetValue(connectionName, typeof(string))));
        daHlpr.SelectCommand = cmdSELECT;
        cmdSELECT.Connection = sqlConn;

        if (sqlType == "SP")
        {
            cmdSELECT.CommandText = "EXEC " + sSPName;
        }
        else
        {
            cmdSELECT.CommandText = sSPName;
        }
    }

    private static string FillDataTable(DataTable dt)
    {
        try
        {
            daHlpr.SelectCommand.CommandTimeout = 60;
            daHlpr.Fill(0, 1000000000, dt);
        }
        catch (Exception ex)
        {
            return ex.Message.ToString();
        }
        return dt.Rows.Count.ToString(); //return the count of rows in the table being returned
    }

Upvotes: 1

Views: 1915

Answers (4)

Brian Webster
Brian Webster

Reputation: 30865

Two seconds is a pretty long time these days, there must be a decent amount of processing required.

If you are using a stored procedure, you may need to disable parameter sniffing.

Basically, store each SP parameter in a newly instantiated variable immediately upon SP execution. Here's a link with more details:

http://sqlserverperformance.wordpress.com/2007/06/07/how-to-disable-parameter-sniffing-in-sql-server-2005/

Also, though it may seem obvious, sometimes it is easy to forget. These problems may evaporate upon Dev Machine Reboot, Sql Server Service Restart, or Sql Server Machine Reboot.

Upvotes: 4

richard
richard

Reputation: 12526

SQL Server was running at maximum RAM and as the day progressed, more and more queries were taking forever. Eventually none would run. Recycled SQL Server to reclaim the memory, and now I will have to diagnose why it was using so much RAM. Thanks all!

Upvotes: 1

DForck42
DForck42

Reputation: 20367

try dropping and recreating the stored procedure.

My similar post

Upvotes: 2

Jesus Ramos
Jesus Ramos

Reputation: 23266

Are you correctly executing a reader or non reader command? A simple typo such as this can cause it to timeout waiting for a response an an insert or update query. Some code might also help with finding the issue.

Upvotes: 1

Related Questions