TSQL help request - More efficient loop code than this?

Posted on 2009-06-30
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)
	--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

Open in new window

Question by:CreepyD
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 60

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


Expert Comment

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:

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.


LVL 60

Accepted Solution

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 
	- CASE @Season 
		WHEN '1' THEN -1 
		ELSE 1 
	END * (TempVar / 2);

Open in new window

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

LVL 60

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,

Assisted Solution

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

Open in new window


Author Closing Comment

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  :)
LVL 60

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.


Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot tables in SQL 1 45
calculate days away 11 61
SQL Query - Multiple match on field with no extras 7 36
How to add an Index to a date/time field? 15 41
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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