我的SQL Server 2000需要3个参数的存储的过程。当我打电话从DOTNET的使用SqlCommand.ExecuteReader（存储的过程），大约需要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事件探查器会话的结果

These are the results from a SQL Profiler session

SP里面点网

SP内部SSMS

查询直接在斑点网

下面的事情站出来对我说：

The following things stand out to me:

任何帮助将是AP preciated。

Any help would be appreciated.

下面是SP的稍微obviscated版本：

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.

下面是一个版本，因为知识产权问题一个已经略有改变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.

推荐答案

由于我的评论似乎提供了正确的答案，我决定把它变成一个完整的答案留给后人在计算器的精神。

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的参数引起的嗅探。 为prevent，只是分配传入的参数值到你的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 。

编辑：按照约翰Strydom的评论，这里是另一种选择： 优化参数驱动的查询与SQL Server OPTIMIZE FOR提示。

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