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?
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.
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.
SQL Server supports two types of CTEs-recursive and nonrecursive.
CTE can be used for both selects and DML (Insert, Update, and Delete) statements.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With