May 17, 2017 at 5:23 pm
I wrote a query that calculates, for each item on an invoice, how many other items are on the same invoice (cntOtherItemsOnInvoice) and also, if its the only item on said invoice (OnlyOneItemOnInvoice).
This is a simplified version of the query:
select <tbl1>.<field1> /* invoice number */
, <tbl1>.<field2> /* item number */
, (select count(*)
from <table1> as tbl2 /* invoice table */
where tbl2.<field1> = tbl1.<field1> /* invoice number */
and tbl2.<field2> <> tbl1.<field2> /* item number */
) as cntOtherItemsOnInvoice
, (select count(*)
from <table1> as tbl3 /* invoice table */
having tbl3.<field1> = tbl1.<field1> /* invoice number */
and count(tbl3.<field2> = 1 /* item number */
) as onlyOneItemOnInvoice
from <table1> as tbl1 /* invoice table */
The underlying table has over 20 millions rows and the query is run based on the last 12 months (i didn't put the date parameter because its not where the bottleneck is occurring) and its aggregating the results per month and item.
Now, this is to be used in a stored proc run from an excel workbook, which has many other datasource, also querying the database for different result-sets. But this one in particular takes between 3 to 4 minutes and I would like to make it faster.
There has to be a faster way to get to the same result.
Any ideas?
Thanks
May 17, 2017 at 6:13 pm
emenard 46273 - Wednesday, May 17, 2017 5:23 PMI wrote a query that calculates, for each item on an invoice, how many other items are on the same invoice (cntOtherItemsOnInvoice) and also, if its the only item on said invoice (OnlyOneItemOnInvoice).This is a simplified version of the query:
select <tbl1>.<field1> /* invoice number */
, <tbl1>.<field2> /* item number */
, (select count(*)from <table1> as tbl2 /* invoice table */
where tbl2.<field1> = tbl1.<field1> /* invoice number */
and tbl2.<field2> <> tbl1.<field2> /* item number */
) as cntOtherItemsOnInvoice
, (select count(*)
from <table1> as tbl3 /* invoice table */
having tbl3.<field1> = tbl1.<field1> /* invoice number */
and count(tbl3.<field2> = 1 /* item number */
) as onlyOneItemOnInvoice
from <table1> as tbl1 /* invoice table */
The underlying table has over 20 millions rows and the query is run based on the last 12 months (i didn't put the date parameter because its not where the bottleneck is occurring) and its aggregating the results per month and item.
Now, this is to be used in a stored proc run from an excel workbook, which has many other datasource, also querying the database for different result-sets. But this one in particular takes between 3 to 4 minutes and I would like to make it faster.
There has to be a faster way to get to the same result.
Any ideas?
Thanks
Can you post the DDL for the tables involved, sample data and expected output. I think I see what you're asking for, but expected output would remove all doubt.
Also, you want the number of items per invoice. Do you want it for just the specified invoice or for all invoices?
May 18, 2017 at 5:30 am
Ed Wagner - Wednesday, May 17, 2017 6:13 PMCan you post the DDL for the tables involved, sample data and expected output. I think I see what you're asking for, but expected output would remove all doubt.
Also, you want the number of items per invoice. Do you want it for just the specified invoice or for all invoices?
There you go (oh that's what those formatting options are for ;):
create table dbo.invoice(
invoicenumber varchar(6) not null,
itemnumber varchar(5) not null
) on [primary]
insert into dbo.orders
values ('000001', '10000'), ('000002', '10000'),('000002','10001'),('000002', '10003'), ('000003','10002'), ('000003','10004')
/* expected output:
order number itemnumber cntOtherItemsOnInvoice onlyOneItemOnInvoice
000001 10000 0 1
000002 10000 2 0
000002 10001 2 0
000002 10003 2 0
000003 10002 1 0
000003 10004 1 0
May 18, 2017 at 6:06 am
emenard 46273 - Thursday, May 18, 2017 5:30 AMEd Wagner - Wednesday, May 17, 2017 6:13 PMCan you post the DDL for the tables involved, sample data and expected output. I think I see what you're asking for, but expected output would remove all doubt.
Also, you want the number of items per invoice. Do you want it for just the specified invoice or for all invoices?There you go (oh that's what those formatting options are for ;):
create table dbo.invoice(
invoicenumber varchar(6) not null,
itemnumber varchar(5) not null
) on [primary]insert into dbo.orders
values ('000001', '10000'), ('000002', '10000'),('000002','10001'),('000002', '10003'), ('000003','10002'), ('000003','10004')/* expected output:
order number itemnumber cntOtherItemsOnInvoice onlyOneItemOnInvoice
000001 10000 0 1
000002 10000 2 0
000002 10001 2 0
000002 10003 2 0
000003 10002 1 0
000003 10004 1 0
I should have noticed that this is one of your first posts. Welcome to SSC.
Will this work for you?
WITH cte AS (
SELECT InvoiceNumber, ItemNumber, RC = COUNT(*) OVER(PARTITION BY InvoiceNumber ORDER BY (SELECT NULL))
FROM dbo.Invoice
)
SELECT InvoiceNumber, ItemNumber, OtherItems = RC - 1, IsOnlyItem = CASE WHEN RC = 1 THEN 1 ELSE 0 END
FROM cte
ORDER BY InvoiceNumber, ItemNumber;
May 19, 2017 at 10:42 am
Is it possible to have more than one of the same item in a given invoice? And if so, does that count as 1 item or more than 1?
May 23, 2017 at 12:08 pm
Ed Wagner - Thursday, May 18, 2017 6:06 AMemenard 46273 - Thursday, May 18, 2017 5:30 AMEd Wagner - Wednesday, May 17, 2017 6:13 PMCan you post the DDL for the tables involved, sample data and expected output. I think I see what you're asking for, but expected output would remove all doubt.
Also, you want the number of items per invoice. Do you want it for just the specified invoice or for all invoices?There you go (oh that's what those formatting options are for ;):
create table dbo.invoice(
invoicenumber varchar(6) not null,
itemnumber varchar(5) not null
) on [primary]insert into dbo.orders
values ('000001', '10000'), ('000002', '10000'),('000002','10001'),('000002', '10003'), ('000003','10002'), ('000003','10004')/* expected output:
order number itemnumber cntOtherItemsOnInvoice onlyOneItemOnInvoice
000001 10000 0 1
000002 10000 2 0
000002 10001 2 0
000002 10003 2 0
000003 10002 1 0
000003 10004 1 0I should have noticed that this is one of your first posts. Welcome to SSC.
Will this work for you?
WITH cte AS (
SELECT InvoiceNumber, ItemNumber, RC = COUNT(*) OVER(PARTITION BY InvoiceNumber ORDER BY (SELECT NULL))
FROM dbo.Invoice
)
SELECT InvoiceNumber, ItemNumber, OtherItems = RC - 1, IsOnlyItem = CASE WHEN RC = 1 THEN 1 ELSE 0 END
FROM cte
ORDER BY InvoiceNumber, ItemNumber;
Worked beautifully. Thank you. Went from 3-4 minutes to 19 secs.
May 23, 2017 at 1:33 pm
emenard 46273 - Tuesday, May 23, 2017 12:08 PMEd Wagner - Thursday, May 18, 2017 6:06 AMemenard 46273 - Thursday, May 18, 2017 5:30 AMEd Wagner - Wednesday, May 17, 2017 6:13 PMCan you post the DDL for the tables involved, sample data and expected output. I think I see what you're asking for, but expected output would remove all doubt.
Also, you want the number of items per invoice. Do you want it for just the specified invoice or for all invoices?There you go (oh that's what those formatting options are for ;):
create table dbo.invoice(
invoicenumber varchar(6) not null,
itemnumber varchar(5) not null
) on [primary]insert into dbo.orders
values ('000001', '10000'), ('000002', '10000'),('000002','10001'),('000002', '10003'), ('000003','10002'), ('000003','10004')/* expected output:
order number itemnumber cntOtherItemsOnInvoice onlyOneItemOnInvoice
000001 10000 0 1
000002 10000 2 0
000002 10001 2 0
000002 10003 2 0
000003 10002 1 0
000003 10004 1 0I should have noticed that this is one of your first posts. Welcome to SSC.
Will this work for you?
WITH cte AS (
SELECT InvoiceNumber, ItemNumber, RC = COUNT(*) OVER(PARTITION BY InvoiceNumber ORDER BY (SELECT NULL))
FROM dbo.Invoice
)
SELECT InvoiceNumber, ItemNumber, OtherItems = RC - 1, IsOnlyItem = CASE WHEN RC = 1 THEN 1 ELSE 0 END
FROM cte
ORDER BY InvoiceNumber, ItemNumber;Worked beautifully. Thank you. Went from 3-4 minutes to 19 secs.
Excellent. I'm glad it worked well for you. Thanks for the feedback.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply