Performance of CTE(Common table expression) Vs Hash(#)Tables.

Let say we have one report and in which we want data from around 20 tables.

so i just created some 4 to 5 cte(Common table expression) for every 4 to 5 tables.

and then join cte's and get the data.but when i execute that stored procedure it will take around 10 Minutes.

i am really strange about this.

Now what i do just replace all cte(Common table expression) with Hash(#)table.

and it's executed with only around 15 seconds.

So i suggest when cte 1 or 2 then ok but 4 to 5 then you can use #tables.

This is work for me.Let's you guys try it.if you use cte and it will take too much time to execute.