loop in update - SQL Server Forums

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

Transact-SQL (2000)

loop in update

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-22 : 08:32:31

Hi,

Here i am using the following query, but it takes more time to finish the process,

DECLARE @CTR INT
DECLARE @COUNT INT
DECLARE @EmpNum EMPNUM

SELECT @CTR = 1

SELECT @COUNT = COUNT(*)
FROM Payroll_Readiness_Table1(Nolock)

SET ROWCOUNT 1

WHILE @CTR <= @COUNT
BEGIN
UPDATE Payroll_Readiness_Table1
SET Flag = @CTR
WHERE Flag = 0
AND H01_EMP_Num = (
select Min(H01_emp_Num)
from Payroll_Readiness_Table1
where Flag = 0
)
SELECT @CTR = @CTR + 1

END
SET ROWCOUNT 0

i just replaced like

UPDATE Payroll_Readiness_Table1
SET Flag = @CTR ,@CTR = @CTR+1
WHERE Flag = 0

is it correct..?

please help me

thanks in advance
KK

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 08:37:19

depends on what you want.
The loop is ordered whareas the sattement isn't so they may end up with different values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-22 : 08:42:32

thanks for ur suggession

U r Right. But in that table the emp_num is in clustered index. so at the time insert it'will sort the records. so is this necessary to use the sub query here..?

KK

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 08:47:02

"But in that table the emp_num is in clustered index. so at the time insert it'will sort the records"

I don't believe that is the case [but no doubt someone will correct me if I'm wrong!], in particular in the scenario where part of the table you are updating is already cached in memory from a previous, unrelated, query, and the rest of the data for the query is not in the cache.

Unfortunately this is one of those things which will work the way you want such a large proportion of the time that the one time it doesn't will be a very hard bug to reproduce!

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 08:47:05

It'll probably (maybe?) be ok but isn't guaranteed.
UPDATE Payroll_Readiness_Table1
SET Flag = (select count(*) from Payroll_Readiness_Table1 t2 where t2.H01_emp_Num <= t.H01_emp_Num and t2.Flag = 0)
from Payroll_Readiness_Table1 t
where Flag = 0

But that will probably be slower than the loop.
In fact it probably won't work - I guess the set rowcount 1 is to cater for duplicate H01_emp_Num's.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 08:48:58

I guess according to the code which you have posted you need to update the field in numeric order
if you have primary key in the table then i guess you can update it without using the loop

UPDATE T1
SET Flag = A.CTR From Payroll_Readiness_Table1 T1 Inner Join
(Select PK,(Select Count(1) From Payroll_Readiness_Table1 Tbl1 Where Tbl1.PK >= Tbl2.PK) As CTR
From Payroll_Readiness_Table1 Tbl2) As A On A.Pk = T1.Pk
WHERE Flag = 0

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 08:50:05

I would make a temporary table with an IDENTITY and the PK columns, and then

INSERT INTO @TempTable (PKCol1, PKCol2, ...)
SELECT PKCol1, PKCol2 ...
FROM MyTable
ORDER BY SortCol1, SortCol2 ...

and then UPDATE joining to the @TempTable and using the Identity column to set the sequence column in the target table.

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-22 : 08:50:12

Yes Kristen! No am doing the big proportion ly that Payroll_Readiness_Table1 has 6,000,000 records. so the loop will be continued upto 6,000,000 times. that's the big problem for me.

please give ur suggession
KK

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 08:51:52

I would look at why this value is needed and see if you can change the process.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-22 : 08:54:22

there is no primary key for the table.it's not a situation if i put primary key also.
my head is now going to be explode ...!

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 09:08:48

Why not just add an Identity column to the table? (I suppose that won't be in any particular order for the existing rows, but if you only want a unique value for each row, rather than a number for the rows in a specific sequence, then that would be enough perhaps?)

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 09:15:34

How about somthing like this..

Select Idenity(Int,1,1) As PK, * Into Payroll_Readiness_Table_Bak From
Payroll_Readiness_Table1

Then from the bak update the field flag using some joins or etc..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-22 : 10:03:20

Thanks chiragkhabaria. I Just followed ur suggession. i got so many solution Thanks for all of your valuable solution and your valuable time

Thanks
KK

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 10:49:16

For 6,000,000 rows I would do the UPDATE in batches of probably around 100,000 per go.

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-22 : 10:55:20

It is a user defined process and purely it's secured from end user. if we are going to be batch process means, we can meet lot of issues regarding the payroll process time.

Thanks
KK

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 11:06:05

By "batch" I just meant "loop".

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources