>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?

I got more or less the same experience, just check the execution plans for both