Performance DIES when REMOVING Where Clause

Question

Performance DIES when REMOVING Where Clause

cpapas

Valued Member

Points: 66
More actions
June 14, 2013 at 9:29 am

#294763

I've never seen anything like it. I have a simple common table expression that returns 7 rows of data.
When you run Select * From cte, it takes 25 seconds to return the data.
When you run Select * From cte WHERE X=1, it takes 1 second
When you add anything BUT "=" , Select * from cte WHERE X > 0, it takes 25 seonds.
I've got an index on that field. The only thing I can add is the field is type Numeric (4,1)
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post query, table definitions, index definitions and execution plans.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cpapas Valued Member Points: 66 More actions · Answer 2

Here's the query. It's the very end that causes the problem

DECLARE @sdate AS DATE

DECLARE @progfk AS INT

SET @progfk=1

SET @sdate = '01/01/2009'

SET NOCOUNT ON;

;WITH cteMain AS (

SELECT g.WorkerPK, g.WrkrLName

, g.HireDate

, RTRIM(w.FirstName) + ' ' + RTRIM(w.LastName) AS WorkerName

, wp.Supervisor

, wp.FSW

, wp.FAW

, wp.ProgramManager

, wp.FatherAdvocate

, ROW_NUMBER() OVER(ORDER BY g.workerpk DESC) AS 'RowNumber'

, COUNT(*) OVER() AS TotalWorkers

FROM dbo.fnGetWorkerEventDatesALL(@progfk, NULL, NULL) g

INNER JOIN Worker w ON w.WorkerPK = g.WorkerPK

INNER JOIN WorkerProgram wp ON g.WorkerPK = wp.WorkerFK

WHERE (DATEDIFF(d, g.HireDate, GETDATE()) > 181

AND g.HireDate > @sdate

AND wp.TerminationDate IS NULL)

GROUP BY g.WorkerPK, g.WrkrLName

, g.HireDate

, w.FirstName

, w.LastName

, wp.Supervisor

, wp.FSW

, wp.FAW

, wp.ProgramManager

, wp.FatherAdvocate

)

, cteAllRequiredTrainings AS (

SELECT topiccode, topicname, subtopiccode FROM dbo.codetopic t1

INNER JOIN SubTopic ON subtopic.TopicFK = t1.codetopicPK

WHERE TopicCode BETWEEN 14.0 AND 19.0 AND programfk IS NULL AND RequiredBy='HFA'

)

, cteMainMerge AS (

SELECT * FROM cteMain, cteAllRequiredTrainings

)

--Now we get the trainings (or lack thereof) for topic code 14.0

, cteTrainings AS (

SELECT

ta.workerfk

, t1.TopicCode

, t1.topicname

, CASE IsExempt

WHEN 1 THEN @sdate

ELSE MIN(t.TrainingDate)

END AS TrainingDate

, Subtopic.SubTopicCode AS subtopiccode2

FROM cteMainMerge

LEFT JOIN TrainingAttendee ta ON ta.WorkerFK = cteMainMerge.WorkerPK

LEFT JOIN Training t ON t.TrainingPK = ta.TrainingFK

LEFT JOIN TrainingDetail td ON td.TrainingFK = t.TrainingPK

LEFT JOIN codeTopic t1 ON td.TopicFK=t1.codeTopicPK

INNER JOIN SubTopic ON subtopic.TopicFK = td.TopicFK AND subtopic.SubTopicPK = td.SubTopicFK

WHERE t1.TopicCode BETWEEN 14.0 AND 19.0

GROUP BY

WorkerFK

, HireDate

, t1.TopicCode

, t1.topicname

, WorkerName

, Supervisor

, FSW

, FAW

, ProgramManager

, FatherAdvocate

, rownumber

, TotalWorkers

, IsExempt

, SubTopic.subtopiccode

)

, cteTrainingsUnique AS (

--this eliminates the problem that a worker could have both a regular training and an exempt training

SELECT workerfk

, topiccode

,topicname

, MIN(trainingdate) AS TrainingDate

, subtopiccode2

FROM cteTrainings

GROUP BY workerfk

, topiccode

, topicname

, subtopiccode2

)

