0

We are in process of creating new non-clustered index for specific views. One of the view looks like this:

SELECT * FROM [dbo].[table_backup]
UNION ALL
(
SELECT * FROM [dbo].[tableA] a
LEFT JOIN
[dbo].[tableB] b on
a.column1PK = b.column1PK and
a.column2PK = b.column2PK and
a.column3PK = b.column3PK and
a.column4PK = b.column4PK
WHERE
 a.column_5= 'D'
    AND a.column_6 <> ''
    AND a.column3PK = b.column3PK
    AND a.column_7 <> 'D'
    AND a.column_7 <> 'M'
)

I already prepared non-cluster filtered index for tableA:

CREATE NONCLUSTERED INDEX [IX_column_6_INCLUDE]
ON [sap].[BSEG]
(
    column_6 ASC,
)
INCLUDE
(
    column_5
    column_7  
)
WHERE column_6 <> ''
      AND 
       a.column_5 in ('D','K')
          AND column_7  <> 'D'
              AND column_7 <> 'M';

I add also value 'K' for column_5 because in filter because we have similar second view which contains same table but with filter on column_5 equals to 'K'.

Below are execution plans:

Without index: https://www.brentozar.com/pastetheplan/?id=rJwAlxDAF

Sum of logical reads on all tables = 49 189 CPU time = 971 ms Elapsed time = 489 ms

With index: https://www.brentozar.com/pastetheplan/?id=SJzSB7dAF

Sum of logical reads on all tables = 127 932 CPU time = 2109 ms Elapsed time = 1010 ms

Query which I run for test performance:

SELECT TOP (10000) * INTO #temp FROM [dbo].[view]

Do you know why adding index cause performance decrease ?

1
  • 1
    Why you want to select *, Please use selective columns and see. select * seems to return so many columns, Please check your cardinality estimate issue also. Feb 2, 2022 at 17:50

1 Answer 1

3

Context

Without knowing the full definition of both tables, here’s what I can see.

  1. Your faster plan is doing a single clustered index scan of Object6 and returning 160k rows.

  2. Your slower plan is doing a nonclustered index scan on Object6, reading 399k rows and only retuning 57k of them. Then it’s following that up with 24,570 key lookups.

  3. Based on their name, I assume columns column1PK - column4PK are Primary Keys on tableA. As such, don't need to be part of any index you create.

Things you can try

  1. Add column_5 as the Key Column, instead of column_6. That is the only column that is compared differently in your query compared to the filtered index. So filter that column first.

  2. Consider removing SELECT *, if possible, and select only the columns you need. SELECT * is a bad practice, and will almost always produce a Key Lookup.

Below would be a good index candidate.

CREATE NONCLUSTERED INDEX [IX_column_5_includes]
ON [sap].[BSEG]
(
    column_5 ASC
)
INCLUDE
(
    column_6,
    column_7
)
WHERE column_6 <> ''
    AND column_5 IN ('D','K')
    AND column_7 NOT IN ('D','M');

Additionally, make this changes to your query. For starters, a.column3PK = b.column3PK is in your JOIN and in your WHERE clause.

SELECT [col1], [col2], [colN]
FROM [dbo].[table_backup]

UNION ALL

SELECT [col1], [col2], [colN]
FROM [dbo].[tableA] a
LEFT JOIN [dbo].[tableB] b
    ON a.column1PK = b.column1PK
        AND a.column2PK = b.column2PK
        AND a.column3PK = b.column3PK
        AND a.column4PK = b.column4PK
WHERE a.column_5 = 'D'
    AND a.column_6 <> ''
    AND a.column_7 NOT IN ('D','M');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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