Calling CTE multiple times in same query

I have a fairly complicated query with multiple CTEs but 1 main CTE that the others all pull from, does this cause that main CTE to be executed multiple times?

Answers


You could use CROSS JOIN thus:

SELECT 
    AVG(CASE WHEN instructorID = @instructorID THEN score END) InstructorSemesterAverage,
    STDEVP(CASE WHEN instructorID = @instructorID THEN score END) InstructorSemesterSTDeviation,
    AVG(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterAverage, 
    STDEVP(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterSTDeviation, 
    AVG(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassScore,
    STDEVP(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassSTDeviation,
    (SELECT DecTile FROM cteNtile WHERE instructorID = @instructorID)*10 DecTile,
    X.DepartmentClassFiveYearAverage AS DepartmentClassFiveYearAverage,
    X.DepartmentClassFiveYearSTDeviation AS DepartmentClassFiveYearSTDeviation,
    X.InstructorClassFiveYearAverage AS InstructorClassFiveYearAverage,
    X.InstructorClassFiveYearSTDeviation AS InstructorClassFiveYearSTDeviation
FROM 
    cteMain Q CROSS JOIN cteFiveYear X

This will prevent multiple executions (for actual execution plan see Number of Executions property) for cteFiveYear.

Example: If you execute this query

SELECT  h.ProductID,h.StandardCost,
        x.AvgPrice
FROM    Production.ProductCostHistory h
CROSS JOIN (
    SELECT  AVG(p.ListPrice) AvgPrice
    FROM    Production.Product p
) x

using AdventureWorks2008R2 database then the actual execution plan will be


Need Your Help

Get array from model to view

javascript asp.net-mvc-3 view model razor

In my model I have one int object and a boolean array:

What is the difference between Hashing vectorizer and Count vectorizer, when each to be used?

machine-learning scikit-learn classification svm

I am trying with various SVM variants in scikit-learn along with CountVectorizer and HashingVectorizer. They use fit or fit_transform in different examples, confusing me which to be used when.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.