Tune query performance

Transact-SQL
1

declare @a table(col1 int)
insert into @a select
1 union all select
2

declare @b table(col1 int, col2 int, must_come_out varchar(10))
insert into @b select
1,1,'abc' union all select
1,2,'def' union all select
1,3,'ghi'

declare @c table(col1 int, col2 int, must_come_out varchar(10))
insert into @c select
1,3,'jkl' union all select
1,4,'mno' union all select
2,1,'pqr' union all select
2,2,'stu'

declare @d table(col1 int, col2 int, must_come_out varchar(10))
insert into @d select
1,2,'vwx' union all select
1,3,'xyz' union all select
1,4,'fhs' union all select
2,1,'wus'

;WITH CTE AS(
SELECT DISTINCT col1, col2
FROM (
SELECT col1, col2
FROM @b
UNION ALL
SELECT col1, col2
FROM @c
UNION ALL
SELECT col1, col2
FROM @d
)Src
)
SELECT *
FROM @a a
LEFT JOIN CTE cte
ON a.col1 = cte.col1
LEFT JOIN @b b
ON cte.col1 = b.col1
AND cte.col2 = b.col2
LEFT JOIN @c c
ON cte.col1 = c.col1
AND cte.col2 = c.col2
LEFT JOIN @d d
ON cte.col1 = d.col1
AND cte.col2 = d.col2

2

@Kristen format sql

2 Likes
3

ok it does not work

4

LOL. First time i see someone activated the auto-format-kristen-way feature in the forum

5

Me too!!!!

