Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow Query Issue... Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 6:46 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
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.


  Post Attachments 
eplan.txt (14 views, 56.58 KB)
Post #1519606
Posted Wednesday, December 4, 2013 6:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
Table definitions and index definitions please?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1519607
Posted Wednesday, December 4, 2013 12:00 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 4,436, Visits: 6,338
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 at GMail
Post #1519750
Posted Thursday, December 5, 2013 7:05 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 675, Visits: 6,810
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.
Post #1520048
Posted Friday, December 6, 2013 4:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:50 AM
Points: 79, Visits: 267
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
Post #1520510
Posted Friday, December 6, 2013 4:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
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 2008, MVP
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

Post #1520517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse