I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF):
In the above part of the plan the table pb_WorkRquestLog contains 229,001 rows. However, the query plan is showing approx. 348 million rows (229,001 x 1,520 iterations):
There is no where clause so the query is using a Clustered Index Scan. I've rebuild all indexes with FULLSCAN and updated all statistics.
The code that this part of the plan is executing is:
select distinct wrs.ServiceKey , owner.DepartmentName AS GroupName , owner.UserName AS UserId , owner.WRLCreateDateTime , owner.WRLNotes , wrx.CreatedDate as WRCreateDateTime , wrx.Id , wrx.Description , cast(wrx.Notes as varchar(2500)) as Notes from prism72ext.dbo.pb_WorkRequestService wrs Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete') left join ( select wl.WorkRequestId , d.Name AS DepartmentName , u.UserName , wl.CreatedDate as WRLCreateDateTime , cast(wl.Notes as varchar(2500)) as WRLNotes from ( SELECT MAX( Id ) AS Id FROM prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0)) GROUP BY WorkRequestId ) mwl join prism72ext.dbo.pb_WorkRequestLog wl on mwl.Id = wl.Id join prism72Ext.dbo.pb_Department d ON wl.DepartmentId = d.DepartmentId left join prism72Ext.dbo.pb_User u on wl.UserId = u.UserId ) owner on wrs.WorkRequestId = owner.WorkRequestId
SSPEF is reporting the Actual Data Size is nearly 5GB, with 3 million Logical Reads! SSMS reports the table to be only 16 MB in size.
I've tuned the query down from 4m28s to 1m35s, but now I'm stuck. I'd be grateful if anyone could point me in the right direction to deal with this.
Edit: Someone suggested trying "OPTION (HASH JOIN, MERGE JOIN)". This made a dramatic difference. I/O was massively reduced and the query ran in 13 seconds.
Does anyone see any issues with this solution?