December 27, 2012 at 3:33 am
I have a procedure which is getting called on click of button. It takes around 5 sec to complete .The problem is in mean time if user clicks again on the button then multiple rows are getting inserted in table which leads to corrupt data. Need to bring down the procedure execution time down to atleast 2 sec. Please help. The procedure is
SCP_SME_INITIATE_LND_ACTION inside this m calling another procedure SCP_SME_INITIATE .
CREATE OR REPLACE PROCEDURE SCP_SME_INITIATE_LND_ACTION(XLNDPK IN CHAR,
XSME_SELECTED IN CHAR,
XLND_USER IN CHAR,
XREMARKS IN VARCHAR2) as
--XLID char(20);
-- XCOMID char(20);
-- XPROFID char(20);
-- XLNDID char(20);
XSME char(20);
XXLEARNER char(20);
XXCOMID char(20);
XXPROFID char(20);
xxSmePending CHAR(20);
BEGIN
select slat.user_id, slat.competency_id, slat.proficiency_id
into XXLEARNER, XXCOMID, XXPROFID
from sct_sme_lnd_action slat
where slat.id = XLNDPK;
begin
select 1 into xxSmePending
from cmt_person cp
inner join sct_sme_workflow ssw on ssw.user_id = cp.id
inner join sct_custom_assessment sca on sca.id = ssw.custom_assessment_id
inner join sct_learner_workflow slw on slw.id = sca.workflow_id
where slw.competency_id = XXCOMID
and slw.proficiency_id = XXPROFID
and slw.learner_id = XXLEARNER
and ssw.sme_approval_status='600'
and ssw.completion_date is null;
exception when no_data_found then
xxSmePending:=null;
end;
if (xxSmePending is null) then
select cp.id into XSME from cmt_person cp where cp.username=XSME_SELECTED;
-- select cp.id into XLNDID from cmt_person cp where cp.username=XLND_USER;
update tp2.sct_sme_lnd_action sld
set sld.status = 200,
sld.assigned_sme_id = XSME,
sld.lnd_id = XLND_USER,
sld.updated_on = sysdate,
sld.remarks = XREMARKS
where sld.id = XLNDPK;
commit;
SCP_SME_INITIATE(XXLEARNER,XXCOMID,XXPROFID,XSME_SELECTED);
scp_sme_automailer_initiate (XXLEARNER,
XXCOMID,
XXPROFID,
XSME_SELECTED);
end if;
end;
----------------SCP_SME_INITIATE-------------------------
CREATE OR REPLACE PROCEDURE SCP_SME_INITIATE (
XLEARNER_ID IN CHAR,
XCOMPETENCY_ID IN CHAR,
XPROFICIENCY_ID IN CHAR,
XSME_USER_NAME IN CHAR
) as
xxworkflow_id CHAR(20);
xxassesmentid CHAR(20);
/* xxsmeassesmentid CHAR(20);*/
XXCUSTOM_ASSESSMENT_ID CHAR(20);
xxexpiry INT;
xxstartdate DATE;
xxexpirydate DATE;
XXMANAGER_ID CHAR(20);
xxsmeid CHAR(20);
BEGIN
begin
select cp1.id into xxsmeid
from cmt_person cp1 where cp1.username= XSME_USER_NAME;
exception when no_data_found then
xxsmeid:=null;
end;
begin
select slw.id into xxworkflow_id
from sct_learner_workflow slw
where slw.learner_id = XLEARNER_ID and slw.competency_id = XCOMPETENCY_ID and
slw.proficiency_id = XPROFICIENCY_ID and slw.status=100 and slw.workflow_flag=100;
exception when no_data_found then
xxworkflow_id:=null;
end;
begin
select sca.id
into xxassesmentid
from sct_custom_assessment sca
where sca.workflow_id = xxworkflow_id and sca.type = 300
group by sca.id;
exception when no_data_found then
xxassesmentid := null;
end;
if (xxassesmentid is null) then
insert into sct_custom_assessment
(ID, WORKFLOW_ID, TYPE, STATUS, REQUEST_DATE, COMPLETION_DATE)
values
(('sclws' ||
lpad(ltrim(rtrim(to_char(scs_custom_assmnt_seq.nextval))), 15, '0')),
xxworkflow_id,
300,
100,
sysdate,
null);
--------------insert in sct_sme _work flow--------
begin
select sca.id,sca.request_date into XXCUSTOM_ASSESSMENT_ID,xxstartdate
from sct_custom_assessment sca
where sca.workflow_id = xxworkflow_id and sca.type = 300 and sca.status= 100;
exception when no_data_found then
XXCUSTOM_ASSESSMENT_ID:=null;
xxstartdate:=null;
end;
begin
select spaw.sme_approval_period
into xxexpiry
from sct_profcncy_assessment_wrkflw spaw
where spaw.proficiency_level_id= XPROFICIENCY_ID;
exception when no_data_found then
xxexpiry:=null;
end;
xxexpirydate := xxstartdate + xxexpiry;
begin
select cp.manager_id
into XXMANAGER_ID
from cmt_person cp
where cp.id = XLEARNER_ID;
exception when no_data_found then
XXMANAGER_ID:=null;
end;
insert into sct_sme_workflow
(ID,
CUSTOM_ASSESSMENT_ID,
expiry_date,
TRIP_COUNT,
COMMENTS,
SEQUENCE,
ACTION_FLOW,
MGR_ACTION_STATUS,
SME_APPROVAL_STATUS,
REQUESTEE,
USER_ID,
MANAGER_ID,
SME_ID,
start_date,
completion_date)
values
(('smewf' ||
lpad(ltrim(rtrim(to_char(scs_sme_workflow_seq.nextval))), 15, '0')),
XXCUSTOM_ASSESSMENT_ID,
xxexpirydate,
1,
null,
1,
'LS',
null,
600, /*New Status added for L-S wrkflw ,ref:CR 1861622 */
'L',
XLEARNER_ID,
XXMANAGER_ID,
xxsmeid,
sysdate,
null);
else
-------------Update the Cmpletion date for the previovs trancation
update sct_sme_workflow ssw
set ssw.completion_date=sysdate
where ssw.custom_assessment_id = xxassesmentid and
ssw.sequence in
(select max(ssw1.sequence) from sct_sme_workflow ssw1 where ssw1.custom_assessment_id = xxassesmentid);
--new entry with updated old data
insert into sct_sme_workflow
(ID,
CUSTOM_ASSESSMENT_ID,
expiry_date,
TRIP_COUNT,
COMMENTS,
SEQUENCE,
ACTION_FLOW,
MGR_ACTION_STATUS,
SME_APPROVAL_STATUS,
REQUESTEE,
USER_ID,
MANAGER_ID,
SME_ID,
START_DATE,
COMPLETION_DATE
)
select ('smewf' ||
lpad(ltrim(rtrim(to_char(scs_sme_workflow_seq.nextval))), 15, '0')),
sme.custom_assessment_id,
sme.expiry_date,
sme.trip_count,
null,
nvl(sme.sequence, 0) + 1,
'LS',
null,
600,
'L',
sme.user_id,
sme.manager_id,
xxsmeid, /*sme.sme_id*/ /*Commented to Fix the bug wherein if a new SME is selected it should go that SME and nt the prev SME in the wrkflw*/
sysdate,
null
from sct_sme_workflow sme
where sme.custom_assessment_id = xxassesmentid and
sme.sequence =
(select max(ssw.sequence)
from sct_sme_workflow ssw
where ssw.custom_assessment_id = xxassesmentid);
/* select sca.workflow_id
into xx_workflowId
from sct_custom_assessment sca
where sca.id = xxassesmentid and sca.type = 300;*/
-----Triggred Notification
commit;
end if;
fgp_notification_post(xxworkflow_id,101449,sysdate,null,'domin000000000000001');
commit;
END;
Thanks 🙂
December 27, 2012 at 3:44 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Easiest temporary fix here would be to disable the button once clicked on and only re-enable it after the execution is complete.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2012 at 5:12 am
Provide table structures..
December 27, 2012 at 7:32 am
Of course since this is Oracle and not t-sql you will probably have much better luck on an Oracle forum getting help with performance. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2012 at 9:39 pm
Disabling the button on click will be easier 🙂 Thanks a lot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply