# Database Discussions > Sybase >  SP Takes Over 9 hours to Run

## Ebo

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	=	&#34;sp_load_third_party_address&#34;
	select	@lc_src_sys	=	&#34;LMDB&#34;
	select	@li_success	=	0
	select	@li_ins_cnt	=	0
	select	@li_error	=	0
	select 	@lvc_err_desc	=	&#34; &#34;
	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 :Wink: , 
						@lc_cty_cde 	= isnull (R84.COUNTY_CODE, &#34;UNK&#34 :Wink: ,
						@lc_ctry_cde	= isnull (R84.COUNTRY_CDE, &#34;UNK&#34 :Wink: ,
						@lc_post_cde	= isnull (R84.POST_CODE, &#34;UNK&#34 :Wink: ,
						@lc_town	= isnull (R84.TOWN, &#34;UNK&#34 :Wink: ,
						@lc_street	= isnull (R84.STREET, &#34;UNK&#34 :Wink: ,
						@lc_bldg_nme	= isnull (R84.BLDNG_NME, &#34;UNK&#34 :Wink: ,
						@lc_dept_cde 	= isnull (R84.DEPT_CODE, &#34; &#34 :Wink: ,
						@lc_addr_desc	= isnull (R85.ADD_TYP_DESC, &#34;UNK&#34 :Wink: 
				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 :Wink: , 
					@lc_cty_cde	= isnull (R79.COUNTY_CODE, &#34;UNK&#34 :Wink: ,
					@lc_ctry_cde	= isnull (R79.COUNTRY_CDE, &#34;UNK&#34 :Wink: ,
					@lc_post_cde	= isnull (R79.POST_CODE, &#34;UNK&#34 :Wink: ,
					@lc_town	= isnull (R79.TOWN, &#34;UNK&#34 :Wink: ,
					@lc_street	= isnull (R79.STREET, &#34;UNK&#34 :Wink: ,
					@lc_bldg_nme	= isnull (R79.BLDNG_NME, &#34;UNK&#34 :Wink: ,
					@lc_dept_cde	= isnull (R79.DEPT_CODE, &#34; &#34 :Wink: ,
					@lc_addr_desc	= isnull (R85.ADD_TYP_DESC, &#34;UNK&#34 :Wink: 
				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
**************************************************  ****************/

----------

