0

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);
Andrew
  • 149
  • 1
  • 13
  • Provide the query / stored procedure that is used to return the data. – Michael G Apr 27 '16 at 21:52
  • I had a very similar issue (raw SQL took 1/4 second, C# took 2-4 minutes) in a production database at my place of work. I troubleshot it with the DBA for several weeks hunting around the Internet for a solution and came up with nothing that worked, and I couldn't reproduce it in our dev and test databases. In the end we recreated the database and the issue vanished, and hasn't come back since. Never found out what the root cause was. – Ranger Apr 27 '16 at 21:54
  • Have you used the SQL profiler to see if its actually doing something unexpected? – BBauer42 Apr 27 '16 at 21:57
  • 2
    @NexTerren, This is a very well known issue: http://www.sommarskog.se/query-plan-mysteries.html Most often you find this in databases which exist for years and where upgraded with scripts. Some defaults of older versions (e.g. SQL Server 2000) where never changed (therefore a newly created database can be a solution). There is no easy one-liner to solve this... Sometimes it helps to set `ARITHABORT ON`, sometimes a `WITH RECOMPILE` helps, read about parameter sniffing ... – Shnugo Apr 27 '16 at 21:58
  • 1
    @Shnugo We _did_ try the ARITHABORT but not the RECOPILE. Glad to hear that there wasn't an easy one-liner fix and that (I guess) we ended up doing the right thing. Just wished we would have came to that earlier! – Ranger Apr 27 '16 at 22:33
  • https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/ – bic Apr 27 '16 at 23:00
  • there's also faster way to do this, rather than using datatable, use datarow since there's a where clause, in your SELECT statement,.. Datatable creates a temporary table and then starts to check for the where clause before it returns a list, compared to datarow, it immediately creates a list of data that satisfies the where clause and returns the list.. and if it returns so many list, put your datarow inside a foreach so that it won't cause the app to crash – JC Borlagdan Apr 28 '16 at 01:54

2 Answers2

1

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

SQL Server - parameter sniffing

Community
  • 1
  • 1
bic
  • 2,201
  • 26
  • 27
0

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();
Community
  • 1
  • 1
Andrew
  • 149
  • 1
  • 13