Solved

retrieving from records from cursor is slow?

Posted on 2014-09-15
6
206 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 48

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 48

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 36

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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 34

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now