Query Optimization using 'except'

user_16496624 0
Viewed 78

I've been lately working on some performance optimization and have been a bit stuck with the below query. Breaking it down, the individual steps don't seem to take very long, but when I run the query as a whole, it takes about 30minutes to complete.

The TABLE has around 100k rows, and the VIEW has around 400k rows, so they're not terribly large. I wasn't sure if I'm just not understanding the EXCEPT logic accurately, and if that's the likely culprit? Would there be an alternative to EXCEPT perhaps?

EDIT - The view itself has about 4 joins and a UNION, so it does have some logic to it.

CREATE TABLE [SCHEMA].[TABLE](
    ColumnA [int] IDENTITY(1,1) NOT NULL,
    ColumnB [tinyint] NOT NULL,
    ColumnC [tinyint] NOT NULL,
    ColumnD [int] NULL,
    ColumnE [nvarchar](50) NOT NULL,
    ColumnF [int] NOT NULL,
    ColumnG [nvarchar](250) NULL,
    ColumnH [nvarchar](250) NULL,
    ColumnI [nvarchar](250) NULL,
    ColumnJ [nvarchar](50) NULL,
    columnK [nvarchar](400) NULL,
    ColumnL [nvarchar](2) NULL,
    ColumnM [nvarchar](250) NULL,
    ColumnN [nvarchar](3) NULL,
----
       DELETE FROM [DB].[SCHEMA].[TABLE] WHERE ColumnB NOT IN (4,6) 
        AND  ColumnG not in
      (SELECT ColumnG 
       FROM 
       (
         SELECT ColumnG,ColumH,ColumnI FROM [DB].[SCHEMA].[TABLE] EXCEPT 

         SELECT ColumnG,ColumnH,ColumnI FROM [DB].[SCHEMA].[VIEW]
         WHERE VIEW.ColumnB='Active' and year(LastChgDateTime) = 9999
       ) AAA )

Thanks for any help!

query-optimization tsql sql-server sql
1 Answers

Without knowing your schema and indexing, it's hard to say. We also haven;t seen a query plan. And you haven't provided the view definition, so we don't know what's involved with that.

But for a start, you can simplify this query in the following way

Note the use of a sarge-able predicate on LastChgDateTime

DELETE FROM [DB].[SCHEMA].[TABLE]
WHERE ColumnB NOT IN (4,6) 
  AND NOT EXISTS (
         SELECT ColumnG,ColumH,ColumnI
           FROM [DB].[SCHEMA].[TABLE] AAA
           WHERE AAA.ColumnG = [TABLE].ColumnG
         EXCEPT 
         SELECT ColumnG,ColumnH,ColumnI
           FROM [DB].[SCHEMA].[VIEW]
           WHERE [VIEW].ColumnB = 'Active' and LastChgDateTime >= '9999-01-01'
       );

For the above, the following indexes would make sense

The view would need indexing on the base tables

[TABLE] (ColumnG, ColumH, ColumnI) INCLUDE (ColumnB)

[VIEW] (ColumnB, ColumnG, ColumH, ColumnI, LastChgDateTime)

We can optimize this further by using an updatable CTE with a window function.

I'm not entirely sure the logic you are trying to achieve, but it appears to be something like this.

WITH cte AS (
    SELECT
      t.*,
      IsGNotInView = COUNT(v.IsNotInView) OVER (PARTITION BY t.ColumnG)
    FROM [DB].[SCHEMA].[TABLE] t
    CROSS APPLY (
       SELECT
        CASE WHEN NOT EXISTS (SELECT 1
            FROM [DB].[SCHEMA].[VIEW] v
            WHERE v.ColumnB = 'Active'
              AND v.LastChgDateTime >= '9999-01-01'
              AND v.ColumnG = t.ColumnG
              AND v.ColumnH = t.ColumnH
              AND t.ColumnI = v.ColumnI
            )
        THEN 1 END
    ) v(IsNotInView)
)
DELETE FROM cte
WHERE ColumnB NOT IN (4,6) 
  AND IsGNotInView = 0;
user_148689970
Related