SqlDataAdapter.Fill() Timeout - Underlying Sproc Returns Quickly
Asked Answered
L

10

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

Lounging answered 20/4, 2009 at 16:47 Comment(0)
S
9
da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 1800;
Spraddle answered 3/8, 2017 at 2:51 Comment(1)
Please see this first how-to-answerDiffractometer
B
6

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.

Beaune answered 17/7, 2018 at 9:48 Comment(2)
kudos - just ran into this today and was exactly this. Added SET ARITHABORT OFF to SSMS and reproduced my seemingly hung query.Somersault
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.Evidentiary
L
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

Lounging answered 20/4, 2009 at 18:40 Comment(2)
That worked for me as well, I was getting a time out even after pulling 10 records from table valued function.Mantua
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.Maui
U
4

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

Uziel answered 17/2, 2017 at 11:20 Comment(2)
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.Veinlet
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 MongoDBEncephalogram
D
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.

Desmoid answered 22/4, 2009 at 19:3 Comment(1)
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.Compliancy
C
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

Compliant answered 3/2, 2010 at 15:53 Comment(0)
H
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.

Heeled answered 20/4, 2009 at 17:4 Comment(0)
S
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);
          }
    }
Sponson answered 27/1, 2022 at 9:3 Comment(0)
R
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://mcmap.net/q/1475385/-dataadapter-fill-dataset-timeout-exception-on-a-specific-database

Raynor answered 17/5, 2022 at 7:4 Comment(0)
C
0

In my case, the problem was a lock on the respective table. (nolock) didn't help.

Costanzia answered 18/10, 2023 at 10:26 Comment(1)
You seem to be telling others only that another answer didn't work. If instead you are saying that you solved your problem when you realized that the problem was a held lock on one of your tables, please state this explicitly and, if possible, explain what investigation you did that revealed this as your problem.Mixedup

© 2022 - 2024 — McMap. All rights reserved.