在C＃和SQL Server Management Studio中的SQL查询非常不同的执行时间 [英] Very different execution times of SQL query in C# and SQL Server Management Studio

问题描述

我有一个简单的SQL查询，从C＃中运行时，每次花费超过30秒，然后倍出，而当上的SQL Server Management Studio中运行成功完成瞬间。在后一种情况下，查询执行计划揭示什么麻烦，和执行时间为s $ P $垫很好通过几个简单的操作。

我已经运行 EXEC sp_who2 ，而查询从C＃中运行，它被列为考虑的CPU时间29000毫秒，并且没有被任何东西

我不知道如何开始解决这个。有没有人有一些见解？

查询是：

  SELECT
    c.lngId，
    ...
从tblCaseÇ
    INNER JOIN tblCaseStatus S于s.lngId = c.lngId
    INNER JOIN tblCaseStatusType T ON t.lngId = s.lngId
    INNER JOIN [其他数据库] .. tblCompany厘米cm.lngId = cs.lngCompanyId
WHERE t.lngId = 25
    和c.IsDeleted = 0
    和s.lngStatus = 1
 

解决方案

首先，从C＃中运行时提取出查询的查询计划：

 选择p.query_plan，*
从sys.dm_exec_requests视图 -  [R
跨应用sys.dm_exec_query_plan（r.plan_handle）p
其中，r.session_id =＆LT; SPID C＃连接的制造＆gt;
 

然后将其与SSMS会话下执行的计划比较（只需单击显示的工具栏在实际的计划）。

和，作为一般规则，总是试图应用系统的方法，而不是猜测。 等待和队列的是一个很好的，实践证明，解决性能问题的SQL Server的方法。

I have a simple SQL query that when run from C# takes over 30 seconds then times-out every time, whereas when run on SQL Server Management Studio successfully completes instantly. In the latter case, a query execution plan reveals nothing troubling, and the execution time is spread nicely through a few simple operations.

I've run 'EXEC sp_who2' while the query is running from C#, and it is listed as taking 29,000 milliseconds of CPU time, and is not blocked by anything.

I have no idea how to begin solving this. Does anyone have some insight?

The query is:

SELECT
    c.lngId,
    ...
FROM tblCase c
    INNER JOIN tblCaseStatus s ON s.lngId = c.lngId
    INNER JOIN tblCaseStatusType t ON t.lngId = s.lngId
    INNER JOIN [Another Database]..tblCompany cm ON cm.lngId = cs.lngCompanyId
WHERE t.lngId = 25
    AND c.IsDeleted = 0
    AND s.lngStatus = 1

解决方案

To start with, extract the query plan of the query when is run from C#:

select p.query_plan, *
from sys.dm_exec_requests r
cross apply sys.dm_exec_query_plan(r.plan_handle) p
where r.session_id = <spid of C# connection>

Then compare it with the plan executed under the SSMS session (simply click the Show actual plan in toolbar).

And, as a general rule, always try to apply a methodical approach rather than guess. Wait and Queues is a very good, proven, performance troubleshooting methodology for SQL Server.

这篇关于在C＃和SQL Server Management Studio中的SQL查询非常不同的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！