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

本文介绍了从 DotNet 执行存储过程需要很长时间，但在 SSMS 中它是立即的的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I have a stored proc on SQL Server 2000 that takes 3 parameters. When I call the stored proc from DotNet using SqlCommand.ExecuteReader () it takes about 28 seconds.

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

When I run the same query inside SSMS directly it returns immediately.

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

When I take the query out of the stored proc and run it directly using DotNet it also returns immediately.

这些是 SQL Profiler 会话的结果

These are the results from a SQL Profiler session

SP Inside Dot Net

时长:28030
读取:2663365
写入:0

SSMS 中的 SP

时长:450
读取:23535
写入:65

直接在点网内部查询

时长:360 度
读取:24865
写入:57

以下几点对我来说很突出:

The following things stand out to me:

SSMS 和 Dot Net 中的直接查询的统计数据非常相似
Dot Net SP 进行大量读取但没有写入
另外两个读取次数很少，但写入次数很少

任何帮助将不胜感激.

这是 SP 的略显模糊的版本:

Here is a slightly obviscated version of the SP:

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

I doubt that it is a query plan issue because even if I run it repeatedly from DotNet, I always get the same results.

这是由于 IP 问题而略有改动的 SP 版本.我希望它仍然有意义:

Here is a version of the SP that's been altered slightly because of IP issues. I hope it still makes sense:

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

对格式问题感到抱歉.我很难让它在论坛上可读.

Sorry about the formatting. I struggled to get it readable at all on the forum.

推荐答案

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

Since my comment seemed to provide the correct answer, I decided to move it into a full answer for posterity in the spirit of stackoverflow.

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

Your problem seems to be caused by SQL Server's Parameter Sniffing. To prevent it, just assign your incoming parameter values to other variables declared right at the top of your 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.

As per Johann Strydom's comment, here is another option: Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint.

这篇关于从 DotNet 执行存储过程需要很长时间，但在 SSMS 中它是立即的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！

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

问题描述

推荐答案

相关文档推荐