Share via

What is the best choice for this scenario - CTE or View

Nagesh CL 706 Reputation points
2024-02-07T11:11:16.49+00:00

Hello Team, I have a scenario where in the source data is in an Azure SQL DB. The data in the source is stored in the form of snapshots. For Ex:- Day1 - 500K Records, Day2 - 510K Records - Day3 - 520K Records (Total - 1.53M records). All the tables the data is stored in snapshots like above. From source, we have to extract the latest data based on natural key, snapshot date and load the target db (In my case Azure SQL DB). To achieve this, I can see 2 options (Unless anybody has even better approach). Option 1 - Use CTEs to create row number based on natural key, order by snapshot date desc. And then use the CTEs (Filter the records -> Where rownumber = 1) in the subsequent source queries and load data to target. Option 2 - Create views in source system which does the same rownumber logic and subsequently use the views as source to build queries. It might be possible that to load one table in target, we might have to join one or more tables. Lets say we need to join 5 tables, then we would end up creating 5 CTEs to generate rownumber and then use that. Considering this scenario which option is the best in terms of performance? Regards, Nagesh CL

Azure SQL Database
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Raja S 0 Reputation points
    2024-10-02T22:20:23.97+00:00

    Azure SQL and MS SQL share a common problem with CTE's.

    CTE's are executed once per join in the dependent SELECT. Thus if you have to join the same CTE twice in the final SQL, you will be traversing your 100M rows multiple times which is not very performant.

    I would go for TEMP table if the resultant dataset is less than 1/3 of available session memory or work memory. If more I would go for a view.

    Visit my blog on CTE optimization comparing CTE across multiple databases. https://www.linkedin.com/pulse/ctesubquery-factoring-optimization-raja-surapaneni-jyjie/

    0 comments
  2. Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator
    2024-02-07T22:54:43.0166667+00:00

    To me the solutions seem equivalent, if I understand you correctly. A CTE is the same thing as a view, the sole difference is that the CTE is scoped to that one single query.

    Keep in mind that both are just macros. The aglebrizer expands the text of the CTE or the view into the query, and the optimizer works with the final query.

    If you are writing a stored procedure, it may be better to materialise some of those CTEs/views into a temp table and use the temp table in the final query. This can help the optimizer to find a better plan. Or it can just mean extra overhead. This is not that easy to tell beforehand.

    0 comments
  3. Olaf Helper 47,616 Reputation points
    2024-02-07T11:29:20.7566667+00:00

    Considering this scenario which option is the best in terms of performance?

    A CTE = "Common Table Expression" is just a different, better readable way to write queries; it as absolutly no effect on query performance. https://learn.microsoft.com/de-de/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.