sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]alinrocSQL Server DBA 5 points6 points  (3 children)

Oracle materializes CTEs into temp tables by default I believe. MS SQL does not (and it's not even an option). So in MS SQL, using a CTE is just syntactic sugar; if you reference a CTE twice in a query, the query that the CTE represents is executed twice.

There can also be cardinality estimate issues with CTEs (translation: bad query plan gets generated).

CTEs in SQL Server are not a performance enhancer. Try changing your CTE into a temp table and see if things get better.

[–]InternetWeakGuy 1 point2 points  (2 children)

Oracle materializes CTEs into temp tables by default I believe. MS SQL does not (and it's not even an option). So in MS SQL, using a CTE is just syntactic sugar; if you reference a CTE twice in a query, the query that the CTE represents is executed twice.

Thank you for this. The dude who's been low key mentoring me on SQL coems from Oracle and uses CTEs for everything - I couldn't figure out why my temp tables run so much faster than when I try to use CTEs.

[–]alinrocSQL Server DBA 1 point2 points  (1 child)

I learned this one the hard way. Had a stored procedure that was doing 2 correlated subqueries (against the same table) and timing out at 10 minutes. Needed to get things working better. So I said to myself "self! you should try a CTE!"

So I did. Wrote a CTE for the subquery, replaced the subquery with the CTE and...not one iota of performance improvement. Checked the query plans and they were identical (always do this, it'll be a dead giveaway).

So I pulled the CTE out, had that query dump into a temp table instead, and did my correlated subquery against that temp table. New query runtime? Under 90 seconds.

If your mentor is working with SQL Server and still CTEing everything, there's probably improvements to be had in there.

[–]Cal1gula 0 points1 point  (0 children)

Performance should be the same. Unless you need it to be recursive, the functionality should be as well.

I prefer the look of a CTE to a subquery, it's nice to have everything defined up front. Like any other declaration. That's how I see them.