Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2016
    Posts
    11

    Unanswered: T-SQL Change a Slow Query using - NOT IN ()

    Hi,

    I have a problem with this bit of code running very slow and I believe the problem is with the NOT IN,
    is it possible to change the NOT IN to something quickier like a LEFT OUTER JOIN and where Variant IS NULL etc.

    Just searching the net now to try somethings but want to know if it's possible or I'm on a wild goose chase.

    Thanks,
    Roger



    SELECT DISTINCT
    s.BI_CompanyID AS CompanyID
    ,s.[Sales Code]
    ,s.[Price Type]
    ,s.[Starting Date]
    ,s.[Ending Date]
    ,COALESCE (i.[Item No_], i2.[Item No_], s.[Item No_]) AS [Item No_]
    ,COALESCE (i.[Variant Code], i2.[Variant Code], s.[Variant Code]) AS [Variant Code]
    ,s.[Unit Price Including VAT]
    ,s.[Unit Price] AS [Unit Price Excluding VAT]
    ,s.[Sales Code] + ' - ' + s.[Price Type] + ' - ' + CAST(s.[Starting Date] AS varchar(50)) AS PriceDesc

    FROM BI_SalesPrice AS s

    INNER JOIN vItem_v2 AS i
    ON s.BI_CompanyID = i.CompanyID
    AND s.[Item No_] = i.[Item No_]
    AND s.[Variant Code] = i.[Variant Code]

    LEFT OUTER JOIN vItem_v2 AS i2
    ON s.BI_CompanyID = i2.CompanyID
    AND s.[Item No_] = i2.[Item No_]
    AND s.[Variant Code] = ''
    AND i2.[Variant Code] NOT IN (
    SELECT DISTINCT i.[Variant Code] FROM BI_SalesPrice AS x
    WHERE (i.CompanyID = i2.CompanyID)
    AND (i.[Item No_] = i2.[Item No_])
    AND (i.[Variant Code] = i2.[Variant Code])
    AND ([Sales Code] = s.[Sales Code])
    )


    WHERE (COALESCE (i.[Variant Code], i2.[Variant Code], s.[Variant Code]) IS NOT NULL)
    AND (COALESCE (i.[Item No_], i2.[Item No_], s.[Variant Code]) IS NOT NULL)
    AND (s.[Price Type] <> '20PCT OFF')
    AND (s.[Unit Price] <> 0)

  2. #2
    Join Date
    Oct 2016
    Posts
    11
    Think I have worked out a way around it, seems to run OK and return OK results not sure if best way to do this...

    SELECT DISTINCT -- Get Prices that have a Variant Code listed
    s.BI_CompanyID AS CompanyID
    ,s.[Sales Code]
    ,s.[Price Type]
    ,s.[Starting Date]
    ,s.[Ending Date]
    ,s.[Item No_]
    ,s.[Variant Code]
    ,s.[Unit Price Including VAT]
    ,s.[Unit Price] AS [Unit Price Excluding VAT]
    ,s.[Sales Code] + ' - ' + s.[Price Type] + ' - ' + CAST(s.[Starting Date] AS varchar(50)) AS PriceDesc

    FROM BI_SalesPrice AS s

    INNER JOIN vItem_v2 AS i
    ON s.BI_CompanyID = i.CompanyID
    AND s.[Item No_] = i.[Item No_]
    AND s.[Variant Code] = i.[Variant Code]

    WHERE (s.[Variant Code] <> '')
    AND (s.[Price Type] <> '20PCT OFF')
    AND (s.[Unit Price] <> 0)

    UNION

    SELECT DISTINCT -- Get Prices for All Variants Code but then ignore variants from above query
    s.BI_CompanyID AS CompanyID
    ,s.[Sales Code]
    ,s.[Price Type]
    ,s.[Starting Date]
    ,s.[Ending Date]
    ,s.[Item No_]
    ,i.[Variant Code]
    ,s.[Unit Price Including VAT]
    ,s.[Unit Price] AS [Unit Price Excluding VAT]
    ,s.[Sales Code] + ' - ' + s.[Price Type] + ' - ' + CAST(s.[Starting Date] AS varchar(50)) AS PriceDesc

    FROM BI_SalesPrice AS s

    INNER JOIN vItem_v2 AS i
    ON s.BI_CompanyID = i.CompanyID
    AND s.[Item No_] = i.[Item No_]

    LEFT OUTER JOIN (
    SELECT DISTINCT -- not interested in [Price Type]
    s2.BI_CompanyID
    ,s2.[Sales Code]
    ,s2.[Starting Date]
    ,s2.[Ending Date]
    ,s2.[Item No_]
    ,s2.[Variant Code]

    FROM BI_SalesPrice AS s2

    INNER JOIN vItem_v2 AS i2
    ON s2.BI_CompanyID = i2.CompanyID
    AND s2.[Item No_] = i2.[Item No_]
    AND s2.[Variant Code] = i2.[Variant Code]

    WHERE (s2.[Variant Code] <> '')
    AND (s2.[Price Type] <> '20PCT OFF')
    AND (s2.[Unit Price] <> 0)
    ) as x

    ON s.BI_CompanyID = x.BI_CompanyID
    AND s.[Sales Code] = x.[Sales Code]
    AND s.[Starting Date] = x.[Starting Date]
    AND s.[Ending Date] = x.[Ending Date]
    AND s.[Item No_] = x.[Item No_]
    AND i.[Variant Code] = x.[Variant Code]

    WHERE (s.[Variant Code] = '')
    AND (s.[Price Type] <> '20PCT OFF')
    AND (s.[Unit Price] <> 0)
    AND x.[Variant Code] IS NULL

    ORDER BY 4,6,7

  3. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    since its already a correlated subquery, try using NOT EXISTS and get rid of the DISTINCT in the select clause.

    Code:
    AND i2.[Variant Code] NOT exists (
     SELECT 1 FROM BI_SalesPrice AS x
     WHERE (i.CompanyID = i2.CompanyID)
     AND (i.[Item No_] = i2.[Item No_]) 
     AND (i.[Variant Code] = i2.[Variant Code]) 
     AND ([Sales Code] = s.[Sales Code])
     )

  4. #4
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Also, I didn't notice until after reviewing the above. Where is the join to this X table? "BI_SalesPrice AS x"

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •