3

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.

Improve this question
  • What is your current issue?What are you trying to achieve? Please post DDL of underlying tables and also plan on brentozar.com/pastetheplan – Learning_DBAdmin Nov 14 '19 at 8:24
  • 1
    If you'll read the query you'll see that it has to scan/seek tab_b 3 times, I want to reduce it to 1 time :-) My actual query is more complicated I just want to understand the idea how to replace 3 joins witn 1 and deal with coalesce part – Michael Cherevko Nov 14 '19 at 8:29
5
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.

Improve this answer
  • Looks great! The thing is that there are couple columns that I need to return. In this case I need to run outer apply for each column(since the conditions are different). Is there a way to group by result set and use some kind of window function on result set to find – Michael Cherevko Nov 14 '19 at 12:13
  • >>>The thing is that there are couple columns that I need to return<<< Yesy, one outer apply for every column because not null values of two columns can be situated in different rows – sepupic Nov 14 '19 at 12:20
  • 2
    @MichaelCherevko You could post a different question with the extra requirements and a bigger example and expected result – Randi Vertongen Nov 14 '19 at 12:32
  • Out of interest, why did you choose to write the WHERE filter like that instead of the more compact WHERE a.col_a = b.col_a AND b.col_b IN ('blabla1', 'blabla2', 'blabla3') AND b.col_c IS NOT NULL? – Andriy M Nov 15 '19 at 15:21
  • @Andriy M I just copied original filters and pasted them in WHERE clause :))) – sepupic Nov 15 '19 at 15:53
1

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:

enter image description here

Improve this answer
  • Don't sure that I understand how you method will reduce table scan... Added simple tables and insert, can you write the query using those tables? – Michael Cherevko Nov 14 '19 at 8:51
  • Hi. Yes. I’ll write the query as soon as I arrive to the office. – pncsoares Nov 14 '19 at 8:52
  • But have in mind that I can only verify that the number of table scans reduce if you share the database model – pncsoares Nov 14 '19 at 9:03
  • What do you mean by "database model"? I'm using 2016 if that is what you asking. – Michael Cherevko Nov 14 '19 at 9:46
  • 1
    think he wants more info about the tables and relations as you clearly say the example tables are a simplified version of your real database – eagle275 Nov 14 '19 at 10:01
0
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 ( .. )

Improve this answer
  • 1
    Check the result of query and compare it from what is required. Moreover table alias name is also incorrect. There is no b2 or b3. – Learning_DBAdmin Nov 14 '19 at 9:33
  • Yea -- edited that ... - mind adding that I hate comma-separated values in a single column .. Don't we normalize to reduce that .... – eagle275 Nov 14 '19 at 9:35
  • Did you check the result set? This is not what I want... – Michael Cherevko Nov 14 '19 at 9:45
  • I know you want one data-set with denormalized columns from 3 data-sets in table b ... go by pncsoares solution with a view or temp table containing that. – eagle275 Nov 14 '19 at 9:59

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.