Databases
StackOverflow2013
Postsdbo
POstored procedure very slow, versus fast view with the same construction
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POstored procedure very slow, versus fast view with the same construction
    primarykey
    data
    text
    <p>I have a stored procedure that was running very slowly for specific parameters.</p> <p>The stored procedure returned a dataset.</p> <p>I converted the stored procedure to a view. I declared the same parameters and values and it returns the dataset in a couple seconds.</p> <p>The stored procedure is taking five minutes.</p> <p>I narrowed the problem down to a particular subquery in the stored procedure. Removing the subquery enabled the stored procedure to return data immediately. I converted the subquery to a temp table and joined the temp table rather than the subquery, and now the stored procedure returns data in a couple seconds.</p> <p>The operations were identical in the initial view vs stored procedure test. Is there something different about how a view is executed? </p> <p><strong>Here is the stored procedure using the new temp table. The original subquery is commented out:</strong></p> <pre><code> (@JCCo bCompany, @BegContract bContract ='', @EndContract bContract ='zzzzzzzzzz', @ThruMth bDate, @PM int = 0, @LabCTs varchar(10)=null, @MatCTs varchar(10)=null, @BegDept varchar(10) ='', @EndDept varchar(10) ='zzzzzzzzzz') With Recompile as --move the dates into a temp table Select JCJM.JCCo, JCJM.Contract, JCCD.Job, LastLaborDate=max(Case when JCTransType='PR' and (CostType between 2 and 3) then ActualDate else null end), LastProjCostDate=max(Case when JCTransType='PF' then ActualDate else null end) INTO #DATES From JCCD JCCD with(NoLock) Join JCJM JCJM with(nolock) on JCJM.JCCo=JCCD.JCCo and JCJM.Job=JCCD.Job where (JCTransType='PR' or JCTransType='PF') and JCCD.JCCo=@JCCo and JCCD.Mth&lt;=@ThruMth and JCJM.Contract Between @BegContract and @EndContract group by JCJM.JCCo,JCJM.Contract,JCCD.Job -- ) as Dates on j.JCCo=Dates.JCCo and j.Contract=Dates.Contract and j.Job=Dates.Job Select JCCD.JCCo, JCCD.Job, JobDesc=j.Description, b.Contract, ContrDesc=b.Description, -- MAX(Dates.LastLaborDate) as maxLastLaborDate, MAX(Dates.LastProjCostDate) as maxLastProjCostDate, TD.LastLaborDate, TD.LastProjCostDate, -- '01/01/2013' AS LastLaborDate, '01/01/2015' AS LastProjCostDate, JCCD.Mth, JCCD.Phase, PhsDesc=JCJP.Description, JCCD.CostType, CTDesc=JCCT.Description, JCCT.JBCostTypeCategory, FieldPctCompl=isnull(u.PctCompl,0), FieldDate=Max(u.Date), PMName=p.Name, EstHours=sum(EstHours), EstCost=sum(EstCost), JTDHrs=sum(ActualHours), JTDCost=sum(ActualCost), ProjHours=sum(JCCD.ProjHours), ProjCost=sum(JCCD.ProjCost), MTDHrs=sum(case when JCCD.Mth=@ThruMth then ActualHours else 0 end), MTDCost=sum(case when JCCD.Mth=@ThruMth then ActualCost else 0 end), FActCost=SUM(Case when JCCO.ProjMethod=2 then JCCD.ActualCost+JCCD.RemainCmtdCost else JCCD.ActualCost end), b.Department, DeptDesc=d.Description, HQName=HQCO.Name,ContrStat=b.ContractStatus,UIAmt=0,UITaxAmt=0,UIMiscAmt=0,ToBeInvd=0,sumTotalCmtdCost=0 from JCCD JCCD with(NoLock) Left Outer Join JCJM j with(NoLock) on JCCD.JCCo=j.JCCo and JCCD.Job=j.Job Left Outer Join JCCM b with(NoLock) on j.JCCo=b.JCCo and j.Contract=b.Contract Left Outer Join JCDM d with(noLock) on b.JCCo=d.JCCo and b.Department=d.Department Left Outer Join JCMP p with(NoLock) on j.JCCo=p.JCCo and j.ProjectMgr=p.ProjectMgr Left Outer Join JCJP JCJP with(NoLock) on JCCD.JCCo=JCJP.JCCo and JCCD.Job=JCJP.Job and JCCD.Phase=JCJP.Phase Left Outer Join JCCT JCCT with(NoLock) on JCCD.PhaseGroup=JCCT.PhaseGroup and JCCD.CostType=JCCT.CostType Left Outer Join (Select u.Co, Job=u.Contract/*Prints Job# 9/13 per Tina - - VU9148*/, JCJM.Contract, u.Phase, u.CostType, a.Mth, Date=Max(isnull(Date,'1950-01-01')),PctCompl=isnull(PctCompl,0) from udVPPctComplete u Join JCJM on u.Co=JCJM.JCCo and u.Contract=JCJM.Job Join (select Co, Job=a.Contract,Contract=JCJM.Contract, Phase, CostType, Mth=Max(isnull(Mth,'1950-01-01')) from udVPPctComplete a Join JCJM on a.Co=JCJM.JCCo and a.Contract=JCJM.Job where Mth&lt;=@ThruMth and Co=@JCCo and JCJM.Contract between @BegContract and @EndContract group by Co, a.Contract,JCJM.Contract, Phase, CostType )a on a.Co=u.Co and a.Job=u.Contract and a.Phase=u.Phase and a.CostType=u.CostType and a.Mth=u.Mth and a.Contract=JCJM.Contract group by u.Co, u.Contract,JCJM.Contract, u.Phase, u.CostType, a.Mth, isnull(PctCompl,0) ) u on JCCD.JCCo=u.Co and b.Contract=u.Contract and JCCD.Phase=u.Phase and JCCD.CostType=u.CostType and u.Job=JCCD.Job LEFT OUTER JOIN #DATES TD ON j.JCCo=TD.JCCo and j.Contract=TD.Contract and j.Job=TD.Job --Left Outer Join (Select JCJM.JCCo, JCJM.Contract, JCCD.Job, -- LastLaborDate=max(Case when JCTransType='PR' and (CostType between 2 and 3) then ActualDate else null end), -- LastProjCostDate=max(Case when JCTransType='PF' then ActualDate else null end) -- From JCCD JCCD with(NoLock) -- Join JCJM JCJM with(nolock) on JCJM.JCCo=JCCD.JCCo and JCJM.Job=JCCD.Job -- where (JCTransType='PR' or JCTransType='PF') and JCCD.JCCo=@JCCo and JCCD.Mth&lt;=@ThruMth -- and JCJM.Contract Between @BegContract and @EndContract -- group by JCJM.JCCo,JCJM.Contract,JCCD.Job -- ) as Dates on j.JCCo=Dates.JCCo and j.Contract=Dates.Contract and j.Job=Dates.Job Join JCCO JCCO with(NoLock) on JCCD.JCCo=JCCO.JCCo Join HQCO HQCO with(NoLock) on JCCD.JCCo=HQCO.HQCo where JCCD.JCCo=@JCCo and b.Contract/*=' 51431.'--*/ between @BegContract and @EndContract and JCCD.Mth&lt;=@ThruMth and b.Department&gt;=@BegDept and b.Department&lt;=@EndDept and j.ProjectMgr=(case when @PM&lt;&gt;0 then @PM else j.ProjectMgr end) and (charindex(','+left(cast(JCCD.CostType as char(5))+' ', len(convert(varchar, JCCD.CostType)))+',', ','+@LabCTs+',')&lt;&gt;0 or charindex(','+left(cast(JCCD.CostType as char(5))+' ', len(convert(varchar, JCCD.CostType)))+',', ','+@MatCTs+',')&lt;&gt;0 ) Group by JCCD.JCCo, JCCD.Job,j.Description, b.Contract, b.Description, JCCD.Mth, JCCD.Phase, JCJP.Description, JCCD.CostType, u.PctCompl,JCCT.Description, p.Name,JCCT.JBCostTypeCategory, TD.LastLaborDate, TD.LastProjCostDate, --Dates.LastLaborDate, Dates.LastProjCostDate, b.Department, d.Description,HQCO.Name,b.ContractStatus </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USunholymackerel
    1. USunholymackerel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POstored procedure very slow, versus fast view with the same construction
      1. This table or related slice is empty.
      1. VTDownMod
    1. COPlease show us both codes.
      singulars
      1. POstored procedure very slow, versus fast view with the same construction
      1. USLuigiEdlCarno
    2. COObviously the view and the stored procedure are not acting the same way, although they may produce the same result. Subselects are ... difficult: they may be executed for each resulted row. And please provide SQL statement and procedure code.
      singulars
      1. POstored procedure very slow, versus fast view with the same construction
      1. USnabuchodonossor
    3. COMy guess is parameter sniffing. SQL Server determines the execution plan for a procedure the first time it is run, based on the params used. That plan is then cached and reused. With a view, the plan is generated for the ``select`` statement you run that references the view. The two could be completely different.
      singulars
      1. POstored procedure very slow, versus fast view with the same construction
      1. USacfrancis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload