Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

URGETNT !! Query perfomance optimization

drovnikdrovnik Member Posts: 31
I have a query is used inside a function that returns an ID but
it takes ages to complete. A 1000 records takes about 30mins but
which means the 40696 records it processes will take almost 21hrs.

Please help me shorten this execution time.

[CODE]

FUNCTION fn_Client_PayPoint_ID( p_PayPoint_ID IN Client_PayPoints.Paypoint_ID%TYPE
, p_Reference_Number IN Client_PayPoints.Reference_Number%TYPE
, p_Value_Date IN DATE DEFAULT fn_Current_Date
) RETURN Client_PayPoints.Client_Paypoint_ID%TYPE IS
--
CURSOR cr_Client_PayPoint_ID( cp_PayPoint_ID Client_PayPoints.Paypoint_ID%TYPE
, cp_Reference_Number Client_PayPoints.Reference_Number%TYPE
, cp_Value_Date DATE
) IS
SELECT ClPa.Client_Paypoint_ID
FROM Client_PayPoints ClPa
WHERE ClPa.Paypoint_ID = cp_PayPoint_ID
AND CASE
WHEN cp_PayPoint_ID IN ( c_PayPoint_KgatlengCouncil_P
, c_PayPoint_KgatlengCouncil_I
, c_PayPoint_SouthEastCouncil_P
, c_PayPoint_SouthEastCouncil_I
, c_PayPoint_JwanengCouncil_P
, c_PayPoint_JwanengCouncil_I
, c_PayPoint_LetlhakengCouncil_P
, c_PayPoint_LetlhakengCouncil_I
, c_PayPoint_SowaTownship_P
, c_PayPoint_SowaTownship_I
, c_PayPoint_NgwatoLandBoard_P
, c_PayPoint_NgwatoLandBoard_I
, c_PayPoint_SerowePalapye_P
, c_PayPoint_SerowePalapye_I
, c_PayPoint_Mahalapye_P
, c_PayPoint_Mahalapye_I
, c_PayPoint_Debswana
, c_PayPoint_BPC
) THEN LTRIM( ClPa.Reference_Number, '0')
ELSE ClPa.Reference_Number
END = CASE
WHEN cp_Paypoint_ID IN ( c_PayPoint_Debswana
, c_PayPoint_BPC
) THEN LTRIM(cp_Reference_Number,'0')
ELSE cp_Reference_Number
END
--
AND cp_Value_Date BETWEEN ClPa.Collection_First_Date
AND NVL( ClPa.Collection_Last_Date, cp_Value_Date);
--
v_Client_PayPoint_ID Client_PayPoints.Client_Paypoint_ID%TYPE;
--
BEGIN
--
OPEN cr_Client_PayPoint_ID( p_PayPoint_ID
, p_Reference_Number
, p_Value_Date
);
--
FETCH cr_Client_PayPoint_ID INTO v_Client_PayPoint_ID;
--
CLOSE cr_Client_PayPoint_ID;
--
RETURN( v_Client_PayPoint_ID);
--
--
END fn_Client_PayPoint_ID;

[/CODE]

The above code return a value to a procedure that executes a
similar cursor query.

Somebody help me please.

Comments

  • infidelinfidel Member Posts: 2,900
    : [CODE]
    : SELECT ClPa.Client_Paypoint_ID
    : FROM Client_PayPoints ClPa
    : WHERE ClPa.Paypoint_ID = cp_PayPoint_ID
    : [/CODE]
    :
    : The above code return a value to a procedure that executes a
    : similar cursor query.
    :
    : Somebody help me please.
    :

    It looks like you have a foreign key relationship here, do you have the proper indexes and constraints created in the database?

    That ugly case statement in the WHERE clause is going to be performed on every row in the set of records that has ClPa.Paypoint_ID = cp_PayPoint_ID. Perhaps you could remove that logic from the query and do it inside a loop.

    [code]
    open mycursor;
    loop
    fetch mycursor into whatever;
    exit when mycursor%notfound;

    if then
    myreturnvalue := whatever;
    exit;
    end if;
    end loop;
    [/code]

    I'd have to try it to see if it helps, but I am betting it will make a huge difference. Having an index on the Client_PayPoints.Paypoint_ID column will probably make a bigger difference, though.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.