7

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?

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 3
    `TOP 100 PERCENT` is pretty much always ignored. `TOP 1E2 PERCENT` *might* work though. Also `TOP 2147483647` presumably shouldn't be `PERCENT`. Even if it works now though it might stop working at any time. Materialising into a `#temp` table yourself will be more robust. – Martin Smith Oct 26 '12 at 15:42
  • @MartinSmith Thank you. It was only the number, without the percent. Anyway, TOP 1E2 does not works too. Actually, I have learned to "materialized" the CTEs from one of the your answers to my questions :-] . Unfortunately, they seems not to work on SQL MS 2012 ... I am not able to use #temp table as this is code is view. – gotqn Oct 26 '12 at 15:54
  • 2
    @Blam - SQL Server may or may not materialize it into a spool rather than repeatedly re-evaluating the underlying query though. [This is discussed in the connect item here](http://connect.microsoft.com/SQLServer/feedback/details/218968/). I'm not at a machine with access to a 2012 instance at the moment though. – Martin Smith Oct 26 '12 at 17:54

1 Answers1

9

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • Actually, I do the same - use table-valued function. Creating @table in the function and inserting the information there let us to better performance. Anyway, in SQL 2008 we have rewrite a lot of table valued function as ordinary views with CTE in order to achieve better performance. Now in SQL 2012, if the CTE is not materialized in some cases, I am worried that we will met a lot of performance issues. – gotqn Oct 27 '12 at 09:34
  • I've been using this technique for a while with a lot of success (thanks @Bogdan), but I just struck one very complex query where it fell down. The @Results SELECT returned <20 rows in 3-4 seconds, but then that blew out to 30-40 seconds when multiple CTE references were layered on top (it has to calculate multiple medians etc). I copied and pasted the exact same logic into a Stored Proc with a Temp table and it runs in 3-4 seconds. Somehow the optimizer must be bypassing the whole table variable (including PRIMARY KEY constraint) and generating multiple SELECTs! Beware ... – Mike Honey Jul 09 '15 at 05:54