Procedure execution taking longer time

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Procedure execution taking longer time

Procedure execution taking longer time

Rate Topic

Display Mode

Topic Options

Author

Message

aditi1.d

Posted Thursday, December 27, 2012 3:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 4:14 AM
Points: 2, Visits: 4

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 Smile

Post #1400537

GilaMonster

Posted Thursday, December 27, 2012 3:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1400541

Aadhar Joshi

Posted Thursday, December 27, 2012 5:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:44 PM
Points: 1,143, Visits: 229

Provide table structures..

Post #1400567

Sean Lange

Posted Thursday, December 27, 2012 7:32 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

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. Smile

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1400618

aditi1.d

Posted Thursday, December 27, 2012 9:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2012 4:14 AM
Points: 2, Visits: 4

Disabling the button on click will be easier Smile

Thanks a lot

Post #1400786

« Prev Topic | Next Topic »

Permissions