InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 07:19:11 GMT20http://www.sqlservercentral.com/Forums/Topic1392338-65-1.aspxIt is called SARGability. When you add that where clause it causes a full scan. It has to evaluate every single row to determine if it belongs in the result set. You could try splitting this logic into two pieces.[code]WHERE DEFAULT_STORE.[STORE ID] > ACTUAL_STORE.[STORE ID]ORDEFAULT_STORE.[STORE ID] < ACTUAL_STORE.[STORE ID][/code]Do a quick google search on SARGability and you will find lots of articles discussing what is happening with your query. Makes sense once you understand it.Tue, 04 Dec 2012 07:48:13 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1392338-65-1.aspxHi,I have the following query that I'm using to extract some demo report data.[b]SELECT HOURS.EMPID, HOURS.WORKDATE, HOURS.MINS, CODES.CORRCODEID, CODES.CORRCODE, CODES.NAME AS [CORRCODE NAME], CODES.CWTYPE, DEFAULT_STORE.[STORE NAME] AS [DEFAULT STORE NAME], DEFAULT_STORE.[STORE ID] AS [DEFAULT STORE ID], DEFAULT_STORE.[STORE SQUARE FOOTAGE] AS [DEFAULT STORE SQUARE FOOTAGE], ACTUAL_STORE.[STORE NAME] AS [ACTUAL STORE NAME], ACTUAL_STORE.[STORE ID] AS [ACTUAL STORE ID], ACTUAL_STORE.[STORE SQUARE FOOTAGE] AS [ACTUAL STORE SQUARE FOOTAGE], WORKED_DEPT_REC.PARENTID [WORKED_DEPT_PARENTID], WORKED_DEPT_REC.NAME [WORKED_DEPT_NAME], WORKED_DEPT_REC.COSTCENTRE [WORKED_DEPT_COSTCENTRE], WORKED_DEPT_REC.DEPTID [WORKED_DEPT_DEPTID], DEFAULT_DEPT_REC.PARENTID [DEFAULT_DEPT_PARENTID], DEFAULT_DEPT_REC.NAME [DEFAULTDEPT_DEPT_NAME], DEFAULT_DEPT_REC.COSTCENTRE [DEFAULT_DEPT_COSTCENTRE], DEFAULT_DEPT_REC.DEPTID [DEFAULT_DEPT_DEPTID], DEFAULT_SHIFT.SHIFTID [DEFAULT_SHIFT_SHIFTID], DEFAULT_SHIFT.TARGET [DEFAULT_SHIFT_TARGET_MINS], DEFAULT_SHIFT.NAME [DEFAULT_SHIFT_NAME], ACTUAL_SHIFT.SHIFTID [ACTUAL_SHIFT_SHIFTID], ACTUAL_SHIFT.TARGET [ACTUAL_SHIFT_TARGET_MINS], ACTUAL_SHIFT.NAME [ACTUAL_SHIFT_NAME]--INTO-- REPORT_DATAFROM CORRECTI HOURS-- GET THE CORRECTIONS CODE DETAILSINNER JOIN CORRCODE CODESON HOURS.CORRCODE = CODES.CORRCODEID-- JOIN ON THE EMPLOYEE RECORDINNER JOIN EMPLOYEE EON HOURS.EMPID = E.EMPID-- GET THE WORKED DEPT RECORD FROM COMPANY STRUCTURE TABLELEFT OUTER JOIN COMPSTRU WORKED_DEPT_RECON WORKED_DEPT_REC.DEPTID = ISNULL(HOURS.DEPTID, E.DEPTID) -- GET THE DEFAULT DEPT ID FROM THE EMPLOYEE HISTORYINNER JOIN EMP_HIST DEFAULT_DEPT ON DEFAULT_DEPT.FINDEX = '-z' AND DEFAULT_DEPT.EMPID = HOURS.EMPID AND HOURS.WORKDATE BETWEEN DEFAULT_DEPT.EFFECTIVE AND DEFAULT_DEPT.EFFECTIVEEND-- GET THE DEFAULT DEPT RECORD FROM COMPANY STRUCTURE TABLELEFT OUTER JOIN COMPSTRU DEFAULT_DEPT_RECON DEFAULT_DEPT_REC.DEPTID = ISNULL(DEFAULT_DEPT.NEWVALUE, E.DEPTID) -- GET THE ROSTER ID FROM THE EMPLOYEE HISTORYINNER JOIN EMP_HIST DEFAULT_ROSTER ON DEFAULT_ROSTER.FINDEX = '-x' AND DEFAULT_ROSTER.EMPID = HOURS.EMPID AND HOURS.WORKDATE BETWEEN DEFAULT_ROSTER.EFFECTIVE AND DEFAULT_ROSTER.EFFECTIVEEND-- GET THE DEFAULT SHIFT ID FROM ROSTDAYSLEFT OUTER JOIN ROSTDAYS RDON DEFAULT_ROSTER.NEWVALUE = ISNULL(RD.ROSTERID, E.CLK_ROSTER) AND RD.WORKDATE = HOURS.WORKDATELEFT OUTER JOIN SHIFTS DEFAULT_SHIFTON DEFAULT_SHIFT.SHIFTID = RD.SHIFTID -- GET THE ACTUAL SHIFT RECORD FROM BASIC LEFT OUTER JOIN BASIC BON B.WORKDATE = HOURS.WORKDATE AND B.EMPID = HOURS.EMPID AND B.SUBALLOC = '0'LEFT OUTER JOIN SHIFTS ACTUAL_SHIFTON ACTUAL_SHIFT.SHIFTID = ISNULL(B.SHIFTID, RD.SHIFTID)LEFT OUTER JOIN( SELECT STORE.NAME [STORE NAME], STORE.DEPTID [STORE ID], STORE.OTHER [STORE SQUARE FOOTAGE], DEPTS.DEPTID FROM COMPSTRU STORE OUTER APPLY dbo.Tesco_udf_Dept_Tree(STORE.DEPTID,1) AS DEPTS WHERE STORE.PARENTID = '!#' ) DEFAULT_STOREON DEFAULT_DEPT_REC.DEPTID = DEFAULT_STORE.DEPTID LEFT OUTER JOIN( SELECT STORE.NAME [STORE NAME], STORE.DEPTID [STORE ID], STORE.OTHER [STORE SQUARE FOOTAGE], DEPTS.DEPTID FROM COMPSTRU STORE OUTER APPLY dbo.Tesco_udf_Dept_Tree(STORE.DEPTID,1) AS DEPTS WHERE STORE.PARENTID = '!#' ) ACTUAL_STOREON WORKED_DEPT_REC.DEPTID = ACTUAL_STORE.DEPTID[/b]It runs quickly enough for my needs. It's just that when I add the following where clause:[b](WHERE DEFAULT_STORE.[STORE ID] <> ACTUAL_STORE.[STORE ID])[/b]the query ends up going from taking 18 seconds to run to failing after an hour with the tempdb expanding to 50GB and running out of disk space. Can someone enlighten me as to why the where clause has such an effect as I'm only filtering on columns already included in the query without the where clause? Also, the actual execution plan changes significantly with the addition of the WHERE.ThanksMickTue, 04 Dec 2012 03:52:05 GMTmickquinlan