Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel