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.
When I run the same query inside SSMS directly it returns immediately.
When I take the query out of the stored proc and run it directly using DotNet it also returns immediately.
These are the results from a SQL Profiler session
SP Inside Dot Net
- Duration: 28030
- Reads: 2663365
- Writes: 0
SP Inside SSMS
- Duration: 450
- Reads: 23535
- Writes: 65
Query directly inside Dot Net
- Duration: 360
- Reads: 24865
- Writes: 57
The following things stand out to me:
- The stats for the SSMS and direct query in Dot Net are very similar
- The Dot Net SP one does a huge amount of reads but no writes
- The other two make very few reads, but a couple of writes
Any help would be appreciated.
Here is a slightly obviscated version of the SP:
I doubt that it is a query plan issue because even if I run it repeatedly from DotNet, I always get the same results.
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.
See Question&Answers more detail:
os