3

(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   200
3

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:

Plan

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:

Filter Predicate

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.

1

Remove the 4 left joins. You are not using their data and they are not needed. It slows everything down, especially if you don't have proper PK and indexes (tell us more)

parameter_table.id should be a PK and/or have 1 or 2 indexes on it such as (it really depends on your model):

create index idx_parameter_table_id0 on parameter_table(id) where col1 = 0
create index idx_parameter_table_id1 on parameter_table(id) where col1 = 1

or

create index idx_parameter_table_id1 on parameter_table(col1, id)

If col2 comes from parameter_table, it may be usefull to include it in the index:

create index ... on parameter_table(...) include (col2)
  • I've modified the SQL. As the left joins is necessary, they can't be removed. I'm agree that the SQL in my application was bad and with bad DB structure (historical issue), hence, add index cannot help due to UNION ALL and grouping in subquery and VIEW. That's why i wonder any shortcut to gain performance in SQL as asked so that it save half of the query time. – Martin Aug 7 '15 at 7:46
  • 1
    Union All is not the problem here. Each independent query is already slow with or without it. You first need to fix each individual query. It is difficult to help you if you don't want to give us enough information: usefull info on the model, the column being joined and existing indexes or PK. I don't blame you because the modele is bad (I didn't say it is bad). I just need to know it better. – Julien Vavasseur Aug 7 '15 at 7:55
  • Note: parameter_table only have 1 row – paparazzo Jan 18 '16 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.