SQL - Cte VS View

SQL - CTE vs VIEW

Views can be indexed but CTE can't. So this is one important point.

CTE work excellent on tree hierarchyi.e. recursive

Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, on the other hand are temporary and will be created when they are used; that's why they are called as inline view.

Update

According to your updated question, views will be the right choice. Dealing with 3.5 million rows in CTE will create extra overhead on TempDb which will eventually slow down SQL Server performance. Remember, CTE is a disposable view hence no statistics are stored and you can't create Indexes too. It is just like a sub query.

Is there any performance difference btw using CTE, view and subquery?

CTE is just syntax so in theory it is just a subquery. you may not get any performance difference while using CTE and Subquery.

I think the biggest benefit for using CTEs is readability. It makes it much easier to see what queries are being used as subqueries, and then it's easy to join them into a query, much like a view.

Now about CTE and View Hope you can read this and see what is suitable for you.

CTE vs View Performance in SQL Server

SQL - CTE vs VIEW

Its also depends how you designed you query and script structure.

SQL CTE vs View

You don't really need two subqueries and a join. Instead, you can enumerate the rows both ways with row_number(), and then filter once, and do conditional aggregation:

select CompanyId, UserId,
    max(case when rowNumMax = 1 then Key1 end) as MaxKey,
    max(LogDateTime)                           as MaxLogDateTime,
    min(case when rowNumMin = 1 then Key1 end) as MinKey,
    min(LogDateTime)                           as MinLogDateTime
from (
    select t.*, 
        row_number() over (partition by UserId order by LogDateTime) as rowNumMin,
        row_number() over (partition by UserId order by LogDateTime desc) as rowNumMax
    from [dbo].[TestTransaction] t
    where CompanyId = @companyId
) t
where 1 in (rowNumMin, rowNumMax)
group by CompanyId, UserId

CTE vs View Performance in SQL Server

A view is a permanent object and the results can be indexed, while a CTE is temporary and created only when used so less flexible. It will be more efficient to break apart your complex query into indexed views than into CTE's. It will be most efficient to ensure all of the tables are properly indexed, which will probably do more for performance than worrying about views vs. CTE's.

WITH Common Table Expression vs. CREATE VIEW performance

What you're looking at is a Common Table Expression and not a View. If you're doing recursion, you need to stick with the CTE and not try to roll that into a view.

Views in MS SQL don't give you any performance benefits unless you are creating clustered indexes on them. From the sounds of your question, that is not the case. You'd likely be better off encapsulating your CTE inside of a stored procedure.

Am I forced to use a CTE or stored procedure instead of a view?

The best way to do this is to put the expression in an APPLY, which means you can re-use it anywhere in the query:

SELECT
    v1.Temp + 1 AS a,
    v1.Temp + 2 AS b,
    v1.Temp + 3 AS c,
    v1.Temp + 4 AS d,
FROM some_table
CROSS APPLY (VALUES (
    CASE WHEN /* 20 lines case expression */
    END
) ) AS v1(Temp)

Difference between SQL View and WITH clause

SQL views and with clauses are very similar. Here are some differences.

Views create an actual object in the database, with associated metadata and security capabilities. With statements are only part of a single query.

In many databases, views have options, for instance, to index them or to "instantiate" them.

With statements offer the opportunity to have recursive CTEs, in some databases. This is not possible for views.

For simple subqueries incorporated into queries, they are quite similar. The choice really depends on whether you want to create a reusable code (views) or are focused on a single query (with).

Which are more performant, CTE or temporary tables?

I'd say they are different concepts but not too different to say "chalk and cheese".

• A temp table is good for re-use or to perform multiple processing passes on a set of data.

• A CTE can be used either to recurse or to simply improved readability.
  
  And, like a view or inline table valued function can also be treated like a macro to be expanded in the main query

• A temp table is another table with some rules around scope

I have stored procs where I use both (and table variables too)

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

SQL is a declarative language, not a procedural language. That is, you construct a SQL statement to describe the results that you want. You are not telling the SQL engine how to do the work.

As a general rule, it is a good idea to let the SQL engine and SQL optimizer find the best query plan. There are many person-years of effort that go into developing a SQL engine, so let the engineers do what they know how to do.

Of course, there are situations where the query plan is not optimal. Then you want to use query hints, restructure the query, update statistics, use temporary tables, add indexes, and so on to get better performance.

As for your question. The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times. Unfortunately, SQL Server does not seem to take advantage of this basic optimization method (you might call this common subquery elimination).

Temporary tables are a different matter, because you are providing more guidance on how the query should be run. One major difference is that the optimizer can use statistics from the temporary table to establish its query plan. This can result in performance gains. Also, if you have a complicated CTE (subquery) that is used more than once, then storing it in a temporary table will often give a performance boost. The query is executed only once.

The answer to your question is that you need to play around to get the performance you expect, particularly for complex queries that are run on a regular basis. In an ideal world, the query optimizer would find the perfect execution path. Although it often does, you may be able to find a way to get better performance.

SQL CTE in a View vs Temp Table in a Stored Procedure

Just to put a bow on this one, here's what I ended up doing:

Instead of using a View to join all possible rows from 2 or 3 tables, I created a Table Based Function that makes the same basic query. As one of the parameters I pass in the Building ID, and use that in a WHERE clause, like so:

SELECT  l.BuildingID, l.ApartmentID, l.LeaseID, l.ApplicantID,
            ROW_NUMBER() OVER (PARTITION BY l.ApartmentID ORDER BY s.Importance DESC, MovedOut, MovedIN DESC, LLSigned DESC, Approved DESC, Applied DESC) AS 'RowNumber'
FROM    dbo.NPleaseapplicant AS l INNER JOIN
        dbo.NPappstatus AS s ON l.BuildingID = s.BuildingID AND l.AppStatus = s.Code
WHERE  (l.BuildingID = @BuildingID)

The result is that it drastically reduces the number of joins required, and it speeds up the query immensely.

I then change all the stored procedures that rely on the View to use the Function instead, and bingo -- huge performance gains.

---

Related Topics