Reducing table scans using group by and window functions

Question

There is a code that I'm trying to improve that looks like this(simple example):

SELECT    DISTINCT a.col_a
         ,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b = 'blabla1'
LEFT JOIN tab_b b2
          ON a.col_a = b2.col_a
             AND b2.col_b = 'blabla2'
LEFT JOIN tab_b b3
          ON a.col_a = b3.col_a
             AND b3.col_b = 'blabla3';

You can use the following script to recreate those tables

CREATE TABLE tab_a(col_a int)
CREATE TABLE tab_b(col_a INT, col_b VARCHAR(10), col_c INT)

INSERT INTO dbo.tab_a ( col_a ) VALUES ( 1 ), ( 2 ), ( 3 );

INSERT INTO dbo.tab_b ( col_a
                       ,col_b
                       ,col_c )
VALUES ( 1, 'blabla1', 1 )
      ,( 1, 'blabla2', 3 )
      ,( 1, 'blabla2', 5 )
      ,( 2, 'blabla2', NULL )
      ,( 2, 'blabla3', 5 );

How can I change it to 1 join + maybe window function and how to rewrite coalesce part. Just to explain, current plan shows 3 tab_b scans, I want to reduce it to 1.

Answer 1

SELECT    DISTINCT a.col_a
         ,b.col_c
FROM      tab_a a
outer apply (select top 1 b.col_c 
             from tab_b b
             where ((a.col_a = b.col_a
                     AND b.col_b = 'blabla1' )
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla2')
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla3'))
                  and b.col_c is not null
            order by b.col_b)b;

This solution has 1 tab_b scan but adds sort because you want to choose b.col_c as in your COALESCE. In the example above this order corresponds to the order given by your constants in join condition that correspond to values of c column. In case when the order should be different the thing will be more complicated as you should write customized order byclause.

Answer 2

If you want or have that 3 descriptions static, you can do the following:

1. Create a temp table or variable table

2. Insert the values in the temp or variable table but in form of column https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

3. Write the script in your answer but delete those 3 LEFT JOIN and just do a CROSS JOIN to the temp or variable table

4. Change the COALESCE code to get the values from the CROSS JOIN object

You can also change your database model to satisfy what you need most.

To help, please share the current database model so we can help you further. You can create it with the following option:

Answer 3

SELECT    DISTINCT a.col_a
         ,b1.col_c
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b IN ('blabla1','blabla2','blabla3');

1 join as requested ^^ edited to a somewhat more elegant style using IN ( .. )