SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow Query Issue...


Slow Query Issue...

Author
Message
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 599
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.
Attachments
eplan.txt (27 views, 56.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86212 Visits: 45229
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12238 Visits: 8541
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
Greg Edwards-268690
Greg Edwards-268690
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 Visits: 8481
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.
Chris Wooding
Chris Wooding
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 989
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86212 Visits: 45229
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search