7

I have a SqlDataAdapter that is being populated with 21 rows of data (4 columns). The sproc that drives it returns in a couple seconds in SQL Mgmt Studio, but the .Fill() takes 5 minutes.

    ArrayList ret = new ArrayList();
    SqlDataAdapter da = null;
    SqlCommand cmd = null;  
        cmd = base.GetStoredProc("usp_dsp_Stuff"); //Returns immediately in MSSMS.
        cmd.CommandTimeout = 3600; // Set to 6 min - debug only
        base.AddParameter(ref cmd, "@Param1", ParameterDirection.Input, SqlDbType.BigInt, 8, 19, 0, theParam1);
        base.AddParameter(ref cmd, "@Param2", ParameterDirection.Input, SqlDbType.BigInt, 8, 19, 0, theParam2);
        base.AddParameter(ref cmd, "@Param3", ParameterDirection.Input, SqlDbType.Char, 1, 'C');
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt); //Takes 5 minutes.

Any ideas?

Thanks in advance! -Chris

9 Answers9

7
da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 1800;
Ileana Martínez
  • 71
  • 1
  • 3
5

Recently, I experienced exactly this: .Fill timed out, but the same SP was superfast in SQL Server Management Studio. This is because your .NET app creates a SQL connection and uses SET ARITHABORT OFF, whereas SQL Server Management Studio uses SET ARITHABORT ON by default. This causes two different execution plans to be used, hence you were not able to reproduce this time-out in SQL Server Management Studio. I recommend you to take a look at your SP and make some changes.

Huy Hoang
  • 852
  • 9
  • 10
  • kudos - just ran into this today and was exactly this. Added SET ARITHABORT OFF to SSMS and reproduced my seemingly hung query. – Mark Nadig Mar 01 '19 at 20:16
  • I had a similar issue. The stored procedure was used to complete quickly in the Query analyzer and almost always used to timeout in the .NET. `SET ARITHABORT OFF` worked like a charm. – Rupesh Bhurke Feb 22 '22 at 11:43
4

Thank you for the help. The solution to this was to add with (nolock) statements on the joins that the sproc was using:

FROM category_tbl c INNER JOIN dbo.categoryItem_LNK cl WITH (NOLOCK) ON c.categoryid = cl.categoryid

I dont know why we were only seeing degradation when using the SqlDataAdapter, but this changed solved the problem right away.

Thanks again, Chris

  • That worked for me as well, I was getting a time out even after pulling 10 records from table valued function. – Sergey May 06 '09 at 00:12
  • I had the same problem running a query but using "WITH (NOLOCK)" didn't help. Adding "OPTION (RECOMPILE)" at the end of my query fixed the problem. – sharpguru Sep 10 '13 at 19:38
3

I know this is too late, like 7 years too late! but I came up against this issue today and wanted to share my fix. In my instance the data been pulled from SQL was a table valued function. The table valued function only returned about 3500 rows and took less than 1 second, but it timed out on the Fill() in the c# code. I don't know who or how it works but dropping and re-creating the function fixed it. I think it is something to do with how .NET reads data given by SQL, like the way a view is needed to be recreated if you make changes to it after it's been used in say a report. Again i;m not 100% sure whats happening behind the scenes but for me it was a quick fix

SQL_Kid
  • 79
  • 10
  • This solved it for me. Had a function call at the start of my sp that 'floors' and 'ceilings' date parameters. I removed the function and did the 'floor' and 'ceiling' manually, and it worked. – F0r3v3r-A-N00b May 20 '17 at 02:16
  • Turns out that simply dropping and recreating the stored procedure in our case fixed this exact issue. Just for safe measure we made sure (NOLOCK) was included everywhere. SQL Server's buggy now - Best to use MongoDB – sean2078 Jan 13 '18 at 15:43
1

Bad query plans and parameter sniffing. For a stored proc, and especially one where parameters will wildly adjust the rows read, a bad execution plan from looking at incoming parameters is the cause. It doesn't happen in SQL Management Studio because of different SET parameters.

This thread sums up your issue nicely: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9fd72536-f714-422a-b4c9-078e2ef365da/

This is a typical case of parameter sniffing. Your application most likely runs with different SET options (set by the client API) and uses a different execution plan than the one created in SSMS. What happens is when your procedure is invoke the first time via your application is creates execution plan based on the parameters passed. However, this execution plan may not be good for another set of parameter, which can result in poor performance when executed with the other set of parameters. See the following for more details and different solutions: http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

Here is more on the internals of plan caching and query plan reuse:
http://technet.microsoft.com/en-us/library/cc966425.aspx

debugme
  • 1,041
  • 1
  • 10
  • 22
1

I hate to break the news, but (NOLOCK) isn't a solution, it just creates new problems, such as dirty reads, missing/duplicated data, and even aborted queries. Locks in a SQL database are your friend.

If locking (or worse, blocking) was causing it to be slow, you compare the connection options running through SSMS and the ones used by your application. Use SQL Profiler to see how the code is being executed.

If any of those fields are large objects, keep in mind that SSMS automatically retrieves only a few hundred characters by default. The extra data returned could be a factor.

Bruce
  • 187
  • 2
  • 9
  • In reality, it depends on the use-case. Perhaps you could expand on `it just creates a new problem` to help the reader decide if these problems are serious enough to cause concern for their own use-case. – Dan Bechard Feb 25 '16 at 22:57
0

I used the following code and its duration is sqlCommclass.CommandTimeout I added before da.Fill (dt) because the request time was longer than 10 minutes

   using (SqlConnection myADONETConnection = new SqlConnection(vendor.Value))
     {
        using (SqlDataAdapter da = new SqlDataAdapter("", myADONETConnection))
         {
          ..
          ..
          ..
         da.SelectCommand = sqlCommclass;
                sqlCommclass.CommandTimeout = 30000;
                 da.Fill(dt);
          }
    }
Ali Bayat
  • 1
0

Other solutions didn't work for me. What did work was adding OPTION(RECOMPILE) at the end of my query, as suggested here: https://stackoverflow.com/a/14120510/1621118

batintherain
  • 63
  • 8
0

Fill() can sometimes be slow because .NET is analysing the data that comes back from the procedure.

Use the SQL Profiler to work out what SQL .NET is actually sending when the Fill() executes.

If it is sending a lot of SET statements, such as

set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off

etc...

.. then putting those same set statements into your stored procedure may speed things up.

codeulike
  • 22,514
  • 29
  • 120
  • 167