SQL Server Timesheet Query Performance -

i working on webapp timesheet administration. write query group time entry of employee week. , have use paging. query slow 10 entry in database took 2-3 second run.

the query uses 2 table. timeentry representant working day when employee start work , when done , pause take in minute(timeentry.pause).

i use table group week , sum day(monday, tuesday, etc...) timetype kind of hour.

the second table representant how many time company pay employee given week.

only pk indexed.

in second image can see result here column:

• firstdayoftheweek

• lastdayoftheweek

• sumbydayweek each

• ht give total work time of week

• hp give how many hour has been paid for

• hb give how many hour have in time bank week

• hb tot. give how many hour have in time bank till beginning

i have tips optimize query as possible!

here sql schema:

here example of result:

result http://s12.postimg.org/gbwc1wiqz/image.png

here execution plan:

• link

alter procedure [dbo].[timesheetweek_employee_search]    @ppagenumber int,    @pemployeeid int  begin      set  xact_abort  on      set  nocount  on     declare @count int     declare @pagecount int      declare @tmptimesheetweekemployee table(firstdayofweek datetime, ccqpaidhour int, ccqpaidminute int, staffpaidhour int, staffpaidminute int,                                             ccqadjustementhour int, ccqadjustementminute int, staffadjustementhour int, staffadjustementminute int)     insert @tmptimesheetweekemployee(firstdayofweek, ccqpaidhour, ccqpaidminute, staffpaidhour, staffpaidminute,     ccqadjustementhour, ccqadjustementminute, staffadjustementhour, staffadjustementminute)     select firstdayofweek, ccqpaidhour, ccqpaidminute, staffpaidhour, staffpaidminute,            ccqadjustementhour, ccqadjustementminute, staffadjustementhour, staffadjustementminute     timesheetweekemployee     employeeid = @pemployeeid      declare @tmptimeentry table(id int, timesheetid int, starttime datetime, endtime datetime, [pause] int, timetypeid int)     insert @tmptimeentry(id, timesheetid, starttime, endtime, [pause], timetypeid)     select id, timesheetid, starttime, endtime, [pause], timetypeid     timeentry     employeeid = @pemployeeid      select @count = (select count(*) (select 1 c                                             @tmptimeentry timeentry                                            group dbo.firstdayofweek(starttime), dbo.lastdayofweek(starttime)) te);     select @pagecount = (case when @count % 10 = 0 @count / 10                                when @count < 10 1                          else @count / 10 + 1 end)      select eid, convert(char(10), tsw.firstdayofweek ,126)firstdayofweek, lastdayofweek,            minuteccq, lundiccq, mardiccq, mercrediccq, jeudiccq, vendrediccq, samediccq,            sum(coalesce(totalccq,0)) totalccqbefore,            minutestaff, lundistaff, mardistaff, mercredistaff, jeudistaff, vendredistaff, samedistaff,            sum(coalesce(totalstaff,0)) totalstaffbefore,            @pemployeeid employeeid,            @pagecount 'pagecount',            coalesce(twet.totalccqpaid, 0) 'totalccqpaid',            coalesce(twet.totalstaffpaid, 0) 'totalstaffpaid',            coalesce(twe.ccqpaid, 0) ccqpaid,            coalesce(twe.staffpaid, 0) staffpaid     (select  dbo.encrypt( row_number() over(order  dbo.firstdayofweek(starttime) desc) ) 'eid',             dbo.firstdayofweek(starttime) firstdayofweek,             convert(char(10),dbo.lastdayofweek(starttime)  ,126) lastdayofweek,             sum(case when timetypeid = 3 datediff(mi,starttime,endtime) - pause else 0 end) 'minuteccq',             sum((case when datepart(dw,starttime) = 1 , timetypeid = 3 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'lundiccq',             sum((case when datepart(dw,starttime) = 2 , timetypeid = 3 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'mardiccq',             sum((case when datepart(dw,starttime) = 3 , timetypeid = 3 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'mercrediccq',             sum((case when datepart(dw,starttime) = 4 , timetypeid = 3 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'jeudiccq',             sum((case when datepart(dw,starttime) = 5 , timetypeid = 3 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'vendrediccq',             sum((case when datepart(dw,starttime) = 6 , timetypeid = 3 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'samediccq',             sum(case when timetypeid = 4 datediff(mi,starttime,endtime) - pause else 0 end) 'minutestaff',             sum((case when datepart(dw,starttime) = 1 , timetypeid = 4 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'lundistaff',             sum((case when datepart(dw,starttime) = 2 , timetypeid = 4 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'mardistaff',             sum((case when datepart(dw,starttime) = 3 , timetypeid = 4 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'mercredistaff',             sum((case when datepart(dw,starttime) = 4 , timetypeid = 4 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'jeudistaff',             sum((case when datepart(dw,starttime) = 5 , timetypeid = 4 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'vendredistaff',             sum((case when datepart(dw,starttime) = 6 , timetypeid = 4 (datediff(mi,starttime,endtime) - pause) else 0 end)) 'samedistaff'             @tmptimeentry timeentry             group dbo.firstdayofweek(starttime), dbo.lastdayofweek(starttime)) tsw     left join (  select dbo.firstdayofweek(starttime) firstdayofweek,                         sum((case when timetypeid = 3 (datediff(mi,starttime,endtime) - [pause]) else 0 end)) 'totalccq',                         sum((case when timetypeid = 4 (datediff(mi,starttime,endtime) - [pause]) else 0 end)) 'totalstaff'                  @tmptimeentry timeentry                  group dbo.firstdayofweek(starttime), dbo.lastdayofweek(starttime)) tt      on tt.firstdayofweek < tsw.firstdayofweek     left join (select e.firstdayofweek,                      sum(coalesce(twe.ccqpaidhour,0 )* 60 + coalesce(twe.ccqpaidminute,0 )-(coalesce(twe.ccqadjustementhour,0 )* 60 + coalesce(twe.ccqadjustementminute,0 ))) 'totalccqpaid',                             sum(coalesce(twe.staffpaidhour,0 ) * 60 + coalesce(twe.staffpaidminute,0 )-(coalesce(twe.staffadjustementhour,0 ) * 60 + coalesce(twe.staffadjustementminute,0 ))) 'totalstaffpaid'               (select dbo.firstdayofweek(starttime) firstdayofweek,                            employeeid                     timeentry                      employeeid = @pemployeeid                     group dbo.firstdayofweek(starttime), employeeid) e               left join @tmptimesheetweekemployee twe on twe.firstdayofweek < e.firstdayofweek               group e.firstdayofweek) twet     on  twet.firstdayofweek = tsw.firstdayofweek      left join (select   firstdayofweek,                         coalesce(ccqpaidhour,0 )* 60 + coalesce(ccqpaidminute,0 ) 'ccqpaid',                         coalesce(staffpaidhour,0 ) * 60 + coalesce(staffpaidminute,0 ) 'staffpaid'                @tmptimesheetweekemployee) twe     on dbo.firstdayofweek(twe.firstdayofweek) = tsw.firstdayofweek     group tsw.firstdayofweek, lastdayofweek, eid,              minuteccq, lundiccq, mardiccq, mercrediccq, jeudiccq, vendrediccq, samediccq,              minutestaff, lundistaff, mardistaff, mercredistaff, jeudistaff, vendredistaff, samedistaff, minutestaff,              ccqpaid, staffpaid, totalccqpaid, totalstaffpaid     order tsw.firstdayofweek desc     offset (case when @ppagenumber > @pagecount 1              else (@ppagenumber -1 ) * 10 end) rows     fetch next 10 rows end 

could idea calculate of stuff in insert? explain instead of doing sum(coalesce(twe.ccqpaidhour,0 )* 60 + coalesce(twe.ccqpaidminute,0 )-(coalesce(twe.ccqadjustementhour,0 )* 60 + coalesce(twe.ccqadjustementminute,0 ))). wipe field , calculate in insert?