SQL 6.5 and locking

[Winston]

Hi

We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.

LE threshold maximum is set to 200.
LE threshold minimum is set to 20.
LE threshold percentage is set to 0.

Locks is set to 0.

I have also included the stored procedure, which we use to update the history table.

As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?

Any help will be greatly appreciated.

Winston


--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000)
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000)
declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)

set nocount on

declare @sex char(1)
declare @huin integer
declare @huan integer
declare @hmailingdate char(8)
declare @mailtot integer
declare @mail12m integer
declare @lastday char(8)

open minihist
fetch next from minihist into @huin,@huan,@hmailingdate
while (@@fetch_status <> -1)
begin

if (@@fetch_status <> -2)
begin

select @mailtot = 1 select @mail12m = 0

/*** Get the gender ***/
select @sex = gender from name where uin = @huin

/*** Calculate if mailed in the last twelwe months ***/
if (@hmailingdate <> null) and (@hmailingdate > &#39;19980524&#39
select @mail12m = @mail12m +1

/*** Get info for this uan from address_summary ***/
select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan

/*** Insert a row into address_summary if doesn&#39;t exist ***/
IF @@rowcount = 0

Insert into address_summary ( uan, uin,mailed_total,Mailed_12months, last_date,last_gender)
values (@huan,@huin,1, @mail12m, @hmailingdate,@sex)
ELSE

/*** compare recency ***/
if (@hmailingdate < @lastday) or (@hmailingdate is null) /* history record is older */

update address_summary
set mailed_total = @mailtot,
mailed_12months = @mail12m
where uan = @huan

else

update address_summary
set uin = @huin,
mailed_total = @mailtot,
mailed_12months = @mail12m,
last_date = @hmailingdate,

last_gender = @sex
where uan = @huan
end

fetch next from minihist into @huin,@huan,@hmailingdate

end

deallocate minihist

[Ray Miao]

You can&#39;t set locks = 0 in sql6.5 since it doesn&#39;t manage number of locks dynamically as sql7 does. The default value is 5000.


------------
Winston at 10/27/99 3:29:28 AM

Hi

We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.

LE threshold maximum is set to 200.
LE threshold minimum is set to 20.
LE threshold percentage is set to 0.

Locks is set to 0.

I have also included the stored procedure, which we use to update the history table.

As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?

Any help will be greatly appreciated.

Winston


--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000)
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000)
declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)

set nocount on

declare @sex char(1)
declare @huin integer
declare @huan integer
declare @hmailingdate char(8)
declare @mailtot integer
declare @mail12m integer
declare @lastday char(8)

open minihist
fetch next from minihist into @huin,@huan,@hmailingdate
while (@@fetch_status <> -1)
begin

if (@@fetch_status <> -2)
begin

select @mailtot = 1 select @mail12m = 0

/*** Get the gender ***/
select @sex = gender from name where uin = @huin

/*** Calculate if mailed in the last twelwe months ***/
if (@hmailingdate <> null) and (@hmailingdate > &#39;19980524&#39
select @mail12m = @mail12m +1

/*** Get info for this uan from address_summary ***/
select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan

/*** Insert a row into address_summary if doesn&#39;t exist ***/
IF @@rowcount = 0

Insert into address_summary ( uan, uin,mailed_total,Mailed_12months, last_date,last_gender)
values (@huan,@huin,1, @mail12m, @hmailingdate,@sex)
ELSE

/*** compare recency ***/
if (@hmailingdate < @lastday) or (@hmailingdate is null) /* history record is older */

update address_summary
set mailed_total = @mailtot,
mailed_12months = @mail12m
where uan = @huan

else

update address_summary
set uin = @huin,
mailed_total = @mailtot,
mailed_12months = @mail12m,
last_date = @hmailingdate,

last_gender = @sex
where uan = @huan
end

fetch next from minihist into @huin,@huan,@hmailingdate

end

deallocate minihist