Best Practice - how to exclude records?

  • Hello All,

    I have a requirement to extract records of invoice details for a specific product type, but ONLY from invoices, where no OTHER type was included.

    To demonstrate: I need to present all the invoice details records of product type A, but will ignore the invoices with other product types, e.g. A and B.

    The query I use is extremely simple, but I am not sure it is a best practice:

    1) I query for all the records of product type 'A'

    2) I add NOT EXISTS to my where clause where product type != 'A'

    Below is correct, working example, which only brings me invoice 1000 as expected,

    but can one advise if there is a better way, please? Reason for raising this question, is that the table I query is of 13M records and 20GB size.

    Much obliged

    IF OBJECT_ID('tempdb..##myTable') IS NOT NULL

    DROP TABLE ##myTable;

    GO

    CREATE TABLE ##myTable(

    invoice_id NUMERIC(10) DEFAULT 0 NOT NULL,

    invoice_line NUMERIC(10) DEFAULT 0 NOT NULL,

    product_type CHAR(1) NOT NULL);

    GO

    ALTER TABLE ##myTable

    ADD CONSTRAINT myTable_pk PRIMARY KEY (invoice_id, invoice_line);

    WITH myRecords AS (

    SELECT 1000 AS invoice_id,

    1 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1000 AS invoice_id,

    2 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1001 AS invoice_id,

    1 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1001 AS invoice_id,

    2 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1001 AS invoice_id,

    3 AS invoice_line,

    'B' AS product_type

    UNION ALL

    SELECT 1002 AS invoice_id,

    1 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1002 AS invoice_id,

    2 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1002 AS invoice_id,

    3 AS invoice_line,

    'A' AS product_type

    UNION ALL

    SELECT 1002 AS invoice_id,

    4 AS invoice_line,

    'E' AS product_type

    )

    INSERT INTO ##myTable

    SELECT * FROM myRecords;

    SELECT *

    FROM ##myTable m

    WHERE m.product_type = 'A'

    AND NOT EXISTS (SELECT 1

    FROM ##myTable t

    WHERE t.invoice_id = m.invoice_id

    AND t.product_type !='A' );

  • Is there a positive reason why you are using global temp tables?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • yes - for a quick mock-up to present the requirement and my solution.

  • Here is a good article by her imminence.

    http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

    I would say that it looks fine to me, but I have had an issue once before where the not exists was running for 8 hours and changing to a left join in that specific case reduced the time down to 16 seconds on 100GB tables.

    So I would favour left join tableX WHERE tableX.ID is null, but that is my personal preference.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • Thank you for the article - will read it now.

  • If you need to know if all the lines have only product_type = 'A' on any kind of regular basis, I'd denormalize and add a flag to the invoice header, maintained by a trigger.

    What % of the rows in the table will be part of invoices with only type 'A'? If it's a small number, try something like this and see how well it performs. Hopefully you have a nonclustered index that contains the inovice_id and the invoice_type, if not, you should try creating one:

    SELECT il.*

    FROM invoice_lines il

    INNER JOIN (

    SELECT invoice_id

    FROM invoice_lines

    GROUP BY invoice_id

    HAVING MIN(product_type) = 'A' AND MAX(product_type) = 'A'

    ) AS invoices_a_only ON

    invoices_a_only.invoice_id = il.invoice_id

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply