December 4, 2013 at 6:46 am
Hi All,
I'm posting a query that is troubling me. The query is taking 3-4 mins and not exists part is responsible for the slowness.
Do we have some way to rewrite some parts or all of the query?
select distinct top 100
i.invoice_id, i.buyer_code, i.submit_time, i.request_id
from
dbo.invoice i inner join dbo.invoice_adj ia on i.invoice_id = ia.invoice_id and ia.amount !=0
where
(i.detail_amount != 0 or i.tax_amount!=0)
and not exists (select 1
from dbo.invoice_detail iad
where i.invoice_id = iad.invoice_id)
and not exists (select 1
from dbo.unprocess_invoice_detail uiad
where i.invoice_id = uiad.invoice_id)
order by i.submit_time desc
Here is the IO stats and Query plan is attached for your review.
Table 'unprocess_invoice_detail'. Scan count 0, logical reads 94, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'invoice_detail'. Scan count 3021367, logical reads 19364105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'invoice'. Scan count 1, logical reads 1485831, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'invoice_adj'. Scan count 1, logical reads 181283, physical reads 0, read-ahead reads 75, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Thanks,
Ashish.
December 4, 2013 at 6:48 am
Table definitions and index definitions please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2013 at 12:00 pm
select distinct top 100
i.invoice_id, i.buyer_code, i.submit_time, i.request_id
from
dbo.invoice i inner join dbo.invoice_adj ia on i.invoice_id = ia.invoice_id and ia.amount !=0
where
(i.detail_amount != 0 or i.tax_amount!=0)
and not exists (select 1
from dbo.invoice_detail iad
where i.invoice_id = iad.invoice_id)
and not exists (select 1
from dbo.unprocess_invoice_detail uiad
where i.invoice_id = uiad.invoice_id)
order by i.submit_time desc
I would also like to see the total row counts for each table as well as the following counts:
1) invoice rows where either detailamount or taxamount are non-zero
2) invoicedetails that do not have any unprocessinvoicedetail rows, but only for the invoicedetails rows where invoices match your non-zero clause
Given certain data volumes and data value distributions it is quite possible that the most (and possibly ONLY) efficient way to answer this query is to have a trigger that maintains a table with row keys that match the conditionals involved. With proper indexes in place that trigger should be extremely efficient.
Of course there are likely other data volumes and data value distributions where (filtered?) indexing could make performance acceptable.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 5, 2013 at 7:05 am
I agree more table definition would be helpful.
And the counts in the tables would also be nice to know.
It appears you are looking for adjustment records that have no adjustment detail record and no unprocessed adjustment detail record.
You might want to give a try at just this subset first and see how that performs.
I would expect that there should be relatively few inv adjustment records per inv record in most systems.
The trigger idea is one way, although I might look a bit at overall table and relations too.
A table for unprocessed is something I might just handle with a status if you have an option.
Then views can be very helpful.
December 6, 2013 at 4:34 am
I would try;
select distinct top 100
i.invoice_id, i.buyer_code, i.submit_time, i.request_id
from
dbo.invoice i
inner join dbo.invoice_adj ia on i.invoice_id = ia.invoice_id and ia.amount !=0
left outer join dbo.invoice_detail iad on i.invoice_id = iad.invoice_id
left outer join dbo.unprocess_invoice_detail uiad on i.invoice_id = uiad.invoice_id
where
(i.detail_amount != 0 or i.tax_amount!=0)
and iad.invoice_id is null
and uiad.invoice_id is null
order by i.submit_time desc
December 6, 2013 at 4:59 am
cw18 (12/6/2013)
I would try;select distinct top 100
i.invoice_id, i.buyer_code, i.submit_time, i.request_id
from
dbo.invoice i
inner join dbo.invoice_adj ia on i.invoice_id = ia.invoice_id and ia.amount !=0
left outer join dbo.invoice_detail iad on i.invoice_id = iad.invoice_id
left outer join dbo.unprocess_invoice_detail uiad on i.invoice_id = uiad.invoice_id
where
(i.detail_amount != 0 or i.tax_amount!=0)
and iad.invoice_id is null
and uiad.invoice_id is null
order by i.submit_time desc
Left outer join ... is null is typically slightly slower than NOT EXISTS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply