Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Red face Unanswered: Problem with a cursor

    I AM TRYING TO FETCH DATA FROM A TABLE THROUGH A CURSOR WHICH MIGHT CONTAIN MSISDN NUMBERS IN DUPLICATE. I HAVE TO FETCH THEM AND THEN PERFORM A SUM FUNCTION FOR THE 'MOU' COLUMN OF THOSE DUPLICATE MSISDN NUMBERS.
    THIS CURSOR IS WORKING VERY SLOW. IT TAKES 5 MINUTES TO INSERT OR UPDATE ONLY 1000 RECORDS.
    KINDLY HELP HOW TO SPEED UP THE EXECUTION TIME. OR SUGGEST IF THERE IS SOMETHING WRONG WITH THE LOGIC I AM EXECUTING..
    THE CODE IS AS FOLLOWS:



    CREATE PROCEDURE DCSDEV.UPDATE_USAGEDATADUMMY()
    LANGUAGE SQL
    P1: BEGIN

    DECLARE lv_DUMMYVAR DOUBLE;
    DECLARE lv_MSISDN_CURS VARCHAR(45);
    DECLARE lv_DATE_CURS DATE;
    DECLARE V INT DEFAULT 0;

    DECLARE DUP_VOICE_RECS CURSOR WITH RETURN FOR (
    SELECT MSISDN_NO, CALL_DATE FROM TEMP_VOICE_USAGE GROUP BY MSISDN_NO, CALL_DATE HAVING COUNT(MSISDN_NO) > 1) ;

    OPEN DUP_VOICE_RECS;
    FETCH_DUP_VOICE_RECS: LOOP
    FETCH DUP_VOICE_RECS INTO lv_MSISDN_CURS, lv_DATE_CURS;
    SELECT COUNT(MSISDN) INTO V FROM USAGE_DATA WHERE MSISDN = lv_MSISDN_CURS AND USAGE_DATE = lv_DATE_CURS;
    SELECT SUM(DOUBLE(MOU)) INTO lv_DUMMYVAR FROM TEMP_VOICE_USAGE WHERE MSISDN_NO = lv_MSISDN_CURS;
    IF V=0
    THEN
    INSERT INTO USAGE_DATA (MSISDN, USAGE_DATE, MOU) VALUES (lv_MSISDN_CURS, lv_DATE_CURS, lv_DUMMYVAR);
    ELSE
    UPDATE USAGE_DATA SET MOU = (MOU + lv_DUMMYVAR) WHERE MSISDN = lv_MSISDN_CURS;
    END IF;
    DELETE FROM TEMP_VOICE_USAGE WHERE MSISDN_NO = lv_MSISDN_CURS;
    END LOOP;
    CLOSE DUP_VOICE_RECS;

    END P1
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are using procedural processing, where you do something for each record in TEMP_VOICE_USAGE. The "something" is the execution of 2 SQL statements (1x INSERT or UPDATE and 1x DELETE). It is not very surprising that it performs poorly for you. Especially if you consider that the DELETE is a searched-delete and not a positioned-delete. (A positioned delete uses WHERE CURRENT OF <cursor>.)

    You may want to use SQL in a set-oriented fashion. You may want to use the MERGE statement. Another option would be to have 1x INSERT for all new records, 1x UPDATE for all existing ones and then a single DELETE (no loops):
    Code:
    INSERT INTO usage_data
    SELECT ... FROM temp_usage_data AS t
    WHERE NOT EXISTS ( SELECT 1 FROM usage_data AS u WHERE u.msisdn_no = t.dup_voice_recs
    
    UPDATE usage_data AS u
    SET u.mou = ( SELECT u.mou + SUM(DOUBLE(t.mou)) FROM temp_voice_usage AS t WHERE u.msisdn = t.dup_voice_recs )
    WHERE EXISTS ( SELECT 1 FROM temp_voice_usage AS t2 WHERE u.msisdn = t2.dup_voice_recs )
    
    DELETE FROM temp_voice_usage
    (modulo typing errors)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •