October 12, 2009 at 9:44 pm
Dear All,
I have store procedure that run slowly and take two hour, i use sql server 2005 and CPU intel Xeon quad core, 3GB RAM,
De
Table Apr_CIFSeqData has 1 index , and 1 Primary Key
Table BS_FacilityNumbering has 1 Primary Key
will Insert about 300.000 rows into table BS_FacilityNumbering
question : why 2 hour ?
thank's for help
Ipan's
Here's my SP :
CREATE procedure sp_bsFacilityNum
as
begin
set transaction isolation level serializable
SET NOCOUNT ON
Declare @CustCode varchar(10)
Declare @FacilityType varchar(3)
Declare @CurrSeqNo_0 numeric
Declare @CurrSeqNo numeric
update Apr_CIFSeqData set SeqNo = '00' where isnumeric(SeqNo) = '0'
declare curr cursor fast_forward for
select CIFNo ,FacilityType
,max(convert(numeric, SeqNo)) as CurrSeqNo --guard non-numeric SeqNo
from Apr_CIFSeqData
group by CIFNo ,FacilityType
open curr
fetch next from curr into
@CustCode ,@FacilityType ,@CurrSeqNo
while @@fetch_status = 0
begin
if exists(
select CIFNo
from Aprova_App.dbo.BS_FacilityNumbering BS_FacilityNumbering WITH (NOLOCK)
where CIFNo = @CustCode
and FacilityType = @FacilityType
)
begin
select @CurrSeqNo_0 = CurrSeqNo
from Aprova_App.dbo.BS_FacilityNumbering
where CIFNo = @CustCode
and FacilityType = @FacilityType
if @CurrSeqNo_0 < @CurrSeqNo
begin
--error ,aprova starts from wrong (smaller) starting point
print 'error ,aprova starts from wrong (smaller) starting point'
print @CustCode + '|' + @FacilityType + '|' + convert(varchar(2) ,@CurrSeqNo) + '|' + right('00' + convert(varchar(2),@CurrSeqNo), 2)
print '------------------------'
end else if @CurrSeqNo_0 = @CurrSeqNo
begin
--correct ,which generated by Aprova will be downloaded back into Aprova (synch)
print 'correct ,which generated by Aprova will be downloaded back into Aprova (synch)'
end else if @CurrSeqNo_0 > @CurrSeqNo
begin
--counter is up for this CIFNo tapi belum cair di AB,
--probably located after Approval, before Hand-off
print 'counter is up for this CIFNo tapi belum cair di AB ,probably located after Approval, before Hand-off'
print @CustCode + '|' + @FacilityType + '|' + convert(varchar(2) ,@CurrSeqNo) + '|' + right('00' + convert(varchar(2),@CurrSeqNo), 2)
print '------------------------'
end
end else
begin
insert into Aprova_App.dbo.BS_FacilityNumbering
(
CIFNo ,FacilityType ,CurrSeqNo ,SeqNo
)values
(
@CustCode ,@FacilityType ,@CurrSeqNo ,right('00' + convert(varchar(2),@CurrSeqNo), 2)
)
--print @CustCode + '|' + @FacilityType + '|' + convert(varchar(2) ,@CurrSeqNo) + '|' + right('00' + convert(varchar(2),@CurrSeqNo), 2)
end
fetch next from curr into
@CustCode ,@FacilityType ,@CurrSeqNo
end
close curr
deallocate curr
SET NOCOUNT OFF
end
October 13, 2009 at 1:53 am
Please keep your issue to one post.
http://www.sqlservercentral.com/Forums/Topic801931-360-1.aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply