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  |
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   |
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 #436241 is a reply to message #436236] |
Tue, 22 December 2009 05:25   |
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 #436265 is a reply to message #436190] |
Tue, 22 December 2009 09:11   |
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   |
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  |
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),
...
|
|
|
Goto Forum:
Current Time: Mon Feb 10 11:50:44 CST 2025
|