July 9, 2008 at 3:13 pm
Should calculated fields be done in SQL or in SRS for performance? I have a report that has all calculations done in SQL and takes 10 minutes to run. Will it run faster if the calcs are done via SRS?
July 10, 2008 at 1:41 am
It really depends on how you are doing your calculations:
Are you using any cursors to do this?
Are you using formulas in your WHERE clause?
Generally speaking I haven't found a difference between calculations in SSRS or SQL but that doesn't mean the difference doesn't exist. I have found though, that when reports take that long to run, it is generally the SQL side of things that is causing the problem.
Have you tried taking your query out of SSRS and simply running it in SQL Server Management Studio to see what the performance is like.
Regards,
Nigel West
UK
July 10, 2008 at 8:20 am
No cursor.
No calcs in WHERE clause.
My calcs are all done in a subreport. The main report uses a LIST and passes employee id among other parameters to the subreport. In the subreport, all calcs are done using SET @variable = (do calc). In the end, all the @variables are returned in a SELECT statement.
I test all my scripts in SQL Management Studio first before moving it into SRS and it takes about 5 seconds per employee. However when running in SRS, it actually took 16 min to process 162 employees.
July 10, 2008 at 10:17 am
The timing is not bad when a single SELECT for a single employee takes 5 seconds!
5 * 162 = 810 seconds in total, this is 13.5 minutes and then you have to add on a little bit for the report rendering (the report renderng often is the longest part, in this case it isn't).
I would suggest that your problem is within the SQL, i.e. the SET statements.
If I was doing a fairly simple select for a single employee I would be looking to do this in well under 1 second, so you really need to look at that part.
Any chance you can post a sample of the SQL you are using??
Regards,
Nigel West
UK
July 10, 2008 at 10:21 am
declare @pay_begin_dt datetime, @pay_end_dt datetime,@empid char(7),@piecework_pay decimal(8,2),@base_wage decimal(4,2),@hours_worked decimal(8,2),@hours_ot_worked decimal(8,2)
,@hours_dbl_worked decimal(8,2),@reg_hourly_rt decimal(8,2),@ot_prem_rt decimal(8,2),@ot_pay decimal(8,2),@dbl_time_pay decimal(8,2),@gross_pay decimal(8,2),@ot_prem_rt_calc decimal(8,2)
,@ot_prem_rt_calc_label char(30),@dbl_time_hrly_rt decimal(8,2)
set @empid ='3021184'
set @base_wage = 8
set @pay_end_dt = '7/4/08'
set @pay_begin_dt = '6/28/08'
set @piecework_pay = (select sum(unit * rate)
from pcs_tpr
where empid = @empid)
set @hours_worked = (SELECT SUM(cast(WFCTIMEINSECONDS AS decimal(8,2)))/3600
FROM VP_ALLTOTALS
WHERE APPLYDATE BETWEEN @pay_begin_dt AND @pay_end_dt
AND WFCLABORLEVELNAME1 = 'mps'
AND PAYCODEID in ('101','102','301') -- 101 = reg hrs, 102 = ot hrs, 301 = dbl hrs
AND PAYCODETYPE = 'P'
and personnum = @empid)
set @reg_hourly_rt = @piecework_pay / @hours_worked
set @hours_ot_worked = case when (SELECT SUM(cast(WFCTIMEINSECONDS AS decimal(8,2)))/3600
FROM VP_ALLTOTALS
WHERE APPLYDATE BETWEEN @pay_begin_dt AND @pay_end_dt
AND WFCLABORLEVELNAME1 = 'mps'
AND PAYCODEID = '102'
AND PAYCODETYPE = 'P'
and personnum = @empid) is null then 0 else
(SELECT SUM(cast(WFCTIMEINSECONDS AS decimal(8,2)))/3600
FROM VP_ALLTOTALS
WHERE APPLYDATE BETWEEN @pay_begin_dt AND @pay_end_dt
AND WFCLABORLEVELNAME1 = 'mps'
AND PAYCODEID = '102'
AND PAYCODETYPE = 'P'
and personnum = @empid) end
set @hours_dbl_worked = case when (SELECT SUM(cast(WFCTIMEINSECONDS AS decimal(8,2)))/3600
FROM VP_ALLTOTALS
WHERE APPLYDATE BETWEEN @pay_begin_dt AND @pay_end_dt
AND WFCLABORLEVELNAME1 = 'mps'
AND PAYCODEID = '301'
AND PAYCODETYPE = 'P'
and personnum = @empid) is null then 0 else
(SELECT SUM(cast(WFCTIMEINSECONDS AS decimal(8,2)))/3600
FROM VP_ALLTOTALS
WHERE APPLYDATE BETWEEN @pay_begin_dt AND @pay_end_dt
AND WFCLABORLEVELNAME1 = 'mps'
AND PAYCODEID = '301'
AND PAYCODETYPE = 'P'
and personnum = @empid) end
if @reg_hourly_rt >= @base_wage
begin
set @ot_prem_rt = isnull(@reg_hourly_rt * .5,0)
set @dbl_time_pay = isnull(@reg_hourly_rt * @hours_dbl_worked,0)
set @ot_prem_rt_calc = isnull(@reg_hourly_rt,0)
set @ot_prem_rt_calc_label = 'Regular Hourly Rate'
set @dbl_time_hrly_rt = isnull(@reg_hourly_rt,0)
end
else
begin
set @ot_prem_rt = isnull(@base_wage * .5,0)
set @dbl_time_pay = isnull(@base_wage * @hours_dbl_worked,0)
set @ot_prem_rt_calc = isnull(@base_wage,0)
set @ot_prem_rt_calc_label = 'Employee Base Wage'
set @dbl_time_hrly_rt = isnull(@base_wage,0)
end
set @ot_pay = isnull(@ot_prem_rt * @hours_ot_worked,0)
set @gross_pay = isnull(@piecework_pay + @ot_pay + @dbl_time_pay,0)
select @piecework_pay as piecework_pay
,@hours_worked as hours_worked
,@reg_hourly_rt as reg_hourly_rt
,@base_wage as base_wage
,@ot_prem_rt_calc_label as ot_prem_rt_calc_label
,@ot_prem_rt_calc as ot_prem_rt_calc
,@ot_prem_rt as ot_prem_rt
,@hours_ot_worked as hours_ot_worked
,@ot_pay as ot_pay
,@dbl_time_hrly_rt as dbl_time_hrly_rt
,@hours_dbl_worked as hours_dbl_worked
,@dbl_time_pay as dbl_time_pay
,@gross_pay as gross_pay
July 10, 2008 at 10:29 am
OK, first look tells me that your problem is definately in the SQL Code,, however, you'll need to give me a couple of hours to fully digest and re-work it.
I'll get back to you later.
Nigel West
UK
July 11, 2008 at 2:05 am
OK, there are a couple of simple things you could do to speed it up, but I'm not sure it's worthwhile doing the simple things, I would make wholesale changes to this.
First, declare a temporary table to hold the data.
DECLARE @TempData TABLE
(
piecework_pay decimal(8,2)
,hours_worked decimal(8,2)
,re_hourly_rt decimal(8,2)
,base_wage decimal(8,2)
,ot_prem_rt_calc_label nvarchar(30)
,ot_prem_rt_calc decimal(8,2)
,ot_prem_rt decimal(8,2)
,hours_ot_worked decimal(8,2)
,ot_pay decimal(8,2)
,dbl_time decimal(8,2)
,hours_dbl_worked decimal(8,2)
,dbl_time_pay decimal(8,2)
,gross_pay decimal(8,2)
)
Then you need to declare your temp variables
declare @pay_begin_dt datetime
declare @pay_end_dt datetime
declare @base_wage decimal(4,2)
Then set your temp variables
set @pay_begin_dt = '7/4/08'
set @pay_end_dt = '6/28/08'
set @base_wage = 8
Notice that I am not using the @emp_id variable, I am suggesting that you change the way you work this report. Currently you are launching the sub-reports from the main report and passing the emp_id as a parameter, this is then used to refresh a dataset inside the sub-report.
I suggest you create a complete dataset in the main report and pass all of the data through to the subreport as parameters. This means that the data will all be returned in one dataset and will be much faster. If you don't want to do this then all of this code can still be used, you just need to add in the code for emp_id.
So, the next thing is to add the data to the temp table:
INSERT INTO @TempData
( piecework_pay, hours_worked, hours_ot_worked, hours_dbl_worked )
SELECT (SELECT SUM(unit * rate) FROM pcs_tpr WHERE empid = vpa.personnum)
, sum(wfctimeinseconds)
, isnull(sum(case when paycode='102' then wfctimeinseconds/3600 else 0 end)),0)
, isnull(sum(case when paycode='301' then wfctimeinseconds/3600 else 0 end)),0)
FROM VP_ALLTOTALS vpa
WHERE applydate between @pay_begin_dt AND @pay_end_dt
and wfclaborlevelname = 'mps'
and paycodetype = 'P'
and paycodeid in ('101','102','301')
This will create the temp table, but only with the sql data, so now you should create the calculated data.
update @tempdata
set @reg_hourly_rt = piecework_pay / hours_worked
And then the final calcs
update @tempdata
set ot_prem_rt = case when reg_hourly_rt >= @base_wage then isnull(reg_hourly_rt * 0.5,0) else isnull(@base_wage * 0.5,0) end
set dbl_time_pay = case when reg_hourly_rt >= @base_wage then isnull(reg_hourly_rt * hours_dbl_worked,0) else isnull(@base_wage * hours_dbl_worked,0) end
set ot_prem_rt_calc = case when reg_hourly_rt >= @base_wage then isnull(reg_hourly_rt,0) else isnull(@base_wage,0) end
set ot_prem_rt_calc_label = case when reg_hourly_rt > @base_wage then 'Regular Hourly Rate' else 'Employee Base Wage' end
set dbl_time_hrly_rt = isnull(reg_hourly_rt,0) else isnull(@base_wage,0) end
And finally, select all of your data to return to the report..
select * from @tempdata
Note that without your database I am creating code here that I "Believe" is right, but it will no doubt need some debugging.
Let me know how you get on.
Regards,
Nigel West
UK
July 14, 2008 at 10:00 am
Thanks Nigel! I will try this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply