Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL CTE materializing techniques not working on SQL Server 2012

I have to use the following techniques to materialized my CTEs and increase the view performance:

WITH CTE AS(
    SELECT TOP 100 PERCENT
    ORDER BY ...
)

WITH CTE AS(
    SELECT TOP 2147483647
    ORDER BY ...
)

Now, neither of this ways works. Has anyone face the same issue or know if in SQL Server 2012 this things are not valid?

like image 814
gotqn Avatar asked Oct 26 '12 15:10

gotqn


People also ask

Is using CTE better than temp table?

If you are joining multiple tables with millions of rows of records in each, CTE will perform significantly worse than temporary tables. I've seen this from my own experience. CTE's perform significantly slower. CTE's also perform slower because the results are not cached.

What is the disadvantage of CTE in SQL Server?

Disadvantages of CTECTE's members cannot use the following clauses of keywords Distinct, Group By, Having, Top, Joins limiting by this type of the queries that can be created and reducing their complexity. The Recursive member can refer to the CTE only once.

What are the two types of CTE supported by SQL Server?

SQL Server supports two types of CTEs-recursive and nonrecursive.

Can we perform DML operations on CTE?

CTE can be used for both selects and DML (Insert, Update, and Delete) statements.


1 Answers

You could try using a multi-step table valued function. This way, the server is forced to materialize the TVF's results into a table variable. Also, you could try using declarative constraints when declaring the this table type (PRIMARY KEY, UNIQUE, CHECK) to improve the performance of the final query:

CREATE FUNCTION CocoJamboSchema.CocoJamboFunction(@parameters ...)
RETURNS @Results TABLE (
    Col1 INT NOT NULL,
    Col2 VARCHAR(10) NULL,
    ...
    PRIMARY KEY(Col1)
)
AS
BEGIN
    WITH MyCTE (...)
    AS
    (
        ...
    )
    INSERT @Results (...)
        FROM MyCTE;

    RETURN;
END;

SELECT ...
FROM CocoJamboSchema.CocoJamboFunction(param values) f
INNER JOIN MySchema.MyTable t ON f.Col1=t.Col1
ORDER BY t.Col1;

Don't forget to add the ORDER BY clause to your final query.

Recently, I used this solution to optimize a view (ViewA, DISTINCT + LEFT JOIN + GETDATE()) used by another views (ViewB). In this case (ViewA) was impossible to create a indexed view (because of DISTINCT + LEFT JOIN + GETDATE()). Instead, I created a multi-statement TVF that improved the performance by reducing the logical reads (drasticaly in some cases) of the final query.

Note: Off course, you could try using an index view.

like image 94
Bogdan Sahlean Avatar answered Sep 19 '22 00:09

Bogdan Sahlean