>I was surprised when the following query was extremely slow:
>
>

>SET NOCOUNT ON
>
>WITH DoublePunches (puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, FXCount)
>AS
>(
>select puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, count(*) AS FXCount
>		FROM handpunch_punches 
>		WHERE DelFlag = 0 
>		GROUP BY puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut 
>		HAVING count(*) > 1
>)
>, RestPunches (puKey, puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut)
>AS
>(
>SELECT MAX(Handpunch_Punches.puKey) AS puKey,
>		Handpunch_Punches.puEmKey, 
>		Handpunch_Punches.puDateIn, 
>		Handpunch_Punches.puTimeIn, 
>		Handpunch_Punches.puDateOut, 
>		Handpunch_Punches.puTimeOut
>		FROM Handpunch_Punches
>		JOIN DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		GROUP BY Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut 
>)
>
>DELETE Handpunch_Punches
>		FROM Handpunch_Punches
>		JOIN DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		WHERE NOT EXISTS(SELECT NULL FROM RestPunches WHERE RestPunches.puKey = Handpunch_Punches.puKey);
>

>
>While the query using temporary tables was extremely fast:
>
>

>SET NOCOUNT ON
>
>SELECT puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, count(*) AS FXCount
>		INTO #DoublePunches
>		FROM handpunch_punches 
>		WHERE DelFlag = 0 
>		GROUP BY puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut
>		HAVING count(*) > 1
>
>SELECT MAX(Handpunch_Punches.puKey) AS puKey,
>		Handpunch_Punches.puEmKey, 
>		Handpunch_Punches.puDateIn, 
>		Handpunch_Punches.puTimeIn, 
>		Handpunch_Punches.puDateOut, 
>		Handpunch_Punches.puTimeOut
>		INTO #RestPunches
>		FROM Handpunch_Punches
>		JOIN #DoublePunches DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		GROUP BY Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut 
>
>DELETE Handpunch_Punches
>		FROM Handpunch_Punches
>		JOIN #DoublePunches DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		WHERE NOT EXISTS(SELECT NULL FROM #RestPunches RestPunches WHERE RestPunches.puKey = Handpunch_Punches.puKey);
>
>
>DROP TABLE #DoublePunches
>DROP TABLE #RestPunches
>

>
>So either I misunderstand the purpose of WITH or there is something else I am not aware of?

Check the sql plan and find where sql do a not smart scan.
T-SQL is a declarative language.
This is an illusion, because in the end,
those who do not satisfies the data but wants them as quickly as possible,
must struggle with indices, statistics and the form of logic
to force the sql engine to work in the best way.

Put an OR condition somewhere and almost always the engine loses the compass.