Home » SQL & PL/SQL » SQL & PL/SQL » Suggest the best alternative apart from the Function
Suggest the best alternative apart from the Function [message #416798] Tue, 04 August 2009 05:55 Go to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi,

I have written a oracle package in which for getting PortName and Discharge Port for the input CalculationNumber, I am calling a function in the ref_cursor select statement.

But for the fields to get populated..it is taking more than 1 min for 11,000 records..(front-end application as DOT-NET)

For your reference:

FUNCTION UFN_ConcatVoySearch_DischPort
        (
                CalculationNumber IN INT
        )
    RETURN    VARCHAR2 
    AS
        l_DischargePortName_tmp    VARCHAR2(5000)    ;
        l_DischargePortName        VARCHAR2(5000)    ;
        l_CalcNumber            NUMBER(13)        ;    
        --Cursors
    CURSOR cur_dischport(in_calcnumber INT)
    IS 
        SELECT  PORT_NAME
           FROM    T465_CARGOPORTS
          WHERE    PORT_NAME IS NOT NULL
            AND CALCNO        =    in_calcnumber
            AND    PORT_TYPE    =    'D'
            GROUP BY PORT_NAME
            ORDER BY MAX(PORTNO) DESC;    
    BEGIN
    
    l_CalcNumber            :=    CalculationNumber;
    l_DischargePortName        :=    NULL;
    
        FOR cur_dischport_rec IN 
            cur_dischport(l_CalcNumber)
        LOOP
            l_DischargePortName_tmp    :=    NULL;
            l_DischargePortName_tmp    :=    cur_dischport_rec.PORT_NAME;
            
            IF l_DischargePortName IS NULL
            THEN
               l_DischargePortName := l_DischargePortName || l_DischargePortName_tmp;
            ELSE
               l_DischargePortName := l_DischargePortName || '/' || l_DischargePortName_tmp;
            END IF;
         END LOOP;    
        
    RETURN  l_DischargePortName;
    
    END UFN_ConcatVoySearch_DischPort;   


Reference Cursor I am using in the Procedure of the Package Body
whose ouput will be in the form of record set.Please ignore the local variables..as I am the values as NULL to get all the records from the table..

OPEN ret_cursor FOR
                        SELECT    a.CALCNR                                          AS CalculationNumber
                        ,        a.CALCTYPE                                                        AS CalculationType        
                        ,        a.NAME_SHIP                                                       AS VesselName    
                        ,        a.DATE_INS                                                        AS EstimationDate    
                        ,        a.USER_INS                                                        AS UserId    
                        ,        a.EstimateDesc                                                    AS EstimateDesc    
                        ,        a.EstimateGroupDesc                                               AS EstimateGroupDesc
                        ,		UFN_ConcatVoySearch_LoadPort(a.CALCNR)           AS LoadPort        
                        ,		UFN_ConcatVoySearch_DischPort(a.CALCNR)          AS DischargePort
                        ,        ''                                                                 AS Charterer
                        ,        ''                                                                 AS Commodity                        
                        ,        a.Status                                                           AS Status
                        FROM    T460_voycalc a                        
                        WHERE    a.CALCNR                            LIKE        NVL(l_CalculationNumber,'%')
                        AND        a.CALCTYPE                            LIKE        NVL(l_CalculationType,'%')
                        AND        NVL(a.NAME_SHIP,' ')                LIKE        NVL(l_VesselName,'%')            
                        AND        NVL(a.USER_INS,' ')                    LIKE        NVL(l_UserId,'%')                
                        AND        NVL(a.EstimateDesc,' ')                LIKE        NVL(l_EstimateDesc,'%')        
                        AND        NVL(a.EstimateGroupDesc,' ')        LIKE        NVL(l_EstimateGroupDesc,'%')
                        GROUP BY        a.CALCNR            
                                 ,        a.CALCTYPE            
                                 ,        a.NAME_SHIP    
                                 ,        a.DATE_INS        
                                 ,        a.USER_INS            
                                 ,        a.EstimateDesc        
                                 ,        a.EstimateGroupDesc
                                 ,        a.Status;


Please let me know the best alternative apart from writing the function and calling it.

Thanks,
Hemanth
Re: Suggest the best alternative apart from the Function [message #416801 is a reply to message #416798] Tue, 04 August 2009 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
How long does it take to retrieve the records if you comment out the function calls?
Re: Suggest the best alternative apart from the Function [message #416804 is a reply to message #416801] Tue, 04 August 2009 06:14 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
It is taking 1-2 secs for all 11,000 records to get populate..
Re: Suggest the best alternative apart from the Function [message #416879 is a reply to message #416804] Tue, 04 August 2009 23:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
calling plsql from sql is expensive. Not that I don't do it. On the contrary I do it a lot when necessary.

Additionally, I do not know your data but if you are scanning a large table each time you call the function that would of course add to the cost.

I would suggest one possible coding alternative. Try using SYS_CONNECT_BY_PATH rather than the function call. Something like this:

sys_context(
            (
             <your select sql here with connect by stuff>
            ),'/'
            )

Consider this:

select sys_connect_by_path(c1,'/')
from (
       select 1 c1 from dual union all
       select 2 from dual
)
where connect_by_isleaf = 1
connect by prior c1 = c1-1
start with c1 = 1
/

by replacing the function with a singleton select you might be able to avoid context switching. If this is your issue it would. You could gauge that by deleting all data from table so you fuction has no data to look at. Remeber to truncate to reset the highwater mark, not delete.

Good luck, Kevin
Re: Suggest the best alternative apart from the Function [message #417331 is a reply to message #416879] Fri, 07 August 2009 05:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Another option would be a variant of Tom Kytes STRAGG function. This concatenates strings together in SQL, which is basically what your function does.
You could just replace your calls to your function with Stragg calls.
Previous Topic: unresolved error
Next Topic: iterative field matching process
Goto Forum:
  


Current Time: Sun Dec 11 05:59:59 CST 2016

Total time taken to generate the page: 0.07291 seconds