BachusII on Jul 11, 2007 at 4:56:56 AM (# 6)
This message has been edited.You are going to a lot of trouble convincing me that you have such an indecent amount of memory installed, that indeed the … hardware is fine … . For (slightly) better readability: SELECT
a.pant_po_dtl_id, a.pant_po_hdr_id, a.pant_po_item_id, a.pant_po_item_desc, a.pant_po_item_qty, a.pant_po_item_rate, a.pant_po_excise_rate, a.pant_po_tax_rate, a.pant_po_tax_cost, a.pant_po_item_val, a.pant_po_item_mrp, a.pant_po_item_icr, a.pant_po_total_cost, PANT_MC_CAT, PANT_PROD_CAT_DESC, PANT_UOM, c.UOM_NAME, d.totShipQty, a.pant_line_item, e.totrecvqty,a.PANT_PO_EAN, a.PANT_PO_ITEMNAME, a.PANT_PO_COLOR, a.PANT_PO_SIZE, a.PANT_PO_SHAPE, a.PANT_PO_SALESPRICE, a.PANT_PO_CONVERSION
FROM
PANT_PO_DTL a, PANT_PROD_CAT_MST b, PANT_UOM_MST c,
(SELECT
pant_po_hdr_id, pant_po_item_id, sum(to_number(SHIPMENT_QTY)) totShipQty, pant_line_item
FROM
ASN_DTL
WHERE
asn_id not in (select asn_id from pant_po_grn_hdr)
GROUP BY
pant_po_hdr_id, pant_po_item_id, pant_line_item) d,
(SELECT
c.PANT_PO_HDR_ID, d.pant_po_item_id, sum(to_number(d.PANT_GRN_RECVD_QTY)) totrecvqty, e.pant_line_item
FROM
PANT_PO_GRN_HDR a, PANT_PO_DISPATCH_DTL b, PANT_PO_HDR c, PANT_PO_GRN_DTL d, ASN_DTL e
WHERE
a.PANT_PO_DIS_DTL_ID = b.PANT_PO_DIS_DTL_ID AND b.PANT_PO_HDR_ID = c.PANT_PO_HDR_ID AND d.PANT_PO_DIS_GRN_ID = a.PANT_PO_DIS_GRN_ID and d.pant_po_item_id = e.pant_po_item_id and c.PANT_PO_HDR_ID = e.PANT_PO_HDR_ID and a.ASN_ID = e.asn_id
GROUP BY
c.PANT_PO_HDR_ID, d.pant_po_item_id, e.pant_line_item) e
WHERE
a.PANT_MC_CAT = b.PANT_PROD_CAT_ID AND a.PANT_UOM = c.UOM_ID AND a.pant_po_hdr_id = d.pant_po_hdr_id (+) AND a.pant_po_item_id = d.pant_po_item_id (+) AND a.pant_line_item = d.pant_line_item (+) AND a.pant_po_hdr_id = e.pant_po_hdr_id (+) AND a.pant_po_item_id = e.pant_po_item_id (+) AND a.pant_line_item = e.pant_line_item (+) AND a.pant_po_hdr_id = '4500005182'
pashah72 on Jul 11, 2007 at 6:31:49 AM (# 7)Thanks for indenting but as I said I got involved in between project (project was already started, going on for last few months, working fine) but found slow only at this stage & fast while surfing whole site so how hardware is creating problem?
pashah72 on Jul 11, 2007 at 7:00:19 AM (# 8)Application Server is weblogic 8.1 & 2GB Ram.
ChrisRickard on Jul 11, 2007 at 8:53:01 AM (# 9)I really don't get how such monstrosities ever see the light of day!
Now I'm pretty sure none of us here are able or willing to actually "Just fix it" for you. But I'll give you some tips I've found when optimizing queries:
First off I'd re-write it using joins and temp tables. Mind you this won't necessarily increase performance but it makes it so much easier to comprehend, also it makes it easier to break the query up into chunks.
Once you have discreet JOIN chunks that you can test individually see which one is taking so long (there may be more than one). Once you identify these see if there are missing or incorrect indexes.
MHenke on Jul 11, 2007 at 11:03:43 PM (# 10)This is usually the point where a significant decrease of interest becomes noticable...
pashah72 on Jul 11, 2007 at 11:09:09 PM (# 11)Thanks for tips, that's what I was looking for because explaining project, sending database structure, queries,etc. is difficult. There are manty tables linked to each other so its difficult to give everything so I was asking theortically wise what is the best way to write queries, how to optimize it for better performance.
Still more tips are welcome.
pashah72 on Jul 15, 2007 at 11:03:48 PM (# 12)I like ChrisRickard tips . Anymore tips for best way to write queries, to optimize it for better performance so that data retrieval is fast.
|
Query Optimization?