-
SP Takes Over 9 hours to Run
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 <> " "
/* Log Start Time */
select @ldt_run_date = getdate()
select @lvc_tmp = "Started at: " + Convert(varchar(25), @ldt_run_date,109)
print @lvc_tmp
/************************************************** ***************
Processing
************************************************** ****************/
if @ai_print = 1
begin
Print "Position 1: Variables set"
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, " ",
@lc_cty_cde = isnull (R84.COUNTY_CODE, "UNK",
@lc_ctry_cde = isnull (R84.COUNTRY_CDE, "UNK",
@lc_post_cde = isnull (R84.POST_CODE, "UNK",
@lc_town = isnull (R84.TOWN, "UNK",
@lc_street = isnull (R84.STREET, "UNK",
@lc_bldg_nme = isnull (R84.BLDNG_NME, "UNK",
@lc_dept_cde = isnull (R84.DEPT_CODE, " ",
@lc_addr_desc = isnull (R85.ADD_TYP_DESC, "UNK"
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 = "tp_cde : " + @lc_tp_cde + " found in R84_COMP_ADDRESS"
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, " ",
@lc_cty_cde = isnull (R79.COUNTY_CODE, "UNK",
@lc_ctry_cde = isnull (R79.COUNTRY_CDE, "UNK",
@lc_post_cde = isnull (R79.POST_CODE, "UNK",
@lc_town = isnull (R79.TOWN, "UNK",
@lc_street = isnull (R79.STREET, "UNK",
@lc_bldg_nme = isnull (R79.BLDNG_NME, "UNK",
@lc_dept_cde = isnull (R79.DEPT_CODE, " ",
@lc_addr_desc = isnull (R85.ADD_TYP_DESC, "UNK"
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 = "tp_cde : " + @lc_tp_cde + " found in R79_BROKER_ADDR"
print @lvc_tmp
end
end
else
begin
select @lb_rec_found = 0
if @ai_print = 1
begin
select @lvc_tmp = "Did not find any address info for tp_cde : " + @lc_tp_cde
print @lvc_tmp
end
end
if @lb_rec_found = 1
begin
if @ai_print = 1
begin
select @lvc_tmp = "Postion 2: About to insert record: " + @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 = "Position 3: " + @lc_tp_cde + " Inserted"
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 = "Insert Failure"
end
else
begin
commit transaction
end
deallocate cursor tp_cde_csr
if @ai_print = 1
begin
select @lvc_tmp = "Position 1: " + @lc_tp_cde + "Inserted"
print @lvc_tmp
end
/************************************************** ****************
Error Log
************************************************** ****************/
if @li_error != 0
begin
/* Log End Time */
select @ldt_end_date = getdate()
select @lvc_tmp = "Failed at: " + 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 = "Finished at: " + Convert(varchar(25), @ldt_end_date, 109)
print @lvc_tmp
select @li_ins_cnt = (select count (*) from orlando..third_party_address)
select @lvc_tmp = "Number of Records Inserted : " + 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
************************************************** ****************/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|