SQL Query Perfomance Issue.

pts.
Tags:
Automation
ERP
Microsoft Windows
Oracle
Performance/Load
Software testing
Software testing tools
Hi All, This is Wasim Ahmed. I basically wanted a kick start to tune my SQL Queries for performance. I have a huge query which is taking an average of 30seconds for records in thousands. I want to improve the efficiency of the Query. Can any one help me in this. I am pasting my SQL query. I would be greatfull if you can mail me your personal email id to wase_ahmed@hotmail.com so that i can share my PLSql procedure.. Thank you. Best Regards Wasim Ahmed. SELECT * FROM (SELECT ohs.ohs_order_no "SOH_ORDER_NO", ohs.cus_code_dlvy "CUS_CODE_DLVY", cus.cus_name "CUS_NAME", ohs.ohs_doc_ref "SOH_CUSTOMER_DOC_REF", NVL (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format), ' ') "SOH_CUSTOMER_ORDER_DATE", ohs.rep_entity_id "REP_ENTITY_ID", ohs.rev_entity_id "REV_ENTITY_ID", ohs.ohs_status "SOH_STATUS", cm.acm_code_desc "SOH_STATUS_DESCRIPTION", pos.prm_id "PRM_ID", prm.prm_name "PRM_NAME", ohs.ohs_transaction_type "OHS_TRANSACTION_TYPE", ' ' "ACCESSLEVEL", ods.sup_code "SUP_CODE", ohs.ohs_order_classification "SOH_ORDER_CLASSIFICATION" FROM ORDER_HEADER_SCRATCH ohs, CUSTOMER_MASTER cus, CODE_MASTER cm, (SELECT MAX (prm_id) AS prm_id, pos_order_no FROM PROCESS_ORDER_STATUS GROUP BY pos_order_no) pos, PROCESS_MASTER prm, (SELECT ods.ohs_order_no, ods.sup_code FROM ORDER_HEADER_SCRATCH ohs, ORDER_DETAIL_SCRATCH ods WHERE ohs.ohs_order_no(+) = ods.ohs_order_no AND ohs.ohs_status <> 1 GROUP BY ods.ohs_order_no, ods.sup_code) ods WHERE ohs.cus_code_dlvy = cus.cus_code AND ohs.ohs_status LIKE l_orderstatus AND ohs.ohs_order_no LIKE l_orderno AND UPPER (ohs.ohs_transaction_type) = 'SO' AND ohs.ohs_status = cm.acm_code_value AND cm.acm_code_fldname = 'SO_HEADER_STATUS' AND ohs.ohs_order_no = pos.pos_order_no(+) AND prm.prm_id(+) = pos.prm_id -- Dummy order number to suppress the order when despatch note number is given AND ohs.ohs_order_no LIKE l_dummyordernumber -- New Requirment for Simple Search for Sup Code AND ohs.ohs_order_no = ods.ohs_order_no(+) AND UPPER (NVL (ods.sup_code, ' ')) LIKE UPPER (l_supcode) AND ohs.rev_entity_id IN (SELECT t_reventityid FROM TEMP_REVENTITY) AND ohs.rep_entity_id IN (SELECT t_repentityid FROM TEMP_REPENTITY) AND UPPER (NVL (ohs.cus_code_dlvy, ' ')) LIKE UPPER (l_cuscode) AND UPPER (NVL (cus.cus_name, ' ')) LIKE UPPER (l_cusname) AND UPPER (NVL (ohs.ohs_doc_ref, ' ')) LIKE UPPER (l_cusrefno) AND ( (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format)) >= l_orderdatefrom AND ohs.ohs_order_date IS NOT NULL ) AND ( (TO_CHAR (ohs.ohs_order_date, Agsoracleconstant.l_date_format)) <= l_orderdateto AND ohs.ohs_order_date IS NOT NULL ) UNION ALL (SELECT DISTINCT soh.soh_order_no "SOH_ORDER_NO", soh.cus_code_dlvy "CUS_CODE_DLVY", cus.cus_name "CUS_NAME", soh.soh_customer_doc_ref "SOH_CUSTOMER_DOC_REF", NVL (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format), ' ' ) "SOH_CUSTOMER_ORDER_DATE", soh.rep_entity_id_orig "REP_ENTITY_ID", soh.rev_entity_id "REV_ENTITY_ID", soh.soh_status "SOH_STATUS", cm.acm_code_desc "SOH_STATUS_DESCRIPTION", pos.prm_id "PRM_ID", prm.prm_name "PRM_NAME", 'SO' "OHS_TRANSACTION_TYPE", ' ' "ACCESSLEVEL", sod.sup_code "SUP_CODE", soh.soh_order_classification "SOH_ORDER_CLASSIFICATION" FROM SO_HEADER soh, CUSTOMER_MASTER cus, CODE_MASTER cm, (SELECT MAX (prm_id) AS prm_id, pos_order_no FROM PROCESS_ORDER_STATUS GROUP BY pos_order_no) pos, PROCESS_MASTER prm, (SELECT sod.soh_order_no, sod.sup_code, sod.rev_entity_id FROM SO_HEADER soh, SO_DETAIL sod WHERE soh.soh_order_no(+) = sod.soh_order_no AND soh.rev_entity_id = sod.rev_entity_id AND soh.soh_status <> 1 GROUP BY sod.soh_order_no, sod.sup_code, sod.rev_entity_id) sod, (SELECT pic.soh_order_no, pic_picklist, pic.rev_entity_id FROM PICKLIST_MASTER pic WHERE (TO_CHAR (date_last_modified, 'YYYY-MM-DD HH:MI:SS PM')) IN ( SELECT MAX (TO_CHAR (date_last_modified, 'YYYY-MM-DD HH:MI:SS PM')) FROM PICKLIST_MASTER pic1 WHERE pic1.soh_order_no = pic.soh_order_no AND pic1.sod_line_number = pic.sod_line_number AND pic1.rev_entity_id = pic.rev_entity_id GROUP BY pic1.pic_picklist) GROUP BY pic.pic_picklist, pic.soh_order_no, pic.rev_entity_id) pic WHERE soh.cus_code_dlvy = cus.cus_code AND soh.soh_status LIKE l_orderstatus AND soh.soh_order_no LIKE l_orderno AND UPPER (soh.soh_transaction_type) = 'SO' AND soh.soh_status = cm.acm_code_value AND cm.acm_code_fldname = 'SO_HEADER_STATUS' AND soh.soh_order_no = pos.pos_order_no(+) AND pos.prm_id = prm.prm_id(+) -- Despatch Note Conditions AND pic.soh_order_no(+) = sod.soh_order_no -- New Simple Search change Sup Code AND soh.soh_order_no = sod.soh_order_no(+) AND soh.rev_entity_id = sod.rev_entity_id AND UPPER (NVL (sod.sup_code, ' ')) LIKE UPPER (l_supcode) AND soh.rev_entity_id IN (SELECT t_reventityid FROM TEMP_REVENTITY) AND soh.rep_entity_id_orig IN (SELECT t_repentityid FROM TEMP_REPENTITY) AND UPPER (NVL (soh.cus_code_dlvy, ' ')) LIKE UPPER (l_cuscode) AND UPPER (NVL (cus.cus_name, ' ')) LIKE UPPER (l_cusname) AND UPPER (NVL (soh.soh_customer_doc_ref, ' ')) LIKE UPPER (l_cusrefno) -- Despatch note AND NVL (pic.pic_picklist, ' ') LIKE l_despatchnotenumber AND ( (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format)) >= l_orderdatefrom AND soh.soh_customer_order_date IS NOT NULL ) AND ( (TO_CHAR (soh.soh_customer_order_date, Agsoracleconstant.l_date_format)) <= l_orderdateto AND soh.soh_customer_order_date IS NOT NULL )) ORDER BY soh_order_no) WHERE ROWNUM < Agsoracleconstant.l_record_count;

Answer Wiki

Thanks. We'll let you know when a new response is added.

Wow! This one is just a treasure chest of things that take time. Rather than go through the whole thing, which could take an hour, I’ll give you some pointers:

(1) Don’t compare using UPPER or NVL unless you have to. Consider NOT NULL fields, field stored in upper case, a second field set up for comparison, or functional indeces, as in CREATE INDEX xxx ON xxx(UPPER(xxx)).

(2) Use = comparison, rather than LIKE, again, unless you have to.

(3) Try to recfactor the lessen the number of subqueries.

(4) Make sure that what you’re searching and matching on is indexed. If I want something of the form. A.first=B.first AND A.second=B.second, it’s best to have a compound index: CREATE INDEX ON b(first, second)

A complete explanation on how to fix this kind of stuff can be found at:

http://linkersystems.com/ArticlesAndWhitePapers/SQLC.pdf

Shameless plug: Of course, you can have a firm specializing in such things make the changes for you.


/s/Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904 from outside of North America

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • WasimAhmed
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/sql-query-perfomance-issue/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following