, cte10_4Final AS (

--if a worker has NO trainings, they won't appear at all, so add them back

select DISTINCT WorkerPK

, RowNumber

, TotalWorkers

, HireDate

, WorkerName

, Supervisor

, FSW

, FAW

, ProgramManager

, FatherAdvocate

, cteMainMerge.TopicCode

, cteMainMerge.topicname

, SubTopicCode

, TrainingDate FROM cteMainMerge

LEFT JOIN cteTrainingsUnique ON cteTrainingsUnique.workerfk=cteMainMerge.workerpk AND subtopiccode2=cteMainMerge.subtopiccode AND cteMainMerge.TopicCode=cteTrainingsUnique.TopicCode

)

, cteMeetTarget AS (

SELECT TotalWorkers

, cte10_4Final.WorkerPK

, WorkerName

, Supervisor

, FSW

, FAW

, ProgramManager

, FatherAdvocate

, TopicCode

, TopicName

, TrainingDate

, HireDate

, SubTopicCode

, CASE WHEN TrainingDate <= dateadd(day, 183, HireDate) THEN 'T' ELSE 'F' END AS 'Meets Target'

, CASE WHEN TrainingDate IS NOT NULL THEN 1 ELSE 0 END AS 'ContentCompleted'

FROM cte10_4Final

GROUP BY cte10_4Final.WorkerPK

, WorkerName

, Supervisor

, FSW

, FAW

, ProgramManager

, FatherAdvocate

, TopicCode

, TopicName

, TrainingDate

, HireDate

, rownumber

, TotalWorkers

, SubTopicCode

)

--Now calculate the number meeting count

, cteCountMeetingOne AS (

SELECT DISTINCT TopicCode

, workerpk

, COUNT(*) AS totalmeetingcount

FROM cteMeetTarget

WHERE [Meets Target]='T'

GROUP BY TopicCode

, workerpk

)

--Now calculate the number meeting count

, cteCountMeeting AS (

SELECT DISTINCT TopicCode

, SUM(totalmeetingcount) AS totalmeetingcount

FROM(

SELECT DISTINCT TopicCode

, CASE TopicCode

WHEN 14.0 THEN

CASE WHEN totalmeetingcount >=2 THEN COUNT(*) END

WHEN 15.0 THEN

CASE WHEN totalmeetingcount >=5 THEN COUNT(*) END

WHEN 16.0 THEN

CASE WHEN totalmeetingcount >=3 THEN COUNT(*) END

WHEN 17.0 THEN

CASE WHEN totalmeetingcount >=2 THEN COUNT(*) END

WHEN 18.0 THEN

CASE WHEN totalmeetingcount >=2 THEN COUNT(*) END

WHEN 19.0 THEN

CASE WHEN totalmeetingcount >=3 THEN COUNT(*) END

ELSE 0

END AS totalmeetingcount

FROM cteCountMeetingOne

GROUP BY TopicCode

, totalmeetingcount

) a

GROUP BY TopicCode

)

SELECT * FROM cteCountMeeting

--WHERE topiccode = 14.0

****NOTICE ABOVE - if I put in the WHERE clause, it runs in under a second, without the WHERE clause OVER 20 seconds ****

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

GilaMonster (6/14/2013)
Please post ~~query~~, table definitions, index definitions and execution plans.

p.s. I'm not sure a 4-page long CTE qualifies as 'simple'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cpapas Valued Member Points: 66 More actions · Answer 4

Here's the execution plan in XML

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</Statements>

</Statements>

</Statements>

<StmtSimple StatementCompId="4" StatementEstRows="42.8058" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="100.768" StatementText=";WITH cteMain AS ( SELECT g.WorkerPK, g.WrkrLName , g.HireDate , RTRIM(w.FirstName) + ' ' + RTRIM(w.LastName) AS WorkerName , wp.Supervisor , wp.FSW , wp.FAW , wp.ProgramManager , wp.FatherAdvocate , ROW_NUMBER() OVER(ORDER BY g.workerpk DESC) AS 'RowNumber' , COUNT(*) OVER() AS TotalWorkers FROM dbo.fnGetWorkerEventDatesALL(@progfk, NULL, NULL) g INNER JOIN Worker w ON w.WorkerPK = g.WorkerPK INNER JOIN WorkerProgram wp ON g.WorkerPK = wp.WorkerFK WHERE (DATEDIFF(d, g.HireDate, GETDATE()) > 181 AND g.HireDate > @sdate AND wp.TerminationDate IS NULL) GROUP BY g.WorkerPK, g.WrkrLName , g.HireDate , w.FirstName , w.LastName , wp.Supervisor , wp.FSW , wp.FAW , wp.ProgramManager , wp.FatherAdvocate ) , cteAllRequiredTrainings AS ( SELECT topiccode, topicname, subtopiccode FROM dbo.codetopic t1 INNER JOIN SubTopic ON subtopic.TopicFK = t1.codetopicPK WHERE TopicCode BETWEEN 14.0 AND 19.0 AND programfk IS NULL AND RequiredBy='HFA' ) , cteMainMerge AS ( SELECT * FROM cteMain, cteAllRequiredTrainings ) --Now we get the trainings (or lack thereof) for topic code 14.0 , cteTrainings AS ( SELECT ta.workerfk , t1.TopicCode , t1.topicname , CASE IsExempt WHEN 1 THEN @sdate ELSE MIN(t.TrainingDate) END AS TrainingDate , Subtopic.SubTopicCode AS subtopiccode2 FROM cteMainMerge LEFT JOIN TrainingAttendee ta ON ta.WorkerFK = cteMainMerge.WorkerPK LEFT JOIN Training t ON t.TrainingPK = ta.TrainingFK LEFT JOIN TrainingDetail td ON td.TrainingFK = t.TrainingPK LEFT JOIN codeTopic t1 ON td.TopicFK=t1.codeTopicPK INNER JOIN SubTopic ON subtopic.TopicFK = td.TopicFK AND subtopic.SubTopicPK = td.SubTopicFK WHERE t1.TopicCode BETWEEN 14.0 AND 19.0 GROUP BY WorkerFK , HireDate , t1.TopicCode , t1.topicname , WorkerName , Supervisor , FSW , FAW , ProgramManager , FatherAdvocate , rownumber , TotalWorkers , IsExempt , SubTopic.subtopiccode ) , cteTrainingsUnique AS ( --this eliminates the problem that a worker could have both a regular training and an exempt training SELECT workerfk , topiccode ,topicname , MIN(trainingdate) AS TrainingDate , subtopiccode2 FROM cteTrainings GROUP BY workerfk , topiccode , topicname , subtopiccode2 ) , cte10_4Final AS ( --if a worker has NO trainings, they won't appear at all, so add them back select DISTINCT WorkerPK , RowNumber , TotalWorkers , HireDate , WorkerName , Supervisor , FSW , FAW , ProgramManager , FatherAdvocate , cteMainMerge.TopicCode , cteMainMerge.topicname , SubTopicCode , TrainingDate FROM cteMainMerge LEFT JOIN cteTrainingsUnique ON cteTrainingsUnique.workerfk=cteMainMerge.workerpk AND subtopiccode2=cteMainMerge.subtopiccode AND cteMainMerge.TopicCode=cteTrainingsUnique.TopicCode ) , cteMeetTarget AS ( SELECT TotalWorkers , cte10_4Final.WorkerPK , WorkerName , Supervisor , FSW , FAW , ProgramManager , FatherAdvocate , TopicCode , TopicName , TrainingDate , HireDate , SubTopicCode , CASE WHEN TrainingDate <= dateadd(day, 183, HireDate) THEN 'T' ELSE 'F' END AS 'Meets Target' , CASE WHEN TrainingDate IS NOT NULL THEN 1 ELSE 0 END AS 'ContentCompleted' FROM cte10_4Final GROUP BY cte10_4Final.WorkerPK , WorkerName , Supervisor , FSW , FAW , ProgramManager , FatherAdvocate , TopicCode , TopicName , TrainingDate , HireDate , rownumber , TotalWorkers , SubTopicCode ) --Now calculate the number meeting count , cteCountMeetingOne AS ( SELECT DISTINCT TopicCode , workerpk , COUNT(*) AS totalmeetingcount FROM cteMeetTarget WHERE [Meets Target]='T' GROUP BY TopicCode , workerpk ) --Now calculate the number me" StatementType="SELECT" QueryHash="0x119B9E84261C02D9" QueryPlanHash="0x76C5F757A30CABE9">

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

<ScalarOperator ScalarString="CASE WHEN [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]=(14.0) THEN CASE WHEN [Expr1108]>=(2) THEN [Expr1109] ELSE NULL END ELSE CASE WHEN [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]=(15.0) THEN CASE WHEN [Expr1108]>=(5) THEN [Expr1109] ELSE NULL END ELSE CASE WHEN [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]=(16.0) THEN CASE WHEN [Expr1108]>=(3) THEN [Expr1109] ELSE NULL END ELSE CASE WHEN [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]=(17.0) THEN CASE WHEN [Expr1108]>=(2) THEN [Expr1109] ELSE NULL END ELSE CASE WHEN [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]=(18.0) THEN CASE WHEN [Expr1108]>=(2) THEN [Expr1109] ELSE NULL END ELSE CASE WHEN [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]=(19.0) THEN CASE WHEN [Expr1108]>=(3) THEN [Expr1109] ELSE NULL END ELSE (0) END END END END END END">

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</GroupBy>

</OutputList>

</PartitionColumns>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</PartitionColumns>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</PartitionColumns>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</DefinedValue>

</DefinedValues>

</HashKeys>

</OutputList>

</PartitionColumns>

</OutputList>

</OutputList>

<Spool>

</OutputList>

</SegmentColumn>

</OutputList>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</ScalarOperator>

</Arithmetic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Arithmetic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</SegmentColumn>

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</DefinedValue>

</DefinedValues>

</HashKeys>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</DefinedValue>

</DefinedValues>

</HashKeys>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Bitmap>

</RelOp>

</OutputList>

</PartitionColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Predicate>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

<IF>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

<ScalarOperator ScalarString="CASE WHEN isnull([Expr1054],'2099-12-31 00:00:00.000')<=isnull([Expr1060],'2099-12-31 00:00:00.000') THEN CASE WHEN isnull([Expr1054],'2099-12-31 00:00:00.000')<=isnull([Expr1065],'2099-12-31 00:00:00.000') THEN [Expr1054] ELSE [Expr1065] END ELSE CASE WHEN isnull([Expr1054],'2099-12-31 00:00:00.000')>isnull([Expr1060],'2099-12-31 00:00:00.000') THEN CASE WHEN isnull([Expr1060],'2099-12-31 00:00:00.000')<=isnull([Expr1065],'2099-12-31 00:00:00.000') THEN [Expr1060] ELSE [Expr1065] END ELSE NULL END END">

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

</InnerSideJoinColumns>

</OuterSideJoinColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Residual>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</OutputList>

</InnerSideJoinColumns>

</OuterSideJoinColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Residual>

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

<Hash>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</PartitionColumns>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</ComputeScalar>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</PartitionColumns>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</DefinedValue>

</DefinedValues>

</HashKeys>

</OutputList>

</PartitionColumns>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</SegmentColumn>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</DefinedValue>

</DefinedValues>

</HashKeys>

</OutputList>

</PartitionColumns>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

<ScalarOperator ScalarString="[HFNY].[dbo].[WorkerProgram].[ProgramFK] as [wp].[ProgramFK]=[@progfk] AND [HFNY].[dbo].[WorkerProgram].[HireDate] as [wp].[HireDate]>[@sdate] AND [HFNY].[dbo].[WorkerProgram].[SupervisorFK] as [wp].[SupervisorFK]=[HFNY].[dbo].[WorkerProgram].[SupervisorFK] as [wp].[SupervisorFK]">

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Parallelism>

</RelOp>

</Bitmap>

</RelOp>

</OutputList>

</PartitionColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Predicate>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</ComputeScalar>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</Segment>

</RelOp>

</SequenceProject>

</RelOp>

</Parallelism>

</RelOp>

</Bitmap>

</RelOp>

</OutputList>

</PartitionColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Predicate>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</ComputeScalar>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</Merge>

</RelOp>

</Merge>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</Filter>

</RelOp>

</ComputeScalar>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Bitmap>

</RelOp>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</Parallelism>

</RelOp>

</Segment>

</RelOp>

