June 6, 2012 at 12:13 pm
I have a general question about CTEs. We are using CTEs all over our SSIS packages and they are doing real good. Each time an SSIS package runs, CTEs handle millions of data, around 1 - 2 million records. But, the people around me says CTEs are not good for huge data and they should be replaced by temp tables. Is that true? Can someone suggest ?
Thank you
June 6, 2012 at 12:21 pm
It depends. You would need to test this for each situation. If you define a CTE and use it once in the immediately following query, then taking the time to load the data into a temporary table may not be the best way to go. If you define a CTE and then call it several times, then possibly putting the data into a temporary table is better.
I don't have the link but there is a CONNECT item to add a hint that if implemented would have SQL Server treat a CTE as temporary table. This is one area that Oracle has a slight advantage in right now as it it can dynamically decide if it should treat its subquery refactoring clause (SQL Servers CTE) as an inline query or a temporary table.
June 8, 2012 at 2:16 pm
Thank you very much ,That is helpful. Right now, we don't see performance issues. We should be good.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply