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

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

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

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

这些是SQL事件探查器会话的结果

SP内部网点

SP内部SSMS

直接在Dot Net内部查询

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

任何帮助，将不胜感激。

以下是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 

抱歉格式化。 我在论坛上努力让它变得可读。

 CREATE PROCEDURE dbo.MyProcedure ( @Param1 INT ) AS declare @MyParam1 INT set @MyParam1 = @Param1 SELECT * FROM dbo.MyTable WHERE ColumnName = @MyParam1 GO