SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hanging WHILE loop


Hanging WHILE loop

Author
Message
tombiagioni1983
tombiagioni1983
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 26
Hello Everyone,

My name is Tom and I'm studying for my Data Platforms MCSE, its still early days so bear with me.

I'm creating my own practice database while I'm studying and have hit a snag because I, naturally, have been venturing into uncharted territories. For context, the database I'm creating is an Items database for a video game. What I'm trying to do is create a join table between Chests and Items and populate it.

Here is the code:

USE DiabloLootSystemClone
Go


--Set Variables for randomizing Information, et al
DECLARE @localChestTier int, @ItemLimit int, @counter int,@ItemCounter int



SET @counter = 1 -- outter chest counter
SET @ItemCounter = 1 -- inner Items in each chest counter

WHILE @counter <= 1000 -- create 1000 chests full of items

SET @localChestTier = (SELECT Chests.ChestTier FROM Chests WHERE Chests.ChestID = @counter) -- Pulling item quality base line... TODO: this seems very rigid, examine better methods of implementation
SET @ItemLimit = (ABS(CHECKSUM(NEWID())) % (@localChestTier * 2)) + 1 -- double base tier for item cap


BEGIN
WHILE @ItemCounter <= @ItemLimit
INSERT ChestItems --Join Table
SELECT
--ChestItemsID Primary Key Auto incremented
@counter,--ChestID from Chests
(SELECT TOP 1 Items.ItemID FROM Items WHERE Items.ItemTier = @localChestTier)--ItemID from Items

SET @ItemCounter +=1 -- next item

SET @counter += 1 -- next chest
END



The Query hangs for a minute plus before anything seems to happen, and I'm scratching my head as to why... Can anybody help?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61507 Visits: 13297
The reason why it is slow is probably the excessive looping.
SQL Server is optimized to do set-based queries, i.e. all rows at the same time instead of a row at a time.

If you could explain a bit more what you are trying to do with the code (and maybe provide some sample data), the code can be rewritten.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1686 Visits: 1099
As suggetsed by Koen it may be because of loops you are creating.
Why don't you read this excellent article of Tally table from Jeff to avoid loop.

The "Numbers" or "Tally" Table: What it is and how it replaces a loop

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40005 Visits: 19449
You are missing the increment of the counter, the code never hits it, improved code below
Cool
--Set Variables for randomizing Information, et al
DECLARE @localChestTier int, @ItemLimit int, @counter int,@ItemCounter int



SET @counter = 1 -- outter chest counter
SET @ItemCounter = 1 -- inner Items in each chest counter

WHILE @counter <= 1000 -- create 1000 chests full of items
BEGIN
SET @localChestTier = 1; -- (SELECT Chests.ChestTier FROM Chests WHERE Chests.ChestID = @counter) -- Pulling item quality base line... TODO: this seems very rigid, examine better methods of implementation
SET @ItemLimit = 10 --(ABS(CHECKSUM(NEWID())) % (@localChestTier * 2)) + 1 -- double base tier for item cap

BEGIN
WHILE @ItemCounter <= @ItemLimit
BEGIN
INSERT INTO ChestItems --Join Table
SELECT
--ChestItemsID Primary Key Auto incremented
@counter,--ChestID from Chests
(SELECT TOP 1 Items.ItemID FROM Items WHERE Items.ItemTier = @localChestTier)--ItemID from Items

SET @ItemCounter +=1 -- next item
END
SET @counter += 1 -- next chest
END
END


Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61507 Visits: 13297
Eirikur Eiriksson (5/7/2014)
You are missing the increment of the counter, the code never hits it, improved code below
Cool


BEGIN and END, your best friends in TSQL :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40838 Visits: 20000
WHILE affects only the statement immediately afterwards. If this statement is a batch defined by BEGIN and END, then the statements in the batch will be processed until the WHILE condition fails. In your case, WHILE was immediately followed by a TSQL statement. This single statement would run until manually stopped.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
tombiagioni1983
tombiagioni1983
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 26
Thanks everyone for the quick responses and the helpful attitude! I come from a C# programming background where iterative loops aren't as costly, So i'll definitely be reading that article! I'll add in the additional BEGIN/END statements and report back.

Thanks again everyone!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41350 Visits: 19815
This is just a guess on what you might be trying to do, just a lot simpler and (hopefully) faster.
It inserts everything at once instead of going one item at a time.


INSERT ChestItems
SELECT c.ChestID
,i.ItemID
FROM Chests c
CROSS APPLY (SELECT TOP ((ABS(CHECKSUM(NEWID())) % (c.ChestTier * 2)) + 1)
Items.ItemID
FROM Items
WHERE Items.ItemTier = Chests.ChestTier) i
WHERE c.ChestID <= 1000



It might not work as you need, but it's an example of how you could do it.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
tombiagioni1983
tombiagioni1983
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 26
Thanks everybody for the help! Here is what I ended up with:

USE DiabloLootSystemClone
Go



DECLARE @counter int

SET @counter = 1

WHILE @counter <= 1000
BEGIN

INSERT ChestItems
SELECT c.ChestID
,i.ItemID
FROM Chests c
CROSS APPLY (SELECT TOP ((ABS(CHECKSUM(NEWID())) % (c.ChestTier * 2)) + 1)
Items.ItemID
FROM Items
WHERE Items.ItemTier = c.ChestTier) i
WHERE c.ChestID = @counter



SET @counter += 1

END
GO



I was having a problem setting the foreign keys on the Items and Chests tables and I'd appreciate some feedback on my implementation. I just ended up creating triggers to set them from the inserted table:

CREATE TRIGGER uAddItemsFK ON ChestItems
AFTER INSERT
AS
BEGIN
UPDATE Items
SET
Items.ChestItemsID = i.ChestItemsID
FROM
Items it
JOIN inserted i
ON it.ItemID = i.ItemID
END
GO

CREATE TRIGGER uAddChestsFK ON ChestItems
AFTER INSERT
AS
BEGIN
UPDATE Chests
SET
Chests.ChestItemsID = i.ChestItemsID
FROM
Chests c
JOIN inserted i
ON c.ChestID = i.ChestID
END
GO




Thanks Again, everybody!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41350 Visits: 19815
Your query looks just like mine but using a while loop instead of a single operation. Why would you do that.

Without DDL and sample data, I can't be sure why are you using those triggers. I can only be sure about one thing, you don't need 2 as they can be part of the same trigger.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search