从 DotNet 执行存储过程需要很长时间，但在 SSMS 中它是立即的

Question

我在 SQL Server 2000 上有一个存储过程，它有 3 个参数。当我使用 SqlCommand.ExecuteReader () 从 DotNet 调用存储过程时，大约需要 28 秒。

当我直接在 SSMS 中运行相同的查询时，它会立即返回。

当我从存储过程中取出查询并直接使用 DotNet 运行它时，它也会立即返回。

这些是 SQL Profiler 会话的结果

SP Inside Dot Net

• 持续时间：28030
• 读取：2663365
• 写入：0

SP Inside SSMS

• 持续时间：450
• 读取：23535
• 写入：65

直接在 Dot Net 内查询

• 持续时间：360
• 读取：24865
• 写入：57

以下事情对我来说很突出：

• SSMS 的统计数据和 Dot Net 中的直接查询非常相似
• Dot Net SP 做了很多工作读取但没有写入
• 另外两个读取很少，但写入几次

任何帮助将不胜感激。

这是 SP 的一个稍微模糊的版本：

我怀疑这是一个查询计划问题，因为即使我从 DotNet 重复运行它，我总是得到相同的结果。

这是由于 IP 问题而稍作修改的 SP 版本。我希望它仍然有意义：

SELECT 
t1.pkiOrderID,
t1.fkiBasketId,
t1.sOriginBasketCode,
t1.dtDateCreated,
t1.sOrderCode,
t1.fkiUserCde,
t1.fkiOrgCde,
t1.sApprovalPerson,
t1.dtDateApproved,
t1.sRequestNo,
t1.dtRequiredDate,
t1.Requestor,
t1.OnBehalfOf,
t1.OrderDesc,
t1.OrderTypeId,
t1.fkiAgentID,
t1.fkiAgentRegionID,
stat.iStatus,
count(oi.pkiOrderItemId) as OrderItems,
count(wf.fkiOrderId) as WorkflowCount,
t1.Currency_Id,
t1.ExchangeRate,
t1.ref_odr_idn,
t2.sOrderCode as ref_odr_cde,
t1.ref_rfq_nbr,
t1.ref_rfs_nbr,
t1.ref_doc_nbr,
t1.ref_rsn,
t1.ref_forip_cde,
t1.ref_fa_nbr,
t1.odr_sub_typ
FROM    tbl1 t1 INNER JOIN 
tbl1Status stat ON
t1.pkiOrderID = stat.fkiOrderID AND
stat.dtDateStatusChanged = (SELECT MAX(stat2.dtDateStatusChanged) 
FROM tbl1Status stat2
WHERE stat2.fkiOrderId = t1.pkiOrderID) LEFT OUTER JOIN 
tbl1Item oi ON
t1.pkiOrderID = oi.fkiOrderId LEFT OUTER JOIN
tbl1Workflows wf ON
t1.pkiOrderID = wf.fkiOrderId LEFT OUTER JOIN 
tbl1 t2 ON 
t1.ref_odr_idn = t2.pkiOrderID
WHERE (t1.fkiUserCde = 'x'
or t1.fkiUserCde in (select fkiUserCde from tbl1 where fkiOrgCde in 
(select sys_org_cde from tbl3 t3 where t3.sys_lnk_org_cde = '123')))
AND ((t1.fkiOrgCde = '123'
and ('123' not in (select sys_org_cde from tbl3 t3) 
or (t1.OrderTypeID <     1 or stat.iStatus IN (2,3,4,5,6,7))))
OR (t1.fkiOrgCde in (select sys_org_cde from tbl3 t3 where     t3.sys_lnk_org_cde = '123')
and t1.OrderTypeID = 1 
and stat.iStatus NOT IN (2,3,4,5,6,7)))           
          AND   t1.OrderTypeID = 2

        GROUP BY
            t1.pkiOrderID,
            t1.fkiBasketId,
            t1.sOriginBasketCode,
            t1.dtDateCreated,
            t1.sOrderCode,
            t1.fkiUserCde,
            t1.fkiOrgCde,
            t1.sApprovalPerson,
            t1.dtDateApproved,
            t1.sRequestNo,
            t1.dtRequiredDate,
            t1.Requestor,
            t1.OnBehalfOf,
            t1.OrderDesc,
            t1.OrderTypeId,
            t1.fkiAgentID,
            t1.fkiAgentRegionID,
            stat.iStatus,
            t1.Currency_Id,
            t1.ExchangeRate,
            t1.ref_odr_idn,
            t2.sOrderCode,
            t1.ref_rfq_nbr,
            t1.ref_rfs_nbr,
            t1.ref_doc_nbr,
            t1.ref_rsn,
            t1.ref_forip_cde,
            t1.ref_fa_nbr,
            t1.odr_sub_typ
        ORDER BY t1.dtDateCreated DESC

对格式感到抱歉。我努力让它在论坛上可读。

Answer 1

由于我的评论似乎提供了正确的答案，因此我决定本着 stackoverflow 的精神将其转变为后代的完整答案。

您的问题似乎是由 SQL Server 的 参数 引起的嗅探。
为了防止这种情况发生，只需将传入的参数值分配给在 SP 顶部声明的其他变量即可。

查看这篇关于它的好文章

示例：

CREATE PROCEDURE dbo.MyProcedure
(
    @Param1 INT
)
AS

declare @MyParam1 INT
set @MyParam1 = @Param1

SELECT * FROM dbo.MyTable WHERE ColumnName = @MyParam1 

GO

我从 eggheadcafe.com。

编辑：根据 Johann Strydom 的评论，这是另一种选择：
优化参数驱动使用 SQL Server OPTIMIZE FOR Hint 进行查询。

Answer 2

只是重新创建了存储过程并修复了它。确实很奇怪。