December 14, 2015 at 7:02 am
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' );
December 14, 2015 at 7:17 am
December 14, 2015 at 7:18 am
yes - for a quick mock-up to present the requirement and my solution.
December 14, 2015 at 7:28 am
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.
December 14, 2015 at 7:30 am
Thank you for the article - will read it now.
December 14, 2015 at 9:53 am
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