How to handle large table?
-
Hi,
I have a table in sql server with nearly 30000 records. I have to iterate through each record and based on the value of the one of the fileds in that table, I need to update the same record (need to update the one of the fileds in that table). up to 1593 records its doing fine later i am getting the following exception:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
I have written the following stored procedure to do the same and i am using the Microsoft application blocks to call the 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 JAN
-- 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 JAN 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 JAN with (nolock) where RowID > @CurrentRowID
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 JAN with (nolock) where RowID = @NextRowID
if @RecType = 'D'
begin
select @result = SSN from member where SSN = @SSN
if len(@Result) > 0
begin
--PASSED RULE 1
update JAN set status = 1 where F2= @Result
end
end
end
GO
Thanks,
Krishna
-
You can replace this whole proc with a single query.
What exactly are you trying to do?
-
My tables has 12 fields
For each record I have to test the second filed data with some other data and based on the result (both are same or not) i need to update the last filed status filed with some integer value.
like filed1data == "somevalue"
update same the same record (but last field) with some integer value
hope i am clear anyway thanks for immediate reply...
-
Actually you're not clear enough.
Can you post the table definition, some sample data along with the expected result you want from this procedure?
-
Remi - This is what I get from reading the procedure (& I could be way off mark...)
something like:
Update Jan
Set Status = 1
Where F1 = 'D' AND Len(SSN) > 0
I'm sure I'm missing something somewhere - Pranitha - is this what you want to do ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
That's what I was figuring... maybe with an exists/join operation on the other table.
But there might be something else in the proc that we're missing. She's setting a lot of variables that she's not using in that code... That can't be set for no reason.
-
Hi,
In simple i can write this using cursors as the following: even with cursors alos i am getting the same exception (Time out exception), as cursors are slow in processing i have replaced the below code using while loop:
CREATE PROCEDURE [dbo].[ApplyRules]
AS
DECLARE @RECTYPE NVARCHAR(255)
DECLARE @SSN FLOAT
DECLARE @REPUNIT FLOAT
DECLARE @PREMAMT FLOAT
DECLARE @DECCODE FLOAT
DECLARE @RESULT VARCHAR(255)
DECLARE @ENTITLEMENTID INT
SET NOCOUNT ON
DECLARE GETDATA CURSOR
FOR
SELECT F1,F2,F7,F8,F12 FROM JAN
OPEN GETDATA
FETCH NEXT FROM GETDATA into @RECTYPE,@SSN,@REPUNIT,@PREMAMT,@DECCODE
WHILE @@FETCH_STATUS = 0
BEGIN
--APPLY RULE 1
IF @RECTYPE = 'D'
BEGIN
SELECT @RESULT = SSN FROM MEMBER WHERE SSN = @SSN
IF LEN(@RESULT) > 0
BEGIN
--PASSED RULE 1
UPDATE JAN SET STATUS = 1 WHERE F2 = @RESULT
END
END
FETCH NEXT FROM GETDATA into @RECTYPE,@SSN,@REPUNIT,@PREMAMT,@DECCODE
END
GO
-
Not sure you really need a cursor for this. There's probably a lot more to it.
--SELECT F1,F2,F7,F8,F12 FROM JAN
--FETCH NEXT FROM GETDATA into @RECTYPE,@SSN,@REPUNIT,@PREMAMT,@DECCODE
--IF @RECTYPE = 'D'
--SELECT @RESULT = SSN FROM MEMBER WHERE SSN = @SSN
-- IF LEN(@RESULT) > 0
-- BEGIN
--PASSED RULE 1
-- UPDATE JAN SET STATUS = 1 WHERE F2 = @RESULT
UPDATE j1
SET j1.STATUS = 1 -- UPDATE JAN SET STATUS = 1
FROM
JAN j1
INNER JOIN MEMBER m1 ON j1.F2 = m1.SSN
--Combo of "SELECT @RESULT = SSN FROM MEMBER WHERE SSN = @SSN"
-- AND "--SELECT @RESULT = SSN FROM MEMBER WHERE SSN = @SSN"
WHERE
j1.F1 = 'D' --IF @RECTYPE = 'D'
AND LEN(m1.SSN)>0 --IF LEN(@RESULT) > 0
Just as a couple final notes:
1. Since your joins are on SSN, you will want to have that indexed if possible. You might also want to consider an index on j1.F1 if there is sufficient selectivity for an index.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Well, just to assess your timeout problem, it is very unlikely that it is because of sql server. I think it is your calling enviroment that is timing out... Try to set the timeout value to higher then it is right now. Although I would recommend any of the above solutions to make your query more efficient...
//Hanslindgren
-
Actually it's the other way around. Change the query, if there's still a timeout, then correct the timeout limit on the apllication side
. -
I'd say yes and no
If you increase the timeout it will probably succeed (but it will still take a long time) since the query is working (agreeably it consumes alot of time to finish but the query itself should not fail). A slow solution is not wrong it just is an unoptimized one
-
She's going to Pluto to get some ice. I choose to go in my Freezer. Takes less time.
.It's her choice now.
-
Hehe. I'll be heading down to the pub to get my ice
Seeing that it is end of the working week now -
Same here... working only 4 hours today. So I only have 26 minutes to go now
. -
Before you guys go and knock yourselves out (:sick
...please answer this for me...Remi (not a stupid question - only a very confused one)..
what is the poster (pranitha) trying to do besides updating a "status" field with 1 when the @Rectype is 'D' and an SSN exists (len > 0) ????
Where are all the other variables being used ???
What am I missing - it's Friday for me too and I want all the answers so they don't keep niggling at me all weekend long.....
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply