Ok, here we go:
NO SP - it is a very simple query -
select sed.RM,
SED.DM 'Supervisor',
SED.[week] 'LCL_Weekno',
SED.projectNumber,
SED.ProjectName 'ProjName',
SES.Total_Shifts,
SED.Staff,
SED.shifts 'Shift',
SED.daysdelay,
SED.locationnumber,
SED.earliestStartDate,
case when DATEPART(DW, SED.earlieststartdate) = 1 then 'Sunday' else
case when DATEPART(DW, SED.earlieststartdate) = 2 then 'Monday' else
case when DATEPART(DW, SED.earlieststartdate) = 3 then 'Tuesday' else
case when DATEPART(DW, SED.earlieststartdate) = 4 then 'Wednesday' else
case when DATEPART(DW, SED.earlieststartdate) = 5 then 'Thursday' else
case when DATEPART(DW, SED.earlieststartdate) = 6 then 'Friday' else
case when DATEPART(DW, SED.earlieststartdate) = 7 then 'Saturday' end end end end end end
end as 'Weekday',
SED.connections 'Your_Connections',
SES.Project_Connections,
SED.SamplesTarget,
SED.samples 'Your_Samples',
SES.AveProjSamples,
SED.Sales 'YourSales',
ses.ProjSale$,
SED.Units 'YourUnits',
SES.ProjUnits,
SES.ProjUnits/SES.Total_Shifts as 'AverageProjectSales',
case when SED.samples = 0 then 0 else SED.Units/SED.samples end as 'Conversion',
case when SED.samples = 0 then 0 else ses.projUnits/(SES.AveProjSamples*SES.Total_Shifts) end as 'ProjectConversion',
case when SED.daysdelay > 0 then 0 else 10 end as 'DaysDelayScore',
case when SED.samples >= SED.SamplesTarget then 30 else
(sed.samples/sed.samplestarget) * 30 end as 'SamplesScore',
case when sed.samples = 0 then 0 else
case when (SED.Units/SED.samples) >= .25 then 30 else
((SED.Units/SED.samples)/.25)* 30 end end as 'ConversionScore',
case when SED.Units >= (SES.ProjUnits/SES.Total_Shifts)*2 then 30 else
SED.Units/((SES.ProjUnits/SES.Total_Shifts)*2) *30 end as 'SalesScore'
from dbo.SalesExecDetail SED inner join
(select projectnumber,
SUM(shifts) 'Total_Shifts',
SUM(connections) 'Project_Connections',
Sum(Samples)/SUM(Shifts) as 'AveProjSamples',
SUM(sales) 'ProjSale$',
SUM(units)'ProjUnits'
from dbo.SalesExecDetail
group by ProjectNumber) as SES on sed.projectnumber = ses.projectnumber
where sed.dm = @Supervisor
and sed.[week] between @StartDate and @EndDate
and SED.earliestStartDate >= '2013-01-01'
The fix I tried was in include separate parameters and link them to the report parameters - made no difference (since it is not an SP). This query runs in < 1 sec from SQL analyser, I have incl;uded the execution plan. Not sure how to get it out of SSRS....
Sean Lange (10/4/2013)
david.kotchie (10/4/2013)
Thanks for this, but I don't use an SP - it is a simple select statement.I am a little confused here. You say you don't have this in a stored proc but in your original post you stated:
I saw some fixes in the 'net (parameter sniffing) and made the suggested changes, but still no love.
Parameter sniffing is all about stored procs so either you are calling one or you aren't. Also if you made the suggested changes you should tell us what those are. I have seen lots of places online that suggest "fixes" for parameter sniffing that are actually worse.
A query that runs fast in SSMS but slow in the application is the classic sign of parameter sniffing. Can you post your query? It would also be helpful if you can post the actual execution plan both from SSMS and when a report is run.