@waterduck Looks like you pressed the ["] button rather than the [</>] button next door to it?

["] button:

declare @a table(col1 int)
insert into @a select
1 union all select
2

[</>] button:

declare @a table(col1 int)
insert into @a select 
1 union all select
2
1 Like
6 
declare @a table(col1 int PRIMARY KEY)
insert into @a select
1 union  select
2

declare @b table(col1 int, col2 int, must_come_out varchar(10) UNIQUE CLUSTERED(col1,col2))
insert into @b select
1,1,'abc' union  select
1,2,'def' union  select
1,3,'ghi'

declare @c table(col1 int, col2 int, must_come_out varchar(10) UNIQUE CLUSTERED(col1,col2))
insert into @c select
1,3,'jkl' union  select
1,4,'mno' union  select
2,1,'pqr' union  select
2,2,'stu'

declare @d table(col1 int, col2 int, must_come_out varchar(10) UNIQUE CLUSTERED(col1,col2))
insert into @d select
1,2,'vwx' union  select
1,3,'xyz' union  select
1,4,'fhs' union  select
2,1,'wus'

;WITH CTE AS(
            SELECT col1, col2
            FROM (
                    SELECT col1, col2
                    FROM @b
                    UNION 
                    SELECT col1, col2
                    FROM @c
                    UNION 
                    SELECT col1, col2
                    FROM @d
                    )Src
            )

SELECT *
FROM @a a
LEFT JOIN CTE cte
ON a.col1 = cte.col1
LEFT JOIN @b b
ON cte.col1 = b.col1
AND cte.col2 = b.col2
LEFT JOIN @c c
ON cte.col1 = c.col1
AND cte.col2 = c.col2
LEFT JOIN @d d
ON cte.col1 = d.col1
AND cte.col2 = d.col2``
1 Like
7 
SELECT a.col1, a.col1,
    COALESCE(col2_b, col2_c, col2_d) AS col2,
    col1_b, col2_b, b_must_come_out,
    col1_c, col2_c, c_must_come_out,
    col1_d, col2_d, d_must_come_out
FROM @a a
OUTER APPLY (
    SELECT
        b.col1 AS col1_b, b.col2 AS col2_b,
        b.must_come_out AS b_must_come_out,
        c.col1 AS col1_c, c.col2 AS col2_c,
        c.must_come_out AS c_must_come_out,
        d.col1 AS col1_d, d.col2 AS col2_d,
        d.must_come_out AS d_must_come_out
    FROM @b b
    FULL OUTER JOIN @c c ON c.col1 = b.col1 AND c.col2 = b.col2
    FULL OUTER JOIN @d d ON d.col1 = COALESCE(b.col1, c.col1) AND d.col2 = COALESCE(b.col2, c.col2)
    WHERE
        a.col1 = COALESCE(b.col1, c.col1, d.col1)
) AS oa1
1 Like
8

Or this. Not sure which of my two queries will perform better on a large data set.

SELECT a.col1, a.col1,
    COALESCE(col2_b, col2_c, col2_d) AS col2,
    col1_b, col2_b, b_must_come_out,
    col1_c, col2_c, c_must_come_out,
    col1_d, col2_d, d_must_come_out
FROM @a a
LEFT OUTER JOIN (
    SELECT
        b.col1 AS col1_b, b.col2 AS col2_b,
        b.must_come_out AS b_must_come_out,
        c.col1 AS col1_c, c.col2 AS col2_c,
        c.must_come_out AS c_must_come_out,
        d.col1 AS col1_d, d.col2 AS col2_d,
        d.must_come_out AS d_must_come_out
    FROM @b b
    FULL OUTER JOIN @c c ON c.col1 = b.col1 AND c.col2 = b.col2
    FULL OUTER JOIN @d d ON d.col1 = COALESCE(b.col1, c.col1) AND d.col2 = COALESCE(b.col2, c.col2)
) AS lj1 ON a.col1 = COALESCE(col1_b, col1_c, col1_d)
1 Like
9

is there any plan to bring ssms coloring to here? may ask mladen =X

10

thx scott and vig, will try and compare the performance

11

I find the SQL parsing to be variable here ... there are a couple of options

    [</>] button - adds 4-spaces to every line
             (which don't display but make it annoying to re-edit)
    ``` (at start and end). Code block, auto-sense Language
    ```sql (at start, just ``` at end) force SQL parsing, note "sql" is case sensitive)
    ```text - plain text
    [code] ... [/code] same code auto-sensing I think, but leaves out ALL blank lines

No formatting (e.g. highlight / bold / colour coding) is possible in Code Blocks, so no ability to draw O/Ps attention to a subtle change/aspect

    ``` test:
SELECT a.col1, a.col1,
    COALESCE(col2_b, col2_c, col2_d) AS col2,
    col1_b, col2_b, b_must_come_out,
    col1_c, col2_c, c_must_come_out,
    col1_d, col2_d, d_must_come_out
FROM @a a
LEFT OUTER JOIN (
    SELECT
        b.col1 AS col1_b, b.col2 AS col2_b,
        b.must_come_out AS b_must_come_out,
        c.col1 AS col1_c, c.col2 AS col2_c,
        c.must_come_out AS c_must_come_out,
        d.col1 AS col1_d, d.col2 AS col2_d,
        d.must_come_out AS d_must_come_out
    FROM @b b
    FULL OUTER JOIN @c c ON c.col1 = b.col1 AND c.col2 = b.col2
    FULL OUTER JOIN @d d ON d.col1 = COALESCE(b.col1, c.col1) AND d.col2 = COALESCE(b.col2, c.col2)
) AS lj1 ON a.col1 = COALESCE(col1_b, col1_c, col1_d)

    ```sql Test
SELECT a.col1, a.col1,
    COALESCE(col2_b, col2_c, col2_d) AS col2,
    col1_b, col2_b, b_must_come_out,
    col1_c, col2_c, c_must_come_out,
    col1_d, col2_d, d_must_come_out
FROM @a a
LEFT OUTER JOIN (
    SELECT
        b.col1 AS col1_b, b.col2 AS col2_b,
        b.must_come_out AS b_must_come_out,
        c.col1 AS col1_c, c.col2 AS col2_c,
        c.must_come_out AS c_must_come_out,
        d.col1 AS col1_d, d.col2 AS col2_d,
        d.must_come_out AS d_must_come_out
    FROM @b b
    FULL OUTER JOIN @c c ON c.col1 = b.col1 AND c.col2 = b.col2
    FULL OUTER JOIN @d d ON d.col1 = COALESCE(b.col1, c.col1) AND d.col2 = COALESCE(b.col2, c.col2)
) AS lj1 ON a.col1 = COALESCE(col1_b, col1_c, col1_d)

I can't see any difference, but I could have sworn that ```sql colour-coded ...

EDIT: Agghhhh ... markDown has swallowed a couple of ' characters

"I could have sworn that ```sql colour-coded"

1 Like
12

hey sir, it's saturday! get back to the real world and play Brain dots