If i run a TSQL Statement in management studio and run the same the query through SqlDataReader, the latter gives the result faster than the former...
如果我在管理工作室中运行TSQL语句并通过SqlDataReader运行相同的查询,后者给出的结果比前者更快...
Any reason??
4 个解决方案
#1
One possibility is the connection state is different - in particular, the SET options etc - not just the obvious ones (stats, profiling, etc) - but even things like ANSI_NULLS can have a big impact on some queries (especially with xml columns, or peristed calculated columns).
一种可能性是连接状态不同 - 特别是SET选项等 - 不仅仅是明显的(统计数据,分析等) - 但即使像ANSI_NULLS这样的东西也会对某些查询产生很大影响(特别是对于xml列,或者peristed calculated columns)。
Also - what are you doing with the data when you read it? Are you displaying it? Storing it? Just dropping it? SSMS has to buffer it in a table-like mechanism to display in a grid... if you are parsing it into standard, typed classes (that already match the table layout) - or just dropping the row unprocessed, then you have less work to do.
另外 - 当你阅读数据时,你在做什么?你在展示吗?存储它?放下它? SSMS必须在类似于表的机制中缓冲它以在网格中显示...如果您将其解析为标准的类型类(已经与表布局匹配) - 或者只是删除未处理的行,那么您的工作就会减少去做。
From what I recall, it also updates the screen in batches - suggesting there is some threading going on... lots of variables here...
据我所知,它还会批量更新屏幕 - 表明存在一些线程......这里有很多变量......
#2
It could be related to the time that it takes to display the results in the SSMS? Which would probably be related to the size of the result set.
它可能与在SSMS中显示结果所需的时间有关?这可能与结果集的大小有关。
How big is the result set?
结果集有多大?
#3
Some thoughts:
The only way to know exactly is to use SQL Profiler to trap duration etc. SSMS require resource to take the result set and display it. What are you doing with the SQL Data Reader results?Or
确切知道的唯一方法是使用SQL事件探查器来捕获持续时间等.SSMS需要资源来获取结果集并显示它。您正在使用SQL数据读取器结果做什么?或者
Or, is this all on your PC, or are you running the SqlDataReader on a server?
或者,这一切都在您的PC上,或者您是否在服务器上运行SqlDataReader?
Or, are you running the query via SSMS first so the plan is cached and compiled, with data in memory?
或者,您是否首先通过SSMS运行查询,以便缓存和编译计划,并在内存中包含数据?
#4
The Tsql query I was running has no set based operations...I was just curious to check the time difference by running a select query on a server that gave out 163336 record sets with 15 columns of all sorts of datatyes. I noted the time at the status bar at the bottom of ssms where time is displayed.
我运行的Tsql查询没有基于集合的操作...我只是想通过在服务器上运行选择查询来检查时差,该服务器给出了163336个记录集,其中包含15列各种数据。我注意到ssms底部状态栏显示时间的时间。
I ran the same query with SqlDataReader too. ANd i was running the same select query multiple times (mind it the cases and spaces were all same)in both forms of query reader.
我也用SqlDataReader运行了相同的查询。和我在两种形式的查询阅读器中多次运行相同的选择查询(请注意案例和空格都相同)。
THe time taken by sqldatareader varied from 15-28 secs where as the query at the ssms varied from 29-31 secs...
sqldatareader所花费的时间从15-28秒不等,因为ssms的查询从29-31秒变化...
However I was not running them at the same time, but alternately and multiple times...so i dont think the network bandwidth or memory or CPU usage denies that the tsql executed with datareader gives the better performance than that executed in ssms
但是我没有同时运行它们,而是交替多次运行...所以我不认为网络带宽或内存或CPU使用率否认使用datareader执行的tsql比ssms执行的性能更好
#1
One possibility is the connection state is different - in particular, the SET options etc - not just the obvious ones (stats, profiling, etc) - but even things like ANSI_NULLS can have a big impact on some queries (especially with xml columns, or peristed calculated columns).
一种可能性是连接状态不同 - 特别是SET选项等 - 不仅仅是明显的(统计数据,分析等) - 但即使像ANSI_NULLS这样的东西也会对某些查询产生很大影响(特别是对于xml列,或者peristed calculated columns)。
Also - what are you doing with the data when you read it? Are you displaying it? Storing it? Just dropping it? SSMS has to buffer it in a table-like mechanism to display in a grid... if you are parsing it into standard, typed classes (that already match the table layout) - or just dropping the row unprocessed, then you have less work to do.
另外 - 当你阅读数据时,你在做什么?你在展示吗?存储它?放下它? SSMS必须在类似于表的机制中缓冲它以在网格中显示...如果您将其解析为标准的类型类(已经与表布局匹配) - 或者只是删除未处理的行,那么您的工作就会减少去做。
From what I recall, it also updates the screen in batches - suggesting there is some threading going on... lots of variables here...
据我所知,它还会批量更新屏幕 - 表明存在一些线程......这里有很多变量......
#2
It could be related to the time that it takes to display the results in the SSMS? Which would probably be related to the size of the result set.
它可能与在SSMS中显示结果所需的时间有关?这可能与结果集的大小有关。
How big is the result set?
结果集有多大?
#3
Some thoughts:
The only way to know exactly is to use SQL Profiler to trap duration etc. SSMS require resource to take the result set and display it. What are you doing with the SQL Data Reader results?Or
确切知道的唯一方法是使用SQL事件探查器来捕获持续时间等.SSMS需要资源来获取结果集并显示它。您正在使用SQL数据读取器结果做什么?或者
Or, is this all on your PC, or are you running the SqlDataReader on a server?
或者,这一切都在您的PC上,或者您是否在服务器上运行SqlDataReader?
Or, are you running the query via SSMS first so the plan is cached and compiled, with data in memory?
或者,您是否首先通过SSMS运行查询,以便缓存和编译计划,并在内存中包含数据?
#4
The Tsql query I was running has no set based operations...I was just curious to check the time difference by running a select query on a server that gave out 163336 record sets with 15 columns of all sorts of datatyes. I noted the time at the status bar at the bottom of ssms where time is displayed.
我运行的Tsql查询没有基于集合的操作...我只是想通过在服务器上运行选择查询来检查时差,该服务器给出了163336个记录集,其中包含15列各种数据。我注意到ssms底部状态栏显示时间的时间。
I ran the same query with SqlDataReader too. ANd i was running the same select query multiple times (mind it the cases and spaces were all same)in both forms of query reader.
我也用SqlDataReader运行了相同的查询。和我在两种形式的查询阅读器中多次运行相同的选择查询(请注意案例和空格都相同)。
THe time taken by sqldatareader varied from 15-28 secs where as the query at the ssms varied from 29-31 secs...
sqldatareader所花费的时间从15-28秒不等,因为ssms的查询从29-31秒变化...
However I was not running them at the same time, but alternately and multiple times...so i dont think the network bandwidth or memory or CPU usage denies that the tsql executed with datareader gives the better performance than that executed in ssms
但是我没有同时运行它们,而是交替多次运行...所以我不认为网络带宽或内存或CPU使用率否认使用datareader执行的tsql比ssms执行的性能更好