Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

retrieving from records from cursor is slow?

Posted on 2014-09-15
6
Medium Priority
?
239 Views
Last Modified: 2014-10-14
Hi,

I have attached my query here. when I can executing from plsql developer , it hardly takes 2 secs.

but when putting in a ref cursor and executing through test window.

It is taking 10 secs to get results to from cursor handle in test window of plsql developer.

Experts, please help.
qry.txt
0
Comment
Question by:sakthikumar
6 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40323177
To start: There is NEVER a reason to use "SELECT DISTINCT" and UNION in a single query.

You are using DISTINCT in 2 places, both should be removed as they are redundant.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40323181
NULL cannot, ever, equal anything.

In other words these do not work:
      AND (NULL IS NULL OR CC.BOE_NO = NULL)
      AND (NULL IS NULL OR CC.RECEIPT_NO = NULL)
      AND (NULL IS NULL OR (CD.BRN_NO = NULL OR CD.BOL_NO = NULL))
      AND (NULL IS NULL OR CC.CUSTOMER_CODE = NULL)

use IS NULL instead
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 40323202
use bindvariables
i guess your generating this query somehow ?
>> or is rotation_no always this specific value ?
(677278 IS NULL OR CC.ROTATION_NO = 677278)

and fwiw ... 677278 IS NUL will always be false
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40323228
I don't understand why you run row_number() over a table and join it back to itself. You can avoid that join.

For the rest I'm not too keen on the correlated subquery to achieve ROTATION_NO (lines 33-39)  but I don't know enough about your data model to suggest an alternate.

I suggest you try UNION ALL. I cannot tell if you really need to remove unwanted repetition from the result, but UNION ALL is faster than UNION.
SELECT
      NVL(CD.BOL_NO, CD.BRN_NO) "BRN/BOL"
    , CC.ROTATION_NO
    , CC.DOC_NO
    , CC.TOTAL_AMOUNT
    , 'GC'
    , CC.RECEIPT_NO
    , CC.COLLECT_DATE
    , CC.CLR_NO
FROM (
           SELECT
                 G.CHARGE_COLLECTION_ID
               , G.ROTATION_NO
               , G.DOC_NO
               , G.TOTAL_AMOUNT
               , G.RECEIPT_NO
               , G.COLLECT_DATE
               , ROW_NUMBER() OVER (PARTITION BY G.BOE_NO ORDER BY G.COLLECT_DATE ASC) CLR_NO
           FROM GCB_DBA.GCB_CHARGE_COLLECTION G
     ) CC
INNER JOIN GCB_DBA.GCB_CHARGE_DTLS CD
      ON CD.CHARGE_COLLECTION_ID = CC.CHARGE_COLLECTION_ID
WHERE CC.STATUS <> 'R'
      AND CD.IS_VALID = 1
      AND (677278 IS NULL OR CC.ROTATION_NO = 677278)
      AND (NULL IS NULL OR CC.BOE_NO IS NULL)
      AND (NULL IS NULL OR CC.RECEIPT_NO IS NULL)
      AND (NULL IS NULL OR CD.BRN_NO IS NULL OR CD.BOL_NO IS NULL)
      AND (NULL IS NULL OR CC.CUSTOMER_CODE IS NULL)
UNION ALL
      SELECT
            NULL      "BRN/BOL"
          , (
                  SELECT
                        BC.ROTN
                  FROM GCB_DBA.OPBE_BOL_CHARGES BC
                  WHERE C.PAY_SR_NO = BC.PAY_SR_NO
                        AND ROWNUM = 1
            ) ROTATION_NO
          , C.BOE_NO  DOC_NO
          , C.TOT_AMT TOTAL_AMOUNT
          , 'OPBE'
          , C.RCPT_NO RECEIPT_NO
          , C.COLLECT_DATE
          , NULL      CLR_NO
      FROM GCB_DBA.OPBE_CHARGE_COLLECTION C
      WHERE C.OTHER_BOE_TYPE = 'N'
            AND NVL(C.DEL_IND, 'N') = 'N'
            AND TXN_SOURCE IS NULL
            AND EXISTS (
                  SELECT
                        1
                  FROM GCB_DBA.OPBE_BOL_CHARGES BC
                  WHERE BC.PAY_SR_NO = C.PAY_SR_NO
                        AND NVL(BC.DEL_IND, 'N') = 'N'
                        AND (677278 IS NULL
                        OR BC.ROTN = 677278)
                        AND (NULL IS NULL
                        OR BC.IMP_CODE = NULL)
            )
            AND (NULL IS NULL
            OR C.BOE_NO = 677278)
            AND (NULL IS NULL
            OR C.RCPT_NO = NULL)
UNION ALL
      SELECT
            NULL      "BRN/BOL"
          , BC.ROTN   ROTATION_NO
          , C.BOE_NO  DOC_NO
          , C.TOT_AMT TOTAL_AMOUNT
          , 'MDS'
          , C.RCPT_NO RECEIPT_NO
          , C.CLR_DATE
          , C.CLR_NO  CLEARANCE_NO
      FROM GCB_DBA.OPBE_BOE_CLEARANCES C
         , GCB_DBA.OPBE_BOE_CHARGES BC
      WHERE C.BOE_NO = BC.BOE_NO
            AND C.CLR_NO = BC.CLR_NO
            AND C.BOE_TYPE = BC.BOE_TYPE
            AND NOT EXISTS (
                  SELECT
                        1
                  FROM GCB_DBA.GCB_CHARGE_COLLECTION CC
                  WHERE CC.BOE_NO = C.BOE_NO
                        AND CC.RECEIPT_NO = C.RCPT_NO
                        AND CC.STATUS <> 'R'
            )
            AND (677278 IS NULL
            OR BC.ROTN = 677278)
            AND (NULL IS NULL
            OR C.BOE_NO = NULL)
            AND (NULL IS NULL
            OR C.RCPT_NO = NULL);

Open in new window

You may need to revert back to UNION
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40323598
I agree that "UNION ALL" is faster then "UNION", because "UNION" will take the time and resources to do a sort operation and remove possible duplicates.  But, if the data from the three different secions of your query are already distinct, you can skip the extra sort and use "UNION ALL".

And, I agree that lines like this look very strange and not at all helpful:
AND NULL IS NULL OR CC.BOE_NO = NULL
1. This part will never be true: "CC.BOE_NO = NULL"
2. This part may always be true "NULL IS NULL" , so why include it?  (I have never tested this syntax, but I don't see what advantage it may possibly add.)
3. If you change that line to this: "AND CC.BOE_NO IS NULL" then it makes sense.  This doesn't help Oracle execute the query efficiently though - it forces Oracle to read every record that meets the other criteria in this section of the query.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40324157
>> it hardly takes 2 secs.  but when putting in a ref cursor and executing through test window.  It is taking 10 secs to get results to from cursor handle in test window of plsql developer.

I think the SQL piece has been covered.  I wanted to comment on the time differences:
Don't know a lot about pl/sql developer but most GUIs like it and SQL Deverloper buffer the results so it will start returning them as soon as it can and does not return the ENTIRE result set at once.

When calling from cursor, it is likely returning ALL rows at once.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question