填充()超时——底层Sproc快速返回

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.

我有一个SqlDataAdapter，它填充了21行数据(4列)。在SQL Mgmt Studio中驱动它的sproc在几秒钟内返回，但是. fill()只需要5分钟。

    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

提前谢谢!屁股的

6 个解决方案

#1

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

我知道这太迟了，就像7年太迟了!但是今天我遇到了这个问题，我想和大家分享我的解决方案。在我的实例中，从SQL中提取的数据是一个表值函数。表值函数只返回大约3500行，花费的时间少于1秒，但是它在c#代码的Fill()上超时。我不知道它是怎么工作的，但它会掉下来重新创建这个函数。我认为这与。net如何读取SQL提供的数据有关，就像在报表中使用视图之后对其进行修改时需要重新创建视图一样。再说一遍，我不能百分之百地确定幕后发生了什么，但对我来说，这是一个快速的解决办法

#2

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

谢谢你的帮助。解决这个问题的方法是在sproc正在使用的连接上添加(nolock)语句:

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

从category_tbl c内部连接dbo。category _lnk cl与(NOLOCK)在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.

我不知道为什么我们在使用SqlDataAdapter时只看到了降级，但这一变化立即解决了问题。

Thanks again, Chris

再次感谢,克里斯

#3

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.

我讨厌打破这个消息，但是(NOLOCK)并不是一个解决方案，它只会产生新的问题，比如脏的读取、丢失的/重复的数据，甚至是中止的查询。SQL数据库中的锁是您的朋友。

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.

如果锁(或者更糟的是，阻塞)导致它变慢，您可以比较运行在SSMS中的连接选项和应用程序使用的连接选项。使用SQL分析器查看代码是如何执行的。

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.

如果这些字段中有任何一个是大型对象，请记住，SSMS默认只检索几百个字符。返回的额外数据可能是一个因素。

#4

da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 1800;

#5

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

Fill()有时会很慢，因为. net正在分析从过程返回的数据。

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

使用SQL Profiler计算出当Fill()执行时SQL . net实际发送的是什么。

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

如果它正在发送大量的SET语句，例如

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.

. .然后将相同的set语句放入存储过程中可能会加快速度。

#6

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.

糟糕的查询计划和参数嗅探。对于存储的proc，特别是在参数将大幅调整行读取的地方，一个糟糕的执行计划从查看传入参数就是原因。由于设置参数不同，在SQL Management Studio中不会发生这种情况。

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

这个线程很好地总结了您的问题: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

这是参数嗅探的典型例子。您的应用程序很可能使用不同的设置选项(由客户端API设置)运行，并使用不同于SSMS中创建的执行计划。当您的过程第一次通过应用程序调用时，会根据传递的参数创建执行计划。但是，这个执行计划可能不适合另一组参数，这可能导致在执行其他参数集时性能不佳。请参阅以下内容以获得更多详细信息和不同的解决方案:http://pratchev.blogspot.com/2007/08/parame-sniff.html。

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

这里有更多关于计划缓存和查询计划重用的内部信息:http://technet.microsoft.com/en-us/library/cc966425.aspx

#1

#2