SqlDataReader 性能下降 [英] Slow performance of SqlDataReader

问题描述

我在 MSSMS 中查询执行约 2 秒(返回 25K 行)

I've query executing ~2 secs in MSSMS (returning 25K of rows)

在 .NET (sqlReader) 中使用的相同查询执行几分钟！

Same query used in .NET (sqlReader) exetuting few minutes!

我也试过只执行 reader

I've also tried to execute only reader

(注释掉 while 循环中的所有代码，只留下 reader.Read() ) - 还是一样！

(commented all code in while loop just leaving reader.Read() ) - still same!

知道怎么回事吗?

我不是 DBA，也没有资格使用 Profiler - 会询问我的 DBA 并让所有人知道.

I'm not DBA and not priviledged to play with Profiler - will ask my DBA and let all know.

与此同时，我注意到在将WITH RECOMPILE"参数添加到我正在谈论的 SP 后，性能得到了显着提升

In the meantime I'm noticed essential performance boost after adding "WITH RECOMPILE" param to SP I'm talking

所以，从我的角度来看，执行计划似乎就是这种情况......你怎么看?

So, from my perspective it seems to be the case with execution plan... What do you think?

我还检查了以下来自 QA 和 .NET 的查询

Also what I've checked was performing below query from QA and .NET

select @@options

我的理解是它应该为两个环境返回相同的值.(如果不是不同的 ex.plans 将被使用)我说得对吗?

My understanding is it shall return same value for both environements. (If not differnet ex.plans will be used) Am I right?

我读过(来自 http://www.sqldev.net/misc/fn_setopts.htm) 在 QA 中 ARITHABOIRT=ON(在 .NET 中它是关闭的)

I've read (from http://www.sqldev.net/misc/fn_setopts.htm) that ARITHABOIRT=ON in QA (in .NET it is off)

有人知道如何为每个 .NET 连接强制 ARITHABOIRT=ON 吗?

Does enybody know how to force ARITHABOIRT=ON for every .NET connections?

推荐答案

我将在 SQL Server Profiler 中设置跟踪，以查看从 .NET 代码连接时连接使用的 SET 选项设置，以及正在使用的设置在 SSMS 中.通过 SET 选项设置，我的意思是

I would set up a trace in SQL Server Profiler to see what SET options settings the connection is using when connecting from .NET code, and what settings are being used in SSMS. By SET options settings, I mean

ARITHABORT
ANSI_NULLS
CONCAT_NULL_YIELDS_NULL
//etc

查看 MSDN选项表

我之前见过选项不同的问题(在那种情况下，ARITHABORT)并且性能差异很大.

I have seen the problem before where the options were different (in that case, ARITHABORT) and the performance difference was huge.

这篇关于SqlDataReader 性能下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！