Home » SQL & PL/SQL » SQL & PL/SQL » Sql query performance
Sql query performance [message #212018] Wed, 03 January 2007 08:55 Go to next message
San4381
Messages: 1
Registered: January 2007
Junior Member
Hi all,

Can anyone help in improving the performance of the query bellow? Im joining five tables and was wondering if there is a better way to do it than the way that i have done it. The query has been running for over 16 hours now with no results.

select count(s.service_connection_code), c.criteria_code, c1.criteria_code
from accounts a, sc_accounts sc, service_connections s, account_criteria ac, criteria c, criteria c1
where a.account_id=sc.sca_account_id
and sc.sca_service_connection_id=s.service_connection_id
and a.account_id=ac.acr_account_id
and ac.acr_criteria_code=c.criteria_code
and ac.acr_criteria_code=c1.criteria_code
and acr.effective_to IS NULL
and sc.effective_to IS NULL
and (a.account_number_source='N' or a.account_number_source='U')
and s.ws_date_to is NULL
and (c.criteria_type = 'P and L Segment' or c1.criteria_type = 'Market Code')
group by c.criteria_code, c1.criteria_code
Re: Sql query performance [message #212025 is a reply to message #212018] Wed, 03 January 2007 09:37 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Why is this in SQL Experts?
Sticky for Performance
Provide the execution plan and indexes of the tables.

By
Vamsi
Re: Sql query performance [message #212026 is a reply to message #212018] Wed, 03 January 2007 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Im joining five tables
WHY, when you are SELECTing data from only THREE tables?
Eliminate extra tables out of FROM clause & subordinate into WHERE clause using IN/EXISTS
Re: Sql query performance [message #212038 is a reply to message #212026] Wed, 03 January 2007 10:22 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Anacedent, it looks as if that's not possible, since there is a "straight" line of joins from the count(s.service_connection_code) to the 2 criteria_codes (coming from c and c1).

However, San, it might be faster to split the statement in 2 parts, with a union between the 2, 1 statement getting all records for c.criteria_code = X and 1 statement getting all records for c1.criteria_code = Y. But this is a very wild guess, since we don't know the numbers of rows involved, indexes, explain plan, etc.

Regards,
Sabine

[Updated on: Wed, 03 January 2007 10:24]

Report message to a moderator

Re: Sql query performance [message #212073 is a reply to message #212018] Wed, 03 January 2007 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Skoosman,
>Anacedent, it looks as if that's not possible,
Yes it IS possible.
San4381,
It long running because you have a cartesian product.
IMO, you have no join condition directly between criteria tables and service_connection_code tables.
It would help if table DESCRIPTIONS & EXPLAIN_PLAN were posted.

[Updated on: Wed, 03 January 2007 13:06] by Moderator

Report message to a moderator

Re: Sql query performance [message #212088 is a reply to message #212073] Wed, 03 January 2007 14:22 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi Anacedent,

Well, I would love to see how. Please eleborate me!

From what I gathered from the statement, the structure is:

service_connections (s)
joins to sc_accounts(sc)
joins to accounts (a)
joins to account_criteria (ac)
joins to criteria (c) and criteria (c1)

Where is the cartesian join and how would we move something to in/exist clauses?

Regards,
Sabine
Re: Sql query performance [message #212122 is a reply to message #212088] Wed, 03 January 2007 19:31 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I agree with Skooman. My opinion of the other advice offered has not changed since this post.

But none of that is important. As @vamsi asked, post the EXPLAIN PLAN and indexes.

Also provide:
- the size of each table
- the number of rows matching acr.effective_to IS NULL
- the number of rows matching sc.effective_to IS NULL
- the number of rows matching (a.account_number_source='N' or a.account_number_source='U')
- the number of rows matching s.ws_date_to is NULL
- the number of rows matching (c.criteria_type = 'P and L Segment' or c1.criteria_type = 'Market Code')
- the number of rows returned if you remove the COUNT and GROUP BY (ie. number of rows before aggregation)

Ross Leishman
Previous Topic: pass a value through a query
Next Topic: Union error
Goto Forum:
  


Current Time: Sun Dec 04 20:52:36 CST 2016

Total time taken to generate the page: 0.21680 seconds