May 6, 2014 at 8:18 pm
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?
May 7, 2014 at 12:34 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 7, 2014 at 12:48 am
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[/url]
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 7, 2014 at 1:02 am
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
May 7, 2014 at 1:04 am
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 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 7, 2014 at 2:06 am
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.
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
May 7, 2014 at 6:14 am
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!
May 7, 2014 at 8:27 am
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.
May 11, 2014 at 10:19 am
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!
May 11, 2014 at 11:10 am
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.
May 11, 2014 at 11:32 am
tombiagioni1983 (5/7/2014)
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!
Iterative loops in T-SQL aren't costly. Just the explicit ones are. 🙂
One of the things that most books and courses on SQL Server and, in particular, the T-SQL Language fail miserably in is how to loop. In courses like those for C#, the first thing they do is teach you how to setup the working environment. The next thing they do is teach you how to print "Hello World" to demonstrate that your working environment is setup. After teaching you some syntax, functions, and a couple of other things, they teach you how to write a loop that will count from, say, 1 to 100 because looping is the backbone of every program.
T-SQL isn't any different except you don't have to write the loops. For example, instead of opening a file, checking to see if any rows exist, reading a row, doing something with it, and looping back to see if another row exists, all you have to do in T-SQL is write a SELECT statement. Behind the scenes, SELECT statements aren't a whole lot more than a machine-language-level loop that works much like I just described. Thanks to a fellow by the name of R.Barry Young, I call those hidden, very high performance loops behind every SELECT a "Pseudo Cursor" and, yes, they are controllable.
If you look at Luis' code, it uses such a Pseudo Cursor to replace your explicit cursor (While Loop, in this case). It's MUCH faster than the While Loop because it only has to make 1 execution plan and then SQL Server loops behind the scenes at machine language speeds. With a While Loop, it has to make or reuse an execution plan for each and every iteration even if it decided that there's an execution plan that it can reuse. The While loop also has to take separate locks for every iteration instead of locking what it needs all at once like the SELECT does. All of that takes time.
My best suggestion to make the paradigm shift from writing procedural code in a language like C# is in my signature line below. First, make the realization the T-SQL will build the loops it needs for you and second, like the tagline says, "[font="Arial Black"]Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column[/font]" and let T-SQL figure out how to express what you want done to each row.
The really cool part about all of this is that you don't actually have to use anything from the table to use the table to loop. All you need is the "presence of rows" to get the job done. You'll find an example of this in the Tally Table article that was previously cited above by rhythmk but, if you have the time, try the following out. It'll help you make the paradigm shift I'm talking about. If you can make that shift, your life will be golden not only for counting but for many other areas of T-SQL, as well.
What does the following statement do?
SELECT *
FROM sys.all_columns
;
Other than offending most people's sensibilities about the use of "*" returning to much data (too many columns), most people would say that the code returns all columns of the table and they'd be partially correct. The other thing that it does is, because there is no limit expressed in the code, it also returns all of the rows. If you look in the lower right corner of the screen, you'll see the number of rows the query returned and, for this table, I absolutely guarantee that number will always be more than 4,000. Write the number of rows returned down. We'll need it again.
Next experiment. What does the following code do? And... what doesn't it do?
SELECT 1
FROM sys.all_columns
;
The obvious thing that it does is that it returns a 1 for every row in the table. You can easily verify that by comparing the number you wrote down in the first experiment with the number of rows this query returned.
The question is, what didn't it do?
The answer is that it didn't use ANY data from the table. It only used the "presence of rows" from the table. To translate that, we used the Pseudo Cursor behind the scenes of the SELECT as a loop to return a certain number of ones, that number being the count of all the rows in the table.
Now, let's do something a bit more practical. Let's count from 1 to however many rows there are in the table. Don't think of it as creating X rows with an incremental count. Think of it as creating a column that has an incremental count up to a limit. Let T-SQL worry about how to create the rows.
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
;
On my little 2005-based test machine, that returns a column of numbers number from 1 to 4,168 even on a brand new, totally empty database. Your number may differ a bit but it will always be > 4,000.
That does bring up the next question. Let's just say that's the largest table in the database and we want to count to, say, 20,000. In C#, you would simply change the max limit of your While loop and you'd be done. In T-SQL, we actually have to provide a larger "Presence of Rows". In C#, if the largest number you could count to was 4,000, you could do it by making a nested loop where each iteration of an outer loop would cause the inner loop to iterate the 4,000 times it's supposed to.
In T-SQL, we can build such a nested loop simply by doing a CROSS JOIN. To keep us from building the 16 Million rows that a nested loop of 4,000 in each loop would deliver, we have to provide a "Stop" value just like you would have to do in C# on the inner loop. Instead of having to worry how to do that in a loop, we just tell T-SQL to stop when it has 20,000 values in the column. We use TOP for this and let T-SQL figure out the rest. Rather than defining the content of each row and evaluating each row for the max value we want, we simply define what we want in the column (as we did before) and when to quit. Like this...
SELECT TOP 20000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
Taking the next step, think of the CROSS APPLY that Luis used as a "For Each Row". His use of CROSS APPLY replaces the nested SELECT in your WHILE loop so that for each of the 1,000 items selected from the Chest table, it'll return X number of rows from the Items table depending on the random number generator that you used (ABS(CHECKSUM(NEWID()))%range+offset). Behind the scenes, it does exactly the same looping that you did in your WHILE loop but in a more "compiled" fashion rather than an "interpretive" fashion.
Again, the key to success for a great deal of what can be done in T-SQL is to understand that T-SQL loops behind the scenes in a very effective manner and your goal is simply to let T-SQL do that for you instead of overriding what it's good at. Tell T-SQL what you want it to do and not how to do it. In other words (again), "[font="Arial Black"]Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
[/font]"
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2014 at 11:37 am
Luis Cazares (5/11/2014)
Your query looks just like mine but using a while loop instead of a single operation. Why would you do that.
Because and like he said, he's a C# procedural programmer just making his first move from a procedural language to a declarative language and hasn't yet made the necessary paradigm shift. 🙂 Based on how he formatted the code and the fact that he understands the use of NEWID() in conjuction with modulus to produce a constrained random integer, he'll get it. It's just going to take some time like the first time most of us started working with T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2014 at 7:32 pm
Thanks Jeff for the detailed insight on the necessary shift in programmatic perspective, I truly appreciate the help!
Right now I'm taking the training courses offered through CBT Nuggets on 70-461 and I can see that there is a nugget entirely devoted to row-based transactions vs. set-based transactions, so I'm confident I'll shortly have a better understanding of how a T-SQL programmer needs to think relative to relative to relational databases (and set theory, at a higher level).
For a better understanding of what I've been trying to accomplish, I'll post some results below:
Re: The ChestItems table, It's purpose is to be a one-to-many join table between Chests and Items. A chest can have many Items, but an Item can have one Chest:
ChestItemsID ChestID ItemID
------------ ----------- -----------
1 1 60002
2 1 60014
3 1 60015
4 1 60021
5 1 60031
6 1 60038
7 2 60004
8 2 60007
9 2 60012
10 2 60019
The purpose of the WHILE loop around the INSERT was to ensure that I was putting x Items into the same chest. If there is a better method of implementation, please share it with me. I am humbled by the help I've already received and appreciate any more that comes my way.
May 11, 2014 at 8:03 pm
tombiagioni1983 (5/11/2014)
If there is a better method of implementation, please share it with me.
There's is indeed. See Luis' code, which he previously posted. Remember that CROSS APPLY is akin to "For Each Row".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2014 at 8:17 pm
Here is a quick "translation" to play around with
😎
USE tempdb
Go
DECLARE @CHEST_COUNT INT = 1000;
/* Seed for the Inline Tally */
;WITH TN(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ChestItemsID
,CHEST.N AS ChestID
,ITEM.N AS ItemID
FROM
(
SELECT TOP (@CHEST_COUNT) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N FROM TN T1, TN T2
, TN T3, TN T4, TN T5, TN T6, TN T7, TN T8, TN T9
) AS CHEST
CROSS APPLY
(
SELECT TOP ((ABS(CHECKSUM(NEWID())) % (CHEST.N * 2)) + 1) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) + 6000 AS N FROM TN T1, TN T2
, TN T3, TN T4, TN T5, TN T6, TN T7, TN T8, TN T9
) AS ITEM;
Result example
ChestItemsID ChestID ItemID
------------- --------- -------
1 1 6001
2 2 6001
3 2 6002
4 2 6003
5 2 6004
6 3 6001
7 4 6001
8 4 6002
9 4 6003
10 4 6004
11 4 6005
12 4 6006
13 4 6007
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply