Any SQL where clause performance similar to where 1=0

(i'm using SQL Server 2008R2 or later)

For centralize manage and consistent, there is a view definition as follow:

CREATE VIEW view_all_situation
(
  select null as src, tf.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee tf
  inner join parameter_table pt on tf.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 0
  group by tf.col1

  UNION ALL

  select tfd.src, tfd.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee_detail tfd
  inner join parameter_table pt on tfd.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 1
  group by tfd.col1
)
(Note: parameter_table only have 1 row)

so if the value in parameter_table.col1 = 1 then return table_fee_detail else return table_fee. (Note: parameter_table only have 1 row)

As the first part require 10 secs and second part require another 10 secs, finally, it costs 20 secs to return. If there any faster way to let SQL Server execute either one part of the UNION ALL base on the parameter_table value so that it returns in 10 secs?

As dynamic SQL is not applicable in my situation, i can't build it to get the result.

I've tried to use

where EXISTS (select 1 from parameter_table where col1 = 0) and 
where EXISTS (select 1 from parameter_table where col1 = 1)

as the where clause, it cannot make the SQL Server to return in 10 secs. It still use 20 secs.

Is it possible to have SQL with result like

where 1=0

so that SQL Server return the result in 10 secs?

```
For example,
if parameter_table.col1 = 0, i get result from table_fee
Src col1 fee fee1
--------------------------------------------
null USD 100 150
null EUR 200 150
null AUD 300 200

if parameter_table.col1 = 1, I get result from table_fee_detail
Src col1 fee fee1
--------------------------------------------
USA USD 100 150
GER EUR 200 150
AU AUD 300 20

You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL.

To demonstrate, here is a simplified version based on the AdventureWorks sample database:

The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of the UseArchive column in a Parameter table:

CREATE TABLE dbo.Parameter
(
    ProductID integer NOT NULL PRIMARY KEY,
    UseArchive bit NOT NULL
);

INSERT dbo.Parameter
    (ProductID, UseArchive)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 0);

This example specifies that rows should come from TransactionHistory for products 1, 2, and 4. For product 3, rows should come from TransactionHistoryArchive.

Solution:

SELECT 
    P.Name, 
    CA.TransactionID
FROM Production.Product AS P
JOIN dbo.Parameter AS PTR
    ON PTR.ProductID = P.ProductID
CROSS APPLY
(
    SELECT TH.ProductID, TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE 
        TH.ProductID = P.ProductID
        AND PTR.UseArchive = 0

    UNION ALL

    SELECT THA.ProductID, THA.TransactionID
    FROM Production.TransactionHistoryArchive AS THA
    WHERE 
        THA.ProductID = P.ProductID
        AND PTR.UseArchive = 1
) AS CA;

The execution plan for this query is:

The key here is the outer reference PTR.UseArchive = value inside the apply. This allows the optimizer to construct a plan featuring Filters with Start-Up Expressions. For example, the Filter above the TransactionHistory table Index Seek has:

The Filter Predicate above the other seek is very similar, of course, but tests UseArchive = 1.

Each Filter Predicate is evaluated once per product. It determines whether the seek below it in the plan will be executed on that iteration of the nested loop join.

In this example, the effect is that the TransactionHistory index seek is executed three times; the seek on the TransactionHistoryArchive table is executed just once.

You may be able to implement this pattern for your use case.

StackExchange Database Administrators Q#109321, answer score: 4