Query Performance 1

[checkai]

Is there any huge issue with the following SQL? This should be getting timesheets for only one week and it is a DOG!!

select s.surname + ', ' + s.forename EmpName
		,b.businessunit BusinessUnit
		,r.region Region
		,case when isnull(c.firstname,'') = '' then c.searchname else c.searchname + ', ' + c.firstname end ClientName
		,f.serviceline Team
		,convert(char(13),w.date,101) Date
		,j.name JobName
		,ca.description Activity
		,w.chargetypeid
		,w.detail
		,case when w.jobischargeable = 1 then 'Chargeable' else 'Non-Chargeable' end ChargeStatus
		,case when w.chargetypeid=24 then isnull(W.Units,0) else 0 end as HoursWorked
		,case when w.chargetypeid=25 and w.[ExpenseTypeID] = 2 then isnull(w.units,0) else 0 end as Miles
		,case when w.chargetypeid=25 and w.[ExpenseTypeID] = 1 then isnull(w.rate*w.units,0) else 0 end as Meals
		,case when w.chargetypeid=25 and w.[ExpenseTypeID] NOT IN (1,2) then isnull(w.rate*w.units,0) else 0 end as OtherExp
		,case when datepart(dw,w.date) = 2 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end MonHrs
		,case when datepart(dw,w.date) = 3 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end TuesHrs
		,case when datepart(dw,w.date) = 4 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end WedHrs
		,case when datepart(dw,w.date) = 5 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end ThursHrs
		,case when datepart(dw,w.date) = 6 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end FriHrs
		,case when datepart(dw,w.date) = 7 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end SatHrs
		,case when datepart(dw,w.date) = 1 and w.chargetypeid = 24 then isnull(w.units,0) else 0 end SunHrs
	from ssrsWIP w
	  join tblstaff s on w.staffid = s.staffid
		join tblserviceline F on w.staffservicelineid = F.servicelineid
		join tblbusinessunit b on w.staffbusinessunitid = b.businessunitid
		join sgtmarket mk on F.marketid = mk.marketid
		join sgtregion r on mk.regionid = r.regionid
		join tblclient c on w.clientid = c.clientid
		join tbljob j on w.jobid = j.jobid
		join tblchargeaccs ca on w.chargeacid = ca.chargeacid
	WHERE w.staffServiceLineID in (Select teamID from @teams)
	AND		((w.chargetypeid = 24 and W.Date >= @weekbegin and W.Date <= @sunday) or (w.chargetypeid = 25 and w.tsday >= @weekBegin and w.tsday <= @sunday) and isnull(w.approved,0)<>0
	AND		(case when w.chargetypeid=24 and w.chargeacid<>131 then isnull(W.Units,0) else 0 end <> 0
	OR		 case when w.chargetypeid=25 and w.[ExpenseTypeID] = 2 then isnull(w.units,0) else 0 end <> 0
	OR		 case when w.chargetypeid=25 and w.[ExpenseTypeID] = 1 then isnull(w.rate*w.units,0) else 0 end <> 0
	OR		 case when w.chargetypeid=25 and w.[ExpenseTypeID] NOT IN (1,2) then isnull(w.rate*w.units,0) else 0 end <> 0))
order by Date, clientname, jobname, activity

 

[SQLSister]

Not surprised it's slow. You have case statments in the where clause, you have NOT IN in the where clasue, you have a subselect in the where clause instead of a join, you have used functions in the where clasue,you are ordering by four fields (are they all indexed?), you are converting dates, you have 8 joins (are all the join fields indexed?). The question isn't really if you can improve performance but where to start.

Start first by looking at the execution plan? Are there any table scans, if so can you change the indexing to get rid of them? At a minimum make sure all the join fields and fields in the where clause are indexed.

Get rid of the subselect and replace it with a join. Joins are significantly faster than subselects.

That's where I'd start. Then on to the where clause. This is tricker and will require much experimentation on your part. It could probably benefit from the use of derived tables. However, this isn;t a sure win, you will need to try differnt variations of putting some of the where criteria into the a derived table and then joining to the other table to see if it will imporve performance. A lot of performance improving is specific to the data inthe data base and how it is stored and retreived and other types of calls to the server that will happen at the same time. So there is no onesize fits all solution to performance imporvement. And what might improve perfomance now, might be slower when there are more records in the tables.

I think you can simplify the where clasue even if you don't use the derived table approach. What is the value of the rate column when the first two conditions are met? Can it be changed to one? Then all you need is the calculation instead of the various case statements.

"NOTHING is more important in a database than integrity." ESquared

 

[checkai]

Thanks, by changing the IN to a join I already have cut off a chunk of time. Also, by evaluating the case statements in the where clause further, I ridded two of those. I changed the dates to use a between statement, is that better?

 

[SQLSister]

between should be ok, but I doubt it will save much time. Getting rid of some of the case statments is good. I also think you can make it one all incluseve case statment, as they can have more thatn two branches. I'm not sure if having them separate like this with an else for each one is truly giving you the results you want. You might inadvertently be excluding records.

"NOTHING is more important in a database than integrity." ESquared

 

[sblocher]

SQLSister, not sure i agree that "Joins are significantly faster than subselects. MS suggests that SQL will optimize them in the same way, assuming ALL ELSE IS EQUAL.

For example, if i put these two queries together and run them, then Execution plans are the same:

select productcode from tblitem where itemnumber in (select itemnumber from tblharnessitem)
select productcode from tblitem inner join tblharnessitem on tblharnessitem.itemnumber = tblitem.itemnumber

Is this true?

 

[SQLSister]

but the join will usually run faster
Most performance tuning books will suggest that you avoid the in construction.

"NOTHING is more important in a database than integrity." ESquared