Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hanging WHILE loop Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 8:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:51 PM
Points: 4, Visits: 13
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?
Post #1568261
Posted Wednesday, May 7, 2014 12:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:09 AM
Points: 13,318, Visits: 10,182
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1568293
Posted Wednesday, May 7, 2014 12:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:10 AM
Points: 559, Visits: 884
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/
Post #1568298
Posted Wednesday, May 7, 2014 1:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 1,309, Visits: 3,749
You are missing the increment of the counter, the code never hits it, improved code below

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

Post #1568300
Posted Wednesday, May 7, 2014 1:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:09 AM
Points: 13,318, Visits: 10,182
Eirikur Eiriksson (5/7/2014)
You are missing the increment of the counter, the code never hits it, improved code below



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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1568302
Posted Wednesday, May 7, 2014 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Post #1568317
Posted Wednesday, May 7, 2014 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:51 PM
Points: 4, Visits: 13
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!
Post #1568405
Posted Wednesday, May 7, 2014 8:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1568502
Posted Sunday, May 11, 2014 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 3:51 PM
Points: 4, Visits: 13
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!
Post #1569632
Posted Sunday, May 11, 2014 11:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1569636
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse