Looping Through A Record Set - 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

SQL Server Development (2000)

Looping Through A Record Set

Author

Topic

breesy
Starting Member

3 Posts

Posted - 2003-07-18 : 11:36:20

Hello,

I have a website with around 5000 members and every 30 minutes I recalculate everyones rank using the following asp script:

SQL = "SELECT Username FROM Members WHERE Username <> 'admin' AND League = 1 ORDER BY Cash + Bank DESC, Username DESC"
SET RANKINGS = MyConn.Execute(SQL)

counter = 1

Do Until RANKINGS.EOF

SQL = "UPDATE Members SET Rank = " & Counter & " WHERE Username = '" & RANKINGS("Username") & "'"
MyConn.Execute(SQL)

counter = counter + 1
RANKINGS.MoveNext
Loop

RANKINGS.Close
SET RANKINGS = Nothing

This works perfectly the only problem is it executes 5000 sql queries and takes 110 seconds to complete...

Is there a way to shrink this down to one query? Or can someone please help me create a ms sql stored procedure for this type of thing... I have been reading through dozens of tutorials but still haven't picked up on how I would do the same type of thing in a stored procedue...

Thanks in advance...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-18 : 11:48:07

Yikes ... breesy, we can definitely help you out.

Processing like that should be done by SQL server .... you have way too many round trips happening ....

create stored procedure execute that stored proc from your web page.

Here's one way to write it, there are many other ways:
----------

create procedure UpdateRanks
AS
select username, identity(1,1) as Rank
into #tmp
from Members
where username <> 'admin' and league = '1'
order by cash + bank DESC, Username DESC

update members
set rank = (select Rank from #tmp where members.username = #tmp.username)

drop table #tmp
------------

then, using ADO:

conn.execute "UpdateRanks"

should run in a matter of seconds .....

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-18 : 11:54:42

by the way -- if you don't use a stored proc, do it this way:

counter =0
Do Until RANKINGS.EOF
counter = counter + 1
RANKING("Rank") = counter
RANKINGS.UPDATE
RANKINGS.MoveNext
Loop
RANKINGS.Close
SET RANKINGS = Nothing

just make sure you open RANKINGS as a read/write connection (I forget what they call it in ADO -- a dynaset ? )

even THAT will be infinitely faster than 5000 update statements...

- Jeff

breesy
Starting Member

3 Posts

Posted - 2003-07-18 : 12:39:49

First of all, thank you for your extremely useful reply.

With the stored proc. when i try to add it i get the following error:

Server: Msg 170, Level 15, State 1, Procedure UpdateRanks, Line 3
Line 3: Incorrect syntax near '1'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-18 : 12:42:29

quote:

First of all, thank you for your extremely useful reply.

With the stored proc. when i try to add it i get the following error:

Server: Msg 170, Level 15, State 1, Procedure UpdateRanks, Line 3
Line 3: Incorrect syntax near '1'.

Then remove the single quotes around 1.

Tara

breesy
Starting Member

3 Posts

Posted - 2003-07-18 : 20:30:01

Its on line 3, this one: select Username, identity(1,1) as Rank

I tried removing the single quotes, it didn't make the error go away.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-19 : 02:56:20

>> Its on line 3, this one: select Username, identity(1,1) as Rank

The IDENTITY function requires three arguments: datatype, seed, increment

So you need to change the line to:

SELECT UserName, IDENTITY(int, 1, 1)...

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources