I was wondering if anyone out there could take a look at below store procedures to tell me why it takes too long to run? The last time I run started on 23 August 2002 at 9:15:41 (pm) and finished at 24 August 2002 at 6:57:50 (am)over 9 hours that is not right is it? Please any help and advise will be welcome.



CREATE PROCEDURE sp_load_third_party_address;1

@ACCT_MONTH integer,
@ai_print integer = 0

AS

/************************************************** ******************

Name : sp_load_third_party_address
Author : Neil Tyler

In : @ACCT_MONTH Current Accounting Period
Out : None
Return : 1 Success
: 0 Failure
Calls : None
Description : Populates third_party_address on the Data Warehouse

Run Time :

Admendments :

Date By Description
--------- ----------- -----------
-Oct-2001 N.Tyler Had to re-write due to changes on data model
************************************************** ******************/

begin
/************************************************** *****************
Declarations
************************************************** ******************/

/* Local Status Variables */

declare @li_err_cnt integer,
@li_row_cnt integer,
@ldt_run_date datetime,
@ldt_end_date datetime,
@li_success integer,
@li_upd_cnt integer,
@li_ins_cnt integer,
@lvc_sp_name varchar(50),
@lvc_tmp varchar(150),
@li_error integer,
@lvc_err_desc varchar(250),
@li_del_cnt integer

/* Local Variables */

declare @lc_src_sys char(4),
@lc_addr_type char(1),
@lc_cty_cde char(3),
@lc_ctry_cde char(3),
@lc_post_cde char(7),
@lc_town char(25),
@lc_street char(25),
@lc_bldg_nme char(25),
@lc_dept_cde char(1),
@lc_addr_desc char(30),
@lb_rec_found bit

/* Cursor Variables */

declare @lc_tp_cde char(15)

/* Initialisation */

select @lvc_sp_name = "sp_load_third_party_address"
select @lc_src_sys = "LMDB"
select @li_success = 0
select @li_ins_cnt = 0
select @li_error = 0
select @lvc_err_desc = " "
select @lb_rec_found = 0

/* Create tp_cde_csr Cursor */

declare tp_cde_csr cursor for
select third_party.tp_cde
from orlando..third_party third_party
where third_party.tp_cde <> &#34; &#34;


/* Log Start Time */

select @ldt_run_date = getdate()
select @lvc_tmp = &#34;Started at: &#34; + Convert(varchar(25), @ldt_run_date,109)
print @lvc_tmp

/************************************************** ***************
Processing
************************************************** ****************/

if @ai_print = 1
begin
Print &#34;Position 1: Variables set&#34;
end

begin transaction
begin
lock table orlando..third_party_address in exclusive mode
open tp_cde_csr

fetch tp_cde_csr into @lc_tp_cde

while @@sqlstatus = 0
begin
if exists (select 1 from lmdb..R84_COMP_ADDRESS R84
where @lc_tp_cde = R84.CO_SHORTNME)
begin
select @lb_rec_found = 1,
@lc_addr_type = isnull (R84.ADD_TYPE, &#34; &#34,
@lc_cty_cde = isnull (R84.COUNTY_CODE, &#34;UNK&#34,
@lc_ctry_cde = isnull (R84.COUNTRY_CDE, &#34;UNK&#34,
@lc_post_cde = isnull (R84.POST_CODE, &#34;UNK&#34,
@lc_town = isnull (R84.TOWN, &#34;UNK&#34,
@lc_street = isnull (R84.STREET, &#34;UNK&#34,
@lc_bldg_nme = isnull (R84.BLDNG_NME, &#34;UNK&#34,
@lc_dept_cde = isnull (R84.DEPT_CODE, &#34; &#34,
@lc_addr_desc = isnull (R85.ADD_TYP_DESC, &#34;UNK&#34
from lmdb..R84_COMP_ADDRESS R84,
lmdb..R85_ADDRESS_TYPE R85
where @lc_tp_cde = R84.CO_SHORTNME
and R84.ADD_TYPE *= R85.ADD_TYPE

if @ai_print = 1
begin
select @lvc_tmp = &#34;tp_cde : &#34; + @lc_tp_cde + &#34; found in R84_COMP_ADDRESS&#34;
print @lvc_tmp
end
end
else if exists (select 1 from lmdb..R79_BROKER_ADDR R79
where @lc_tp_cde = R79.BROKER_NO + R79.BROKER_CODE)
begin
select @lb_rec_found = 1,
@lc_addr_type = isnull (R79.ADD_TYPE, &#34; &#34,
@lc_cty_cde = isnull (R79.COUNTY_CODE, &#34;UNK&#34,
@lc_ctry_cde = isnull (R79.COUNTRY_CDE, &#34;UNK&#34,
@lc_post_cde = isnull (R79.POST_CODE, &#34;UNK&#34,
@lc_town = isnull (R79.TOWN, &#34;UNK&#34,
@lc_street = isnull (R79.STREET, &#34;UNK&#34,
@lc_bldg_nme = isnull (R79.BLDNG_NME, &#34;UNK&#34,
@lc_dept_cde = isnull (R79.DEPT_CODE, &#34; &#34,
@lc_addr_desc = isnull (R85.ADD_TYP_DESC, &#34;UNK&#34
from lmdb..R79_BROKER_ADDR R79,
lmdb..R85_ADDRESS_TYPE R85
where @lc_tp_cde = R79.BROKER_NO + R79.BROKER_CODE
and R79.ADD_TYPE *= R85.ADD_TYPE

if @ai_print = 1
begin
select @lvc_tmp = &#34;tp_cde : &#34; + @lc_tp_cde + &#34; found in R79_BROKER_ADDR&#34;
print @lvc_tmp
end
end
else
begin
select @lb_rec_found = 0
if @ai_print = 1
begin
select @lvc_tmp = &#34;Did not find any address info for tp_cde : &#34; + @lc_tp_cde
print @lvc_tmp
end
end
if @lb_rec_found = 1
begin

if @ai_print = 1
begin
select @lvc_tmp = &#34;Postion 2: About to insert record: &#34; + @lc_tp_cde
print @lvc_tmp
end

insert into orlando..third_party_address(
tp_cde,
addr_type,
cty_cde,
ctry_cde,
addr_desc,
cde_no,
post_cde,
town,
street,
bldg_nme,
dept_cde,
src_sys,
acc_mth,
load_dte)

select third_party.tp_cde,
@lc_addr_type,
@lc_cty_cde,
@lc_ctry_cde,
@lc_addr_desc,
0,
@lc_post_cde,
@lc_town,
@lc_street,
@lc_bldg_nme,
@lc_dept_cde,
@lc_src_sys,
@ACCT_MONTH,
@ldt_run_date
from orlando..third_party third_party
where @lc_tp_cde = third_party.tp_cde

if @ai_print = 1
begin
select @lvc_tmp = &#34;Position 3: &#34; + @lc_tp_cde + &#34; Inserted&#34;
print @lvc_tmp
end
end

fetch tp_cde_csr into @lc_tp_cde
end
end
/* Rollback if insert fails */

if @@error != 0
begin
raiserror 20001, third_party_address
rollback transaction
select @li_error = @@error
select @lvc_err_desc = &#34;Insert Failure&#34;
end
else
begin
commit transaction
end
deallocate cursor tp_cde_csr

if @ai_print = 1
begin
select @lvc_tmp = &#34;Position 1: &#34; + @lc_tp_cde + &#34;Inserted&#34;
print @lvc_tmp
end

/************************************************** ****************
Error Log
************************************************** ****************/

if @li_error != 0
begin
/* Log End Time */

select @ldt_end_date = getdate()
select @lvc_tmp = &#34;Failed at: &#34; + Convert(varchar(25), @ldt_end_date, 109)
print @lvc_tmp
select @li_success = 1

/* Error Log */

exec sp_populate_sp_err_log @lvc_sp_name, @ldt_run_date,
@lvc_err_desc, @li_error

/* Run Log */

exec sp_populate_sp_run_log @lvc_sp_name, @ldt_run_date,
@ldt_end_date, @li_success,
@li_del_cnt, @li_upd_cnt,
@li_ins_cnt

return -1

end
/************************************************** ****************
Statistics
************************************************** ****************/

/* Log End Time */

select @ldt_end_date = getdate()

select @lvc_tmp = &#34;Finished at: &#34; + Convert(varchar(25), @ldt_end_date, 109)
print @lvc_tmp
select @li_ins_cnt = (select count (*) from orlando..third_party_address)
select @lvc_tmp = &#34;Number of Records Inserted : &#34; + Convert(varchar(25),@li_ins_cnt)
print @lvc_tmp
/* Run Log */


exec sp_populate_sp_run_log @lvc_sp_name, @ldt_run_date,
@ldt_end_date, @li_success,
@li_del_cnt, @li_upd_cnt,
@li_ins_cnt

return 1
end

/************************************************** ****************
End sp_load_third_party_address
************************************************** ****************/