We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

TSQL help request - More efficient loop code than this?

CreepyD
CreepyD asked
on
Medium Priority
181 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

Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

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.

--#


Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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  :)
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.