SP Query Bottleneck

[walden]

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
************************************************** ****************/

[bilal]

Your stored procedure clearly has a classic performance bottleneck caused by row-by-row processing. The use of a cursor (tp_cde_csr) is the biggest issue?this forces SQL Server to process each record one at a time instead of using efficient set-based operations.

You?re also running multiple EXISTS checks and SELECT queries inside the loop, which multiplies the workload dramatically for large datasets. On top of that, the LOCK TABLE ... IN EXCLUSIVE MODE blocks other operations and can slow things further.

Another bottleneck is the repeated lookups on tables like R84_COMP_ADDRESS and R79_BROKER_ADDR without ensuring proper indexing on columns like CO_SHORTNME or BROKER_NO + BROKER_CODE.

To improve performance, replace the cursor with a set-based INSERT using JOINs, and pre-join all required tables in a single query. Also, add proper indexes and avoid concatenation in WHERE clauses.

In short, your long runtime (9+ hours) is expected with this design?the main bottleneck is the cursor + repeated queries pattern. Converting this to set-based logic should reduce execution time drastically.

**Links removed by Site Administrator so it doesn't look like you're spamming us. Please don't post them again.**


**Also - Please note that this thread was started on 09-02-2002.**