How to prevent SQL Server from running the same subquery multiple times-CodePudding

I have a query that follows the following structure:

SELECT * 
FROM 
    ... <generated code> ...
    (SELECT <fields>,  
        CASE(SELECT TOP 1 ID FROM [Configuration] WHERE IsDefault=1 ORDER BY ID)
            WHEN 1 THEN t.FirstName
            WHEN 2 THEN t.LastName END As Identifier
     FROM <table> t) AS tmp
... <generated code> ...
WHERE <generated filters>

In the query execution plan I see that a Clustered Index Scan on the Configuration table is being executed the same number of times as there are numbers in <table>, however, I know that the result of those scans is always going to be the same, when I replace the

SELECT TOP 1 ID 
FROM [Configuration] 
WHERE IsDefault = 1 
ORDER BY ID

part for the current value of the configuration, this query runs fast.

I'm looking for a way to tell SQL Server that this subquery always has the same result so that it runs fast, the obvious way I see is to declare a temporary variable with the value of that query and use the variable in the main query, the problem is that the beginning and end of the query is generated by the application code and I don't have manual control over that.

The ideal solution for me would be to create a deterministic function that runs that query, and have SQL Server know that since the function is deterministic, and it doesn't depend on the current row, it only needs to run it once, but for some reason it just didn't work and it still ran a bunch of times.

How should I go about optimizing this? Am I misunderstanding deterministic functions? Did I just do it wrong with the function? Is there another way?

CodePudding user response：

As mentioned in the comments, using a CROSS JOIN works, the final query is:

SELECT * 
FROM 
     ... <generated code> ...
    (SELECT <fields>,  
        CASE config.ID
            WHEN 1 THEN t.FirstName
            WHEN 2 THEN t.LastName END As Identifier
     FROM <table> t
     CROSS JOIN (SELECT TOP 1 ID FROM [Configuration] WHERE IsDefault=1 ORDER BY ID) config) AS tmp
... <generated code> ...
WHERE <generated filters>

As mentioned in the question using a temporary variable would fix the execution plan but is not an option for me because the top part of the query is generated by code from a different component

DECLARE @config INT
SET @config = (SELECT TOP 1 ID FROM [Configuration] WHERE IsDefault=1 ORDER BY ID)
SELECT * 
FROM 
     ... <generated code> ...
    (SELECT <fields>,  
        CASE @config
            WHEN 1 THEN t.FirstName
            WHEN 2 THEN t.LastName END As Identifier
     FROM <table> t) AS tmp
... <generated code> ...
WHERE <generated filters>