Home » SQL & PL/SQL » SQL & PL/SQL » query optimization (oracle 10g)
query optimization [message #410575] Mon, 29 June 2009 01:37 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,

This query takes much time to execute around 10 minutes.
Please help to optmize the query.
I cannot put structure because of security reason.

SELECT prz.*, (SELECT COUNT(*) AS qty_a 
	   		  FROM runs w
			  WHERE w.PrizeID = prz.PrizeID 
			  AND w.StatusCode NOT IN ('R', 'X')) AS QtyAwarded, 
			  (SELECT COUNT(*) AS qty_v 
			   FROM runs w 
			   WHERE w.PrizeID = prz.PrizeID 
			   AND w.StatusCode = 'V') AS QtyVerified, 
			   (SELECT COUNT(*) AS qty_o 
			   FROM selected_items oi JOIN catalog_items ci ON (ci.ItemID = oi.ItemID) 
			   WHERE ci.PrizeID = prz.PrizeID 
			   AND oi.ItemStatus IN ('ORDERED','CONFIRMED') 
			   GROUP BY oi.ItemID 
			   ORDER BY qty_o ASC LIMIT 1) AS QtyOrdered, 
			   (SELECT COUNT(*) AS qty_s 
			   FROM runs w 
			   WHERE w.PrizeID = prz.PrizeID 
			   AND w.StatusCode = 'F') AS QtyShipped, 
			   (SELECT COUNT(*) AS qty_e 
			   FROM selected_items oi JOIN catalog_items ci 
			   ON (ci.ItemID = oi.ItemID) 
			   WHERE ci.PrizeID = prz.PrizeID 
			   AND oi.ItemStatus IN ('REJECTED') 
			   GROUP BY oi.ItemID ORDER BY qty_e ASC LIMIT 1) AS QtyExceptioned 
FROM gifts prz , 
WHERE prz.PromotionID=2709;


Thanks,

[Updated on: Mon, 29 June 2009 01:44]

Report message to a moderator

Re: query optimization [message #410580 is a reply to message #410575] Mon, 29 June 2009 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
400+ posts and unable to post correctly!

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required and usual information.

Regards
Michel
Re: query optimization [message #410598 is a reply to message #410575] Mon, 29 June 2009 03:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can't give us the information that we need, then you're probsbly not going to get the help you want - sad but true.

Your problem is probably caused by you running each of your SELECT clause queries for every single row that you return.

I'd be very tempted to rewrite your FROM clause as
FROM gifts prz
    ,runs  w
    ,(select * 
      from   selected_items oi
            ,catalog_items  ci
      where  ci.itemid = oi.itemid) oi
WHERE prz.PromotionID=2709
and   prz.prizeid = w.prizeid(+)
and   prz.prizeid = oi.prizeid(+);
and use analytic functions to do the totalling, or group the whole thing by prz.prizeid and use normal aggregate functions.

What is this LIMIT clause that you're using in the Order by?
Re: query optimization [message #410657 is a reply to message #410575] Mon, 29 June 2009 09:56 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
LIMIT is valid with SELECT for MYSQL.
Only LIMIT n number of rows are returned.
Previous Topic: How to find a column by sampling data
Next Topic: row records into one column
Goto Forum:
  


Current Time: Sun Dec 11 04:20:21 CST 2016

Total time taken to generate the page: 0.08131 seconds