how to handle large tables (nearly 30000 records)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to handle large tables (nearly 30000 records)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-16 : 08:08:41

krishna_ravi writes "Hi,

I have a table with nearly 30000 records. I need to iterate through each record and based on the second field data i have the update the same record with some data. I am using while loop to iterate though each loop. Its doing fine upto 1000 records then its giving "Time out" exception.

Here is my stored procedure:

CREATE PROCEDURE [dbo].[ApplyRules]
AS

SET NOCOUNT ON

--Declare all variables

declare @RecType nvarchar(255)
declare @SSN int
declare @ReportingUnit float
declare @PremiumAmt float
declare @DeductionCode float
declare @Result varchar(255)
declare @EntitlementID int
declare @ReturnCode int
declare @NextRowID int
declare @CurrentRowID int
declare @LoopControl int

-- Initialize variables
select @LoopControl = 1
select @NextRowID = min(RowID) from JAN55

-- Make sure the table has data.
if isnull(@NextRowID,0) = 0
begin
select 'No data in found in table!'
return
end

-- Retrieve the first row
select @CurrentRowID = RowID,@RecType = F1,@SSN = F2,@ReportingUnit = F7,@PremiumAmt = F8,@DeductionCode=F12 from JAN55 where RowID = @NextRowID

-- start the main processing loop.
while @LoopControl = 1

begin
-- Reset looping variables.
select @NextRowID = NULL
-- get the next RowID
select @NextRowID = min(RowID) from JAN55 where RowID > @CurrentRowID

-- did we get a valid next row id?
if isnull(@NextRowID,0) = 0
begin
break
end
-- get the next row.
SELECT @CurrentRowID = RowID,@RecType = F1,@SSN = F2,@ReportingUnit = F7,@PremiumAmt = F8,@DeductionCode=F12 from JAN55 where RowID = @NextRowID
if @RecType = 'D'
begin
select @result = SSN from member where SSN = @SSN
if len(@Result) > 0
begin
--PASSED RULE 1
update JAN55 set status = 1 where F2= @Result
end
end
end
GO"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-16 : 08:15:03

Wow, that's doing things the hard way.

UPDATE JAN55
SET status = 1
FROM
JAN55
INNER JOIN member ON member.ssn = JAN55.F2

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources