| Tuning of a sql querry [message #222114] |
Thu, 01 March 2007 23:13  |
donind
Messages: 95
Registered: February 2007
|
Member |
|
|
Hi,
Below is my querry. I just need a technique and how a below querry can be tuned
SELECT DISTINCT rop1.routingid,rh.routing_id,rh.start_date,rh.location_id,rh.material
FROM mst_routing_header rh,MST_ROUTINGOPERATION rop1
WHERE (rop1.routingid,rh.material,rh.location_id) IN (SELECT DISTINCT rop.routingid,itm.mfr_item_id,ibr.producedlocationid
FROM MST_ITEM itm,MST_ROUTINGHEADER rthdr,MST_ITEMBOMROUTING ibr,MST_ROUTINGOPERATION rop
WHERE rthdr.routingid =ibr.routingid
AND rthdr.location_id =ibr.producedlocationid
AND ibr.produceditemid=itm.item_id
AND rthdr.routingid=rop.routingid
AND rthdr.location_id=rop.location_id
AND rthdr.routingtype='TEST'
AND ibr.sys_ent_state='ACTIVE'
AND rthdr.sys_ent_state='ACTIVE'
AND rop.sys_ent_state='NEW')
AND rh.sys_ent_state='ACTIVE';
Thanks in advance
|
|
|
|
|
|
|
|
|
|
| Re: Tuning of a sql querry [message #222149 is a reply to message #222142] |
Fri, 02 March 2007 00:56  |
donind
Messages: 95
Registered: February 2007
|
Member |
|
|
Any help in tuning this querry
SELECT distinct a.routing_id,a.platform,a.equipment_group,a.equipment_category,a.priority as eqpp ,b.priority as rdtlp
FROM mst_equipment_priority a ,mst_routing_detail b,mst_resource_master c
WHERE a.routing_id=l_routingid
AND a.routing_id=b.routing_id
AND a.platform=b.platform
AND b.platform in (SELECT DISTINCT platform FROM mst_routing_detail WHERE routing_id=l_routingid)
AND a.equipment_group=c.equipment_group
AND a.equipment_category=c.equipment_category
AND c.planning='Y'
AND c.equipment_type =l_equipment_type
AND a.sys_ent_state='ACTIVE'
AND b.sys_ent_state='ACTIVE'
AND c.sys_ent_state='ACTIVE'
ORDER BY a.equipment_category desc,rdtlp asc,eqpp asc;
thanks
|
|
|
|
| Re: Tuning of a sql querry [message #222218 is a reply to message #222149] |
Fri, 02 March 2007 06:10 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Query tuning is a non-trivial task.
If there was a simple step by step process that you could follow to get a tuned query at the far end, it would be automated.
Tuning is not a boolean process - Queries are not either Tuned or Untuned. There is frequently something else that you could do to to make a query faster, but a law of diminishing returns quickly takes effect.
1) You need to work out why you this query needs tuning (DBA complains of excessive IO, Users say it takes too long to return data)
2) You need to work out when you will have finished tuning it (What level of IO is acceptable, how quickly do the users need it to run)
3) You need to know how to tune queries. Go and read those links @rleishman pointed you to.
If you want us to look at it, then as the sticky says, give us more info.
At a minimum we'll need an Explain Plan for the query, details of what indexes are currently on the columns, sizes of the tables, and some idea of how many rows from each table are expected to be involved in the query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
