Solved

TSQL help request - More efficient loop code than this?

Posted on 2009-06-30
7
159 Views
Last Modified: 2012-05-07
I have a 'map' in SQL which currently consists of a grid of 100x100 'squares'.
This is for a web-based game I'm toying with making.
The final one would need many more squares, more like 10k x 10k.

My problem is the way I've written the code, it uses a very basic loop, incrementing a number each time through, raising the 'key' by 1 each time.
I do it this way because I want each square to have a random temperature based on that particular squares average temperature.

Is there a more efficient way of coding this so it doesn't have to loop through it 10,000 times? Some way of implementing it so it can update all records at once?
I'm in the process of learning SQL as I go, so really don't know what's possible. Maybe I'm trying to do the impossible here..?

Any help or advice appreciated.
declare @TempAv int

declare @TempVar int

declare @temp int

declare @rain int
 

While @count <= (Select top 1 [num] from Map order by [num] desc)

BEGIN

	--Get Variables for this area

	set @TempAv = (Select [TempAv] from [map] where [num]=@count)

	set @TempVar = (Select [TempVar] from [map] where [num]=@count)
 

	--Set Temperature

	If @Season = '0'

		set @temp = @TempAv + convert(int, 0 + @TempVar * RAND(CHECKSUM(NEWID()))) - (@TempVar / 2)

	Else if @Season = '1'

		set @temp = @TempAv + convert(int, 0 + @TempVar * RAND(CHECKSUM(NEWID()))) + (@TempVar / 2)

	Else if @Season = '2'

		set @temp = @TempAv + convert(int, 0 + @TempVar * RAND(CHECKSUM(NEWID()))) - (@TempVar / 2)

	Else if @Season = '3'

		set @temp = @TempAv - convert(int, 0 + @TempVar * RAND(CHECKSUM(NEWID()))) - (@TempVar / 2)
 

	update Map

	Set [TempNow] = @temp where [num] = @count

END

Open in new window

0
Comment
Question by:CreepyD
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24748799
Try something like this.
+Only variation I saw in the formula was if season was '3', you subtract the latter portion of formula versus add.
+TempAv and TempVar appear to come from same row, so should not need to store in variables first.
update Map

Set [TempNow] = TempAv + CASE @Season WHEN '3' THEN -1 ELSE 1 END * CAST(0 + @TempVar * RAND(CHECKSUM(NEWID()))) - TempVar / 2 AS INT);

Open in new window

0
 
LVL 3

Expert Comment

by:BrightApollo
ID: 24748836
Do you realize you are performing the same set operation no matter what the value of @Season is?  Also, a table definition would be really helpful here.

I don't see any reason why you could write this, given what you have:

UPDATE Map
SET [TempNow] = ([TempAv] - CONVERT(INT, 0 + [TempVar] * RAND(CHECKSUM(NEWID()))) - ([TempVar] / 2))

No loops needed.

Now your problem is going to be computing all of those random numbers for all rows.  We'll get to that later.  See if this works for you.

--#


0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 400 total points
ID: 24748839
Alright, just saw additional difference and a type-o.  Try this:
update Map

Set [TempNow] = TempAv 

	+ CASE @Season 

		WHEN '3' THEN -1 

		ELSE 1 

	END * CAST(0 + @TempVar * RAND(CHECKSUM(NEWID())) AS INT) 

	- CASE @Season 

		WHEN '1' THEN -1 

		ELSE 1 

	END * (TempVar / 2);

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24748854
Missed one last thing which was @TempVar should be just TempVar.  I see BrightApollo saw the same thing I did that you don't need the loop at all. ;)

Hope that helps.

Best regards,
Kevin
0
 
LVL 2

Assisted Solution

by:nikege
nikege earned 100 total points
ID: 24748997
Try something like this:

update [Map] 

set [TempNow] = case @Season when '0' then TempAv  + convert(int, 0 + TempVar * RAND(CHECKSUM(NEWID()))) - (TempVar / 2)

							when '1' then TempAv + convert(int, 0 +  TempVar * RAND(CHECKSUM(NEWID()))) + ( TempVar / 2)

							when '2' then  TempAv + convert(int, 0 +  TempVar * RAND(CHECKSUM(NEWID()))) - ( TempVar / 2)

							when '3' then  TempAv - convert(int, 0 +  TempVar * RAND(CHECKSUM(NEWID()))) - ( TempVar / 2)

							else 0

				end

Open in new window

0
 

Author Closing Comment

by:CreepyD
ID: 31598498
Awesome, thanks a lot guys, I was thinking I'd hit dead end but you've got me over it.  I will use this method for my other sections too!
mwvisa1 your code worked perfectly so I gave you most of the points as you were first, however I ended up using nikege's code which works the same but I can read it easier so a few points for you too  :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24749354
Yes, I had same thought as nikege initially, but seeing the similarities in the formula I used the multiplication by -1 to switch signs via case statement to give you reduced code.  Does make it a little harder to read, but simpler to maintain once you understand as you won't have to change the main calc of @TempVar * RAND(CHECKSUM(NEWID())) or (TempVar / 2) in four different places whenever you change logic.  

Best of luck to you and thanks for the points.

Regards,
Kevin
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now