</SequenceProject>

</RelOp>

</ComputeScalar>

</RelOp>

</Segment>

</RelOp>

</Spool>

</RelOp>

</OutputList>

</ScalarOperator>

</Predicate>

</OutputList>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</OutputList>

</RelOp>

</NestedLoops>

</RelOp>

</NestedLoops>

</RelOp>

</Parallelism>

</RelOp>

</Bitmap>

</RelOp>

</OutputList>

</PartitionColumns>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Predicate>

</OutputList>

<Hash>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</HashKeysBuild>

</HashKeysProbe>

<ScalarOperator ScalarString="[HFNY].[dbo].[TrainingAttendee].[TrainingFK] as [ta].[TrainingFK]=[HFNY].[dbo].[TrainingDetail].[TrainingFK] as

.[TrainingFK]">

</Identifier>

</ScalarOperator>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</OutputList>

<Hash>

</HashKeysBuild>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</HashKeysProbe>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</OutputList>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</PartitionColumns>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</OutputList>

<Hash>

</HashKeysBuild>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TopicFK" />

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="SubTopicFK" />

</HashKeysProbe>

<ScalarOperator ScalarString="[HFNY].[dbo].[SubTopic].[TopicFK]=[HFNY].[dbo].[TrainingDetail].[TopicFK] as

.[TopicFK] AND [HFNY].[dbo].[SubTopic].[SubTopicPK]=[HFNY].[dbo].[TrainingDetail].[SubTopicFK] as

.[SubTopicFK]">

</Identifier>

</ScalarOperator>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TopicFK" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="SubTopicFK" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</PartitionColumns>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

<ScalarOperator ScalarString="[HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]>=(14.0) AND [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]<=(19.0)">

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</OutputList>

</OuterReferences>

</OutputList>

</OutputList>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<ScalarOperator ScalarString="[HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]>=(14.0) AND [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]<=(19.0)">

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="SubTopicFK" />

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TopicFK" />

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</OutputList>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TopicFK" />

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="SubTopicFK" />

</PartitionColumns>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="SubTopicFK" />

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TopicFK" />

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</OutputList>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="SubTopicFK" />

</DefinedValue>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TopicFK" />

</DefinedValue>

<ColumnReference Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Alias="

" Column="TrainingFK" />

</DefinedValue>

</DefinedValues>

<Object Database="[HFNY]" Schema="[dbo]" Table="[TrainingDetail]" Index="[PK__Training__F23C2E472057CCD0]" Alias="

" IndexKind="Clustered" />

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</StreamAggregate>

</RelOp>

</Parallelism>

</RelOp>

</StreamAggregate>

</RelOp>

</Parallelism>

</RelOp>

</ComputeScalar>

</RelOp>

</OutputList>

</PartitionColumns>

</OutputList>

</OutputList>

</OuterReferences>

</OutputList>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<ScalarOperator ScalarString="[HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]>=(14.0) AND [HFNY].[dbo].[codeTopic].[TopicCode] as [t1].[TopicCode]<=(19.0)">

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

<Spool>

</OutputList>

</OutputList>

<Spool>

</OutputList>

</SegmentColumn>

</OutputList>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</ScalarOperator>

</Arithmetic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Arithmetic>

</ScalarOperator>

</DefinedValue>

</ScalarOperator>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</SegmentColumn>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

<IF>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

<ScalarOperator ScalarString="CASE WHEN isnull([Expr1010],'2099-12-31 00:00:00.000')<=isnull([Expr1016],'2099-12-31 00:00:00.000') THEN CASE WHEN isnull([Expr1010],'2099-12-31 00:00:00.000')<=isnull([Expr1021],'2099-12-31 00:00:00.000') THEN [Expr1010] ELSE [Expr1021] END ELSE CASE WHEN isnull([Expr1010],'2099-12-31 00:00:00.000')>isnull([Expr1016],'2099-12-31 00:00:00.000') THEN CASE WHEN isnull([Expr1016],'2099-12-31 00:00:00.000')<=isnull([Expr1021],'2099-12-31 00:00:00.000') THEN [Expr1016] ELSE [Expr1021] END ELSE NULL END END">

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</Identifier>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</ComputeScalar>

</RelOp>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</OutputList>

<Hash>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</ComputeScalar>

</RelOp>

</Hash>

</RelOp>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</SegmentColumn>

</OutputList>

</OrderByColumn>

</OrderByColumn>

</OrderByColumn>

</OrderBy>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

<ScalarOperator ScalarString="[HFNY].[dbo].[WorkerProgram].[ProgramFK] as [wp].[ProgramFK]=[@progfk] AND [HFNY].[dbo].[WorkerProgram].[HireDate] as [wp].[HireDate]>[@sdate] AND [HFNY].[dbo].[WorkerProgram].[SupervisorFK] as [wp].[SupervisorFK]=[HFNY].[dbo].[WorkerProgram].[SupervisorFK] as [wp].[SupervisorFK]">

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</GroupBy>

</OutputList>

</OrderByColumn>

</OrderBy>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</NestedLoops>

</RelOp>

</Sort>

</RelOp>

</Segment>

</RelOp>

</SequenceProject>

</RelOp>

</OutputList>

</Identifier>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</ComputeScalar>

</RelOp>

</Hash>

</RelOp>

</Sort>

</RelOp>

</Hash>

</RelOp>

</Hash>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</Filter>

</RelOp>

</ComputeScalar>

</RelOp>

</Sort>

</RelOp>

</Hash>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</Sort>

</RelOp>

</Segment>

</RelOp>

</SequenceProject>

</RelOp>

</ComputeScalar>

</RelOp>

</Segment>

</RelOp>

</Spool>

</RelOp>

</OutputList>

</ScalarOperator>

</Predicate>

</OutputList>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</OutputList>

</RelOp>

</NestedLoops>

</RelOp>

</NestedLoops>

</RelOp>

</Spool>

</RelOp>

</NestedLoops>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

<IF>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</Filter>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</Parallelism>

</RelOp>

</StreamAggregate>

</RelOp>

</StreamAggregate>

</RelOp>

</Parallelism>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</Sort>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

The actual execution plan saved as an *.sqlplan file and attached (uploaded) to a post would be much more helpful. Personally, I'm not going to spend a lot of time trying to read through an XML execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Query and execution plan down (kinda), table and index definitions still outstanding.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cpapas Valued Member Points: 66 More actions · Answer 7

I can't seem to attach a file to a post. All I see is the hyperlink option.

cpapas Valued Member Points: 66 More actions · Answer 8

I've found the first place the error occurs, so I've substantially simplified the query. If someone can tell me how to attach a file, I can get a new execution plan attached.

Thanks and here is the simpler code:

DECLARE @sdate AS DATE

DECLARE @progfk AS INT

SET @progfk=1

SET @sdate = '01/01/2009'

SET NOCOUNT ON;

;WITH cteMain AS (

SELECT g.WorkerPK, g.WrkrLName

, g.HireDate

, RTRIM(w.FirstName) + ' ' + RTRIM(w.LastName) AS WorkerName

, wp.Supervisor

, wp.FSW

, wp.FAW

, wp.ProgramManager

, wp.FatherAdvocate

, ROW_NUMBER() OVER(ORDER BY g.workerpk DESC) AS 'RowNumber'

, COUNT(*) OVER() AS TotalWorkers

FROM dbo.fnGetWorkerEventDatesALL(@progfk, NULL, NULL) g

INNER JOIN Worker w ON w.WorkerPK = g.WorkerPK

INNER JOIN WorkerProgram wp ON g.WorkerPK = wp.WorkerFK

WHERE (DATEDIFF(d, g.HireDate, GETDATE()) > 181

AND g.HireDate > @sdate

AND wp.TerminationDate IS NULL)

GROUP BY g.WorkerPK, g.WrkrLName

, g.HireDate

, w.FirstName

, w.LastName

, wp.Supervisor

, wp.FSW

, wp.FAW

, wp.ProgramManager

, wp.FatherAdvocate

)

, cteAllRequiredTrainings AS (

SELECT topiccode, topicname, subtopiccode FROM dbo.codetopic t1

INNER JOIN SubTopic ON subtopic.TopicFK = t1.codetopicPK

WHERE TopicCode BETWEEN 14.0 AND 19.0 AND programfk IS NULL AND RequiredBy='HFA'

)

, cteMainMerge AS (

SELECT * FROM cteMain, cteAllRequiredTrainings

)

--Now we get the trainings (or lack thereof) for topic code 14.0

, cteTrainings AS (

SELECT

ta.workerfk

, cteMainMerge.TopicCode

, t1.topicname

, CASE IsExempt

WHEN 1 THEN @sdate

ELSE MIN(t.TrainingDate)

END AS TrainingDate

, subtopiccode AS subtopiccode2

--, Subtopic.SubTopicCode AS subtopiccode2

FROM cteMainMerge

LEFT JOIN TrainingAttendee ta ON ta.WorkerFK = cteMainMerge.WorkerPK

LEFT JOIN Training t ON t.TrainingPK = ta.TrainingFK

LEFT JOIN TrainingDetail td ON td.TrainingFK = t.TrainingPK

LEFT JOIN codeTopic t1 ON td.TopicFK=t1.codeTopicPK

--INNER JOIN SubTopic ON subtopic.TopicFK = td.TopicFK AND subtopic.SubTopicPK = td.SubTopicFK

WHERE t1.TopicCode BETWEEN 14.0 AND 19.0

GROUP BY

WorkerFK

, HireDate

, cteMainMerge.TopicCode

, t1.topicname

, WorkerName

, Supervisor

, FSW

, FAW

, ProgramManager

, FatherAdvocate

, rownumber

, TotalWorkers

, IsExempt

, subtopiccode

)

SELECT * FROM cteTrainings

WHERE topiccode=14.0

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

cpapas (6/14/2013)
I can't seem to attach a file to a post. All I see is the hyperlink option.

Below the text area, a button labled 'edit attachments'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cpapas Valued Member Points: 66 More actions · Answer 10

Something that might help troubleshoot. If I change the where clause to something that is NOT in the table, for example WHERE TopicCode>20.0, then the query runs INSTANTLY (much less than 1 second).

So it looks like SQL is trying to figure something out and I'm looking at the datatype of Numeric(4,1) being the issue. I'm going to change it to integer and test.

cpapas Valued Member Points: 66 More actions · Answer 11

OK, first thanks to everyone for helping.

I figured out the issue and for anyone who cares here is the problem and here is the solution.

The problem was in the "cteTrainings", specifically the WHERE clause "WHERE t1.TopicCode Between 14.0 and 19.0"

Why? Because it was joining EVERYTHING in my tables, then filtering out those that were between 14 and 19.

The Solution Was to remove the WHERE clause entirely, and modify my join statements:

LEFT JOIN codeTopic t1 ON td.TopicFK=t1.codeTopicPK AND cteMainMerge.topiccode = t1.TopicCode

INNER JOIN SubTopic ON subtopic.TopicFK = td.TopicFK AND subtopic.SubTopicPK = td.SubTopicFK

AND cteMainMerge.subtopiccode = subtopic.subtopiccode

The items in bold were what I changed.

Why this could run so quickly with a "Select * from cteTraining where TopicCode = 14.0", is a level of SQL I do not have, because I would think it first has to run the COMPLETE cte before it could select specific values.

But it now runs in under 4 seconds.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Part of this is your understanding of CTE's. They are not executed as separate queries, they are actually expanded out in the final query where they are used. Think of them as temporary views or derived tables declared in the actual FROM clause of the final query you are writing.

CTE's make writing these types of queries easier as you can build complex queries in small steps.

I am sure I am oversimplifying things here, but you may want to do some more reading about them in Books Online and I am sure others may post links to Blog posts and other articles that will help as well.

Lowell SSC Guru Points: 323504 More actions · Answer 13

first thing i see is the non-Sargable WHERe statemnet in the first CTE:

cteMain is not Sargable:

WHERE (DATEDIFF(d, g.HireDate, GETDATE()) > 181

i think this is the equivilent, and would use an index (if it exists) on g.HireDate.

putting a function on a column makes it non-sargable, and requires a table scan/index scan

WHERE HIREDATE < datediff(d,-181,GETDATE())

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cpapas Valued Member Points: 66 More actions · Answer 14

Thanks Lowell. Made the change, only I used DateAdd instead of DateDiff. Appreciate the advice.