为什么一个数据库查询只能走在应用程序慢？ [英] Why does a database query only go slow in the application?

问题描述

我有一个网页时需要10分钟，对数据库运行一个查询，但在不到一秒钟同一查询返回从SQL Server Management Studio中运行时。

I have a webpage that takes 10 minutes to run one query against a database, but the same query returns in less than a second when run from SQL Server Management Studio.

该网页是刚烧制SQL在数据库正在执行一个存储过程，而这又是在执行一个非常简单的选择了四个表。再次的代码是基本ADO，上的SqlCommand设置在CommandText然后执行的ExecuteReader获取数据

The webpage is just firing SQL at the database that is executing a stored procedure, which in turn is performing a pretty simple select over four tables. Again the code is basic ADO, setting the CommandText on an SqlCommand and then performing an ExecuteReader to get the data.

该网页正常工作迅速，但是当它减慢只有得到它加速的方法就是进行碎片整理对表的索引被查询（不同的人不同的时间），这似乎没有什么意义，当相同的查询手动执行如此之快。

The webpage normally works quickly, but when it slows down the only way to get it speeded up is to defragment the indexes on the tables being queried (different ones different times), which doesn't seem to make sense when the same query executes so quickly manually.

我有看这个问题但它并不适用，网页简直是刚在数据库射击文本。

I have had a look at this question but it doesn't apply as the webpage is literally just firing text at the database.

有没有人有，为什么这是怎么回事缓慢的方式有什么好的想法，而不是其他？
谢谢

Does anyone have any good ideas why this is going slow one way and not the other? Thanks

推荐答案

我会怀疑参数嗅探。

由于不同的使用你的应用程序的连接可能不会在你SSMS连接可用缓存的执行计划设置选项，因此它会产生一个新的不同的计划。

The cached execution plan used for your application's connection probably won't be usable by your SSMS connection due to different set options so it will generate a new different plan.

您可以通过下面的查询检索存储过程的缓存计划。然后比较，看看它们是不同的（例如，是缓慢的在做索引搜索和书签查找在那里的其他人做一个扫描的地方吗？）

You can retrieve the cached plans for the stored procedure by using the query below. Then compare to see if they are different (e.g. is the slow one doing index seeks and bookmark lookups at a place where the other one does a scan?)

Use YourDatabase;

SELECT *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where sys.dm_exec_sql_text.OBJECTID=object_id('YourProcName') 
         and attribute='set_options'

这篇关于为什么一个数据库查询只能走在应用程序慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！