CODEWITHSUNDEEP
c#sqlsql-serverperformance
Andrew
Andrew

Reputation: 149

Why does SQL Procedure takes 3secs. in SSMS, minutes in C#

If I run a stored procedure in SSMS, it takes threee seconds. Calling the same procedure from C# takes minutes to return. I suspect that the longer execution times are the result of the number of rows returned. One parpameter case that does not return (in the time that I have been willing to wait) returns 38,000 rows.

How do I speed up queries that return a lot of rows?

Thanks.

Here is the C#:

DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60000;
cmd.Parameters.Add("@Family_Code", SqlDbType.VarChar).Value = "SPCF";
SqlDataReader dr = cmd.ExecuteReader();  // takes 'forever'
dt.Load(dr);
ds.Tables.Add(dt);

Upvotes: 0

Views: 154

Answers (2)

Andrew
Andrew

Reputation: 149

Thanks for all the suggestions. This turned out to improve things. Query times reduced to an acceptable < 5 secs.

I added the lines below to the C# c ode just before the main query. The idea is to set the setting used by SSMS.

SqlCommand cmdOption1 = 
        new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", conn);
int rc = cmdOption1.ExecuteNonQuery();

Upvotes: 0

bic
bic

Reputation: 2281

Remove the parameter execution plans by mapping them to local variables.

SQL Server - parameter sniffing

Upvotes: 1

Related Questions