Home » SQL & PL/SQL » SQL & PL/SQL » need help for insert (database10g,10.1,xp)
need help for insert [message #436190] Tue, 22 December 2009 01:19 Go to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
I need to help write as single insert statement instead of insert and update or give me new idea to approach since this update statement taking more time.


1) first i'm inserting data into table like below.

INSERT INTO SS_ITEM_STAT_OPN(
        REP_ID, ITEM_ID, REPORT_COLUMN_DISPLAY, REPORT_COLUMN_SEQ_NO, BEGIN_VALUE, END_VALUE)
        SELECT 
        LN_SEQ_REP_ID, ITEM_ID, REPORT_COLUMN_DISPLAY, REPORT_COLUMN_SEQ_NO, BEGIN_VALUE, END_VALUE
        FROM VW_SCALE_SCORE_CONFIG CROSS JOIN REP_ITEM_STAT_OPN 
        WHERE REP_ID = LN_SEQ_REP_ID;




and i'm updating same table using for loop,for loop purpose i'm using below statement

SELECT ITEM_ID 
        BULK COLLECT INTO TV_REP_ITEM_STAT_OPN
        FROM REP_ITEM_STAT_OPN
        WHERE REP_ID = LN_SEQ_REP_ID;



and i'm updating like below.

FOR LN_SSI_OPN IN NVL(TV_SS_ITEM_STAT_OPN.FIRST,1)..NVL(TV_SS_ITEM_STAT_OPN.LAST,0)
        LOOP
            UPDATE SS_ITEM_STAT_OPN SET 
            TOT_STUD_ITEM_SS             =     NVL((SELECT COUNT(*) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
                                            WHERE    SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                                    AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                            AND     SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
                                            AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                            AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE
                                            AND ROUND(A.SCALE_SCORE) <= END_VALUE
                                            AND CONDITION_COLUMN= 'SCALE_SCORE'
                                            GROUP BY ITEM_ID,END_VALUE),0),
            TOT_STUD_CORR_ANS_ITEM_SS     =     NVL((SELECT COUNT(*) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
                                            WHERE SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                                    AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                            AND  SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
                                            AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                            AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE 
                                            AND ROUND(A.SCALE_SCORE) <= END_VALUE
                                            AND CONDITION_COLUMN= 'SCALE_SCORE'
                                            AND RESPONSE_IS_CORRECT = 1
                                            GROUP BY ITEM_ID,END_VALUE),0),
            TOT_STUD_SS                 =  NVL((SELECT COUNT(*) FROM 
                                            (SELECT A.TEST_SESSION_DETAIL_ID
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B,SS_ITEM_STAT_OPN
                                            WHERE SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                            AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                            AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                            AND ROUND(A.SCALE_SCORE) >B.BEGIN_VALUE
                                            AND ROUND(A.SCALE_SCORE) <= B.END_VALUE
                                            AND CONDITION_COLUMN= 'SCALE_SCORE'
                                            GROUP BY A.TEST_SESSION_DETAIL_ID),VW_SCALE_SCORE_CONFIG
                                            WHERE END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE
                                            GROUP BY END_VALUE),0)
            WHERE    REP_ID = LN_SEQ_REP_ID
            AND     SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                    AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE);
            COMMIT;


can some one help to give idea .
Re: need help for insert [message #436215 is a reply to message #436190] Tue, 22 December 2009 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you're looping and performing SQL inside the loop, which is always a good way to slow things down - can you rewrite things to do a single sql?
Failing that, you can rewrite the statement to use the FORALL syntax to reduce the execution time a bit.

Take that commit outside of the loop too.
Re: need help for insert [message #436236 is a reply to message #436215] Tue, 22 December 2009 05:13 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
can you help to re write a update using FORALL?
Re: need help for insert [message #436241 is a reply to message #436236] Tue, 22 December 2009 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this.

Your best performance gain will come from loosing the loop altogether and merging the original SELECT into the WHERE clause of the UPDATE statment.

FORALL LN_SSI_OPN IN NVL(TV_SS_ITEM_STAT_OPN.FIRST,1)..NVL(TV_SS_ITEM_STAT_OPN.LAST,0)
  UPDATE SS_ITEM_STAT_OPN 
  SET    TOT_STUD_ITEM_SS          = NVL((SELECT COUNT(*) 
                                          FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
                                          WHERE    SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                          AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                                   AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                          AND     SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
                                          AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                          AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE
                                          AND ROUND(A.SCALE_SCORE) <= END_VALUE
                                          AND CONDITION_COLUMN= 'SCALE_SCORE'
                                          GROUP BY ITEM_ID,END_VALUE),0),
         TOT_STUD_CORR_ANS_ITEM_SS = NVL((SELECT COUNT(*) 
                                          FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
                                          WHERE SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                          AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                                  AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                          AND  SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
                                          AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                          AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE 
                                          AND ROUND(A.SCALE_SCORE) <= END_VALUE
                                          AND CONDITION_COLUMN= 'SCALE_SCORE'
                                          AND RESPONSE_IS_CORRECT = 1
                                            GROUP BY ITEM_ID,END_VALUE),0),
         TOT_STUD_SS               = NVL((SELECT COUNT(*) FROM 
                                         (SELECT A.TEST_SESSION_DETAIL_ID
                                          FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B,SS_ITEM_STAT_OPN
                                          WHERE SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                          AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                          AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                          AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                          AND ROUND(A.SCALE_SCORE) >B.BEGIN_VALUE
                                          AND ROUND(A.SCALE_SCORE) <= B.END_VALUE
                                          AND CONDITION_COLUMN= 'SCALE_SCORE'
                                          GROUP BY A.TEST_SESSION_DETAIL_ID),VW_SCALE_SCORE_CONFIG
                                          WHERE END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE
                                          GROUP BY END_VALUE),0)
  WHERE    REP_ID = LN_SEQ_REP_ID
  AND     SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
  AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
          AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE);
Re: need help for insert [message #436242 is a reply to message #436236] Tue, 22 December 2009 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you first ask, "can you help to rewrite it in a single statement?"
And don't put a commit inside a loop.

To use "forall" you just to replace "for" by "forall" and remove "loop".

Regards
Michel
Re: need help for insert [message #436265 is a reply to message #436190] Tue, 22 December 2009 09:11 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
TOT_STUD_ITEM_SS and TOT_STUD_CORR_ANS_ITEM_SS could be computed in single passage
SELECT COUNT(*) As TOT_STUD_ITEM_SS , 
       Count(Case When RESPONSE_IS_CORRECT = 1 Then 1 End) As TOT_STUD_CORR_ANS_ITEM_SS
  FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
  WHERE    SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
  AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
           AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
  AND     SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
  AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
  AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE
  AND ROUND(A.SCALE_SCORE) <= END_VALUE
  AND CONDITION_COLUMN= 'SCALE_SCORE'
  GROUP BY ITEM_ID,END_VALUE

Re: need help for insert [message #436342 is a reply to message #436265] Tue, 22 December 2009 22:48 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Thanks mnitu.Can you help me simplifying for below update statement since i'm not much comfortable with pl sql.
 UPDATE REP_ITEM_STAT_OPN SET 
            TOT_STUD_CORR_ANS_BY_ITEM     =    NVL((SELECT COUNT(1) 
                                            FROM     PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE     RESPONSE_IS_CORRECT = 1 
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY ITEM_ID ),0) ,
            POOL_CREATED_DT             =     (SELECT MAX(A.POOL_CREATED_DT) 
                                            FROM     PSYCHOMETRIC_ITEM_STAT_OPN A
                                            WHERE     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND REP_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID
                                            GROUP BY ITEM_ID),
            TOT_SS_BY_ITEM                 =     NVL((SELECT SUM(SCALE_SCORE) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY ITEM_ID ),0),
            AVG_SS_CORR_BY_ITEM         =     NVL((SELECT AVG(SCALE_SCORE) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE    RESPONSE_IS_CORRECT = 1 
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY ITEM_ID ),0) ,
            AVG_SS_INCORR_BY_ITEM         =     NVL((SELECT AVG(SCALE_SCORE) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE RESPONSE_IS_CORRECT <> 1 
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY ITEM_ID ),0),
            STD_DEV_SS                     = NVL((SELECT STDDEV(SCALE_SCORE)
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE    REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID),0),
            OVERALL_IER                 =    (CASE
                                                WHEN LN_TOT_RECORDS = 0 THEN '.' 
                                                ELSE TO_CHAR(TOT_STUD_BY_ITEM/LN_TOT_RECORDS)
                                            END),
            MEAN_TIME                     =     NVL((SELECT TO_CHAR(AVG(RESPONSE_DURATION))
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID),'.'),
            STD_DEV_TIME                 =  CASE WHEN TOT_STUD_BY_ITEM =0 OR TOT_STUD_BY_ITEM=1 THEN '.'
                                            ELSE NVL((    SELECT TO_CHAR(STDDEV(RESPONSE_DURATION)) FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                                            WHERE    REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                                            AND     REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID
                                            GROUP BY PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID),'0')END,
            CUM_IER                        =    (CASE
                                                WHEN LN_TOT_FOURYR_RECORDS = 0 THEN '.' 
                                                ELSE TO_CHAR((SELECT TOT_STUD_FOUYR_BY_ITEM FROM REP_ITEM_STAT_OPN
                                                WHERE ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN))/LN_TOT_FOURYR_RECORDS)
                                            END )                                                                                        
            WHERE     REP_ID = LN_SEQ_REP_ID
            AND     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN);
            COMMIT;
            UPDATE REP_ITEM_STAT_OPN SET 
            OVERALL_ITEM_DIFF             =    (CASE
                                                WHEN TOT_STUD_BY_ITEM = 0 THEN '.' 
                                                ELSE TO_CHAR(TOT_STUD_CORR_ANS_BY_ITEM/TOT_STUD_BY_ITEM)
                                            END)    
            WHERE     REP_ID = LN_SEQ_REP_ID
            AND     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN);
            COMMIT;
            UPDATE REP_ITEM_STAT_OPN SET 
            ITEM_DISCRIMINATION         =     (CASE 
                                                WHEN TOT_STUD_BY_ITEM =0 OR TOT_STUD_BY_ITEM=1 THEN '.'
                                                WHEN STD_DEV_SS = 0 THEN '.' 
                                                WHEN OVERALL_ITEM_DIFF=0 OR OVERALL_ITEM_DIFF=1 THEN '.'
                                                ELSE TO_CHAR((((AVG_SS_CORR_BY_ITEM-AVG_SS_INCORR_BY_ITEM) * SQRT(OVERALL_ITEM_DIFF*(1-OVERALL_ITEM_DIFF)))/STD_DEV_SS)*SQRT(TOT_STUD_BY_ITEM/(TOT_STUD_BY_ITEM-1)))
                                            END)    
            WHERE     REP_ID = LN_SEQ_REP_ID
            AND     REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN);

Re: need help for insert [message #436387 is a reply to message #436342] Wed, 23 December 2009 04:46 Go to previous message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Whenever possible try to compute values in a single passage, something like this:
UPDATE REP_ITEM_STAT_OPN SET 
      (TOT_STUD_CORR_ANS_BY_ITEM, POOL_CREATED_DT, TOT_SS_BY_ITEM, AVG_SS_CORR_BY_ITEM, ...) 
       =    (SELECT Count(Case When RESPONSE_IS_CORRECT = 1 Then 1 End) As TOT_STUD_CORR_ANS_BY_ITEM,
                   MAX(A.POOL_CREATED_DT) As POOL_CREATED_DT,
                   SUM(SCALE_SCORE) As TOT_SS_BY_ITEM,
                   AVG(Case When RESPONSE_IS_CORRECT = 1 Then scale_score End) As SCALE_SCORE ,
                   ...
                           
                  FROM PSYCHOMETRIC_ITEM_STAT_OPN 
                  AND  REP_ITEM_STAT_OPN.ITEM_ID=TV_REP_ITEM_STAT_OPN(LN_SS_OPN)
                  AND  REP_ITEM_STAT_OPN.ITEM_ID = PSYCHOMETRIC_ITEM_STAT_OPN.ITEM_ID                        
                  GROUP BY ITEM_ID ),
      OVERALL_IER                 =    (CASE
                                                WHEN LN_TOT_RECORDS = 0 THEN '.' 
                                                ELSE TO_CHAR(TOT_STUD_BY_ITEM/LN_TOT_RECORDS)
                                            END),                        
  ...

Previous Topic: problem with ref cursor
Next Topic: create a view that has zip codes and nearby zip codes
Goto Forum:
  


Current Time: Wed Dec 07 04:39:48 CST 2016

Total time taken to generate the page: 0.11445 seconds