Solved

retrieving from records from cursor is slow?

Posted on 2014-09-15
6
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 49

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 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 77

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

635 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