August 7, 2009 at 9:51 pm
Hello all,
So I was bored and felt like throwing some script together. I wanted to see if I could create a simple script that would generate all possible combination's of gender, hair color, and eye color. I wrote this in one shot, and was proud of myself that it worked the first time I hit the F5 button 😀
Let me know what you think or if I missed a way to improve this...
Use Test_SK
DECLARE
@Counter1 tinyint
,@Counter2 tinyint
,@Counter3 tinyint
,@MaxGenderID tinyint
,@Gender_Name char(1)
,@MaxHairID tinyint
,@Hair_Name varchar(10)
,@MaxEyeID tinyint
,@Eye_Name varchar(10)
IF EXISTS (SELECT Table_Name FROM Information_Schema.tables WHERE Table_Name = 'Gender')
DROP TABLE Gender
CREATE TABLE Gender (ID TINYINT IDENTITY (1,1) , Gender_Name char(1))
IF EXISTS (SELECT Table_Name FROM Information_Schema.tables WHERE Table_Name = 'Hair')
DROP TABLE Hair
CREATE TABLE Hair (ID TINYINT IDENTITY (1,1), Hair_Name varchar(10))
IF EXISTS (SELECT Table_Name FROM Information_Schema.tables WHERE Table_Name = 'Eyes')
DROP TABLE Eyes
CREATE TABLE Eyes (ID TINYINT IDENTITY (1,1), Eye_Name varchar(10))
IF EXISTS (SELECT Table_Name FROM Information_Schema.tables WHERE Table_Name = 'Master_Table')
DROP TABLE Master_Table
CREATE TABLE Master_Table (ID tinyint identity (1,1), Gender_Name char(1), Hair_Name varchar(10), Eye_Name varchar(10))
INSERT INTO Gender Values ('M')
INSERT INTO Gender Values ('F')
INSERT INTO Hair Values ('Black')
INSERT INTO Hair Values ('Blonde')
INSERT INTO Hair Values ('Brown')
INSERT INTO Hair Values ('Red')
INSERT INTO Eyes Values ('Blue')
INSERT INTO Eyes Values ('Green')
INSERT INTO Eyes Values ('Brown')
INSERT INTO Eyes Values ('Hazel')
SELECT
@MaxGenderID = (SELECT MAX(ID) FROM Gender)
,@MaxHairID = (SELECT MAX(ID) FROM Hair)
,@MaxEyeID = (SELECT MAX(ID) FROM Eyes)
SELECT @Counter1 = 1
WHILE @Counter1 <= @MaxGenderID
BEGIN
SELECT @Gender_Name = (SELECT Gender_Name FROM Gender WHERE ID = @Counter1)
SELECT @Counter2 = 1
WHILE @Counter2 <= @MaxHairID
BEGIN
SELECT @Hair_Name = (SELECT Hair_Name FROM Hair WHERE ID = @Counter2)
SELECT @Counter3 = 1
WHILE @Counter3 <= @MaxEyeID
BEGIN
SELECT @Eye_Name = (SELECT Eye_Name FROM Eyes WHERE ID = @Counter3)
INSERT INTO Master_Table VALUES (@Gender_Name, @Hair_Name, @Eye_Name)
SELECT @Counter3 = @Counter3 + 1
END
SELECT @Counter2 = @Counter2 + 1
END
SELECT @Counter1 = @Counter1 + 1
END
SELECT * FROM Master_Table
[/code]
Link to my blog http://notyelf.com/
August 7, 2009 at 10:05 pm
Hi,
try this
CREATE TABLE #Gender
(ID TINYINT IDENTITY (1,1) ,
Gender_Name char(1))
CREATE TABLE #Hair
(ID TINYINT IDENTITY (1,1),
Hair_Name varchar(10))
CREATE TABLE #Eyes
(ID TINYINT IDENTITY (1,1),
Eye_Name varchar(10))
INSERT INTO #Gender Values ('M')
INSERT INTO #Gender Values ('F')
INSERT INTO #Hair Values ('Black')
INSERT INTO #Hair Values ('Blonde')
INSERT INTO #Hair Values ('Brown')
INSERT INTO #Hair Values ('Red')
INSERT INTO #Eyes Values ('Blue')
INSERT INTO #Eyes Values ('Green')
INSERT INTO #Eyes Values ('Brown')
INSERT INTO #Eyes Values ('Hazel')
select a.Gender_Name,b.Hair_Name,c.Eye_Name
from #Gender a,#Hair b,#Eyes c
August 7, 2009 at 10:12 pm
Ah sweet thank mr or mrs 500 😀
I completely forgot about cross joins!
Link to my blog http://notyelf.com/
August 8, 2009 at 2:57 pm
shannonjk (8/7/2009)
Ah sweet thank mr or mrs 500 😀I completely forgot about cross joins!
Heh... it would be better if you forgot how to make While Loops. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2009 at 9:41 pm
I don't use them often, but inevitably I find myself using them on a rare occasion 😀
I have a batch of code that updates a record sequence based on current record sequence and does this until the end of the sequences, if you are interested in figuring out how to do that update without a loop I will send you the code 😉
Link to my blog http://notyelf.com/
August 8, 2009 at 10:50 pm
shannonjk (8/8/2009)
I don't use them often, but inevitably I find myself using them on a rare occasion 😀I have a batch of code that updates a record sequence based on current record sequence and does this until the end of the sequences, if you are interested in figuring out how to do that update without a loop I will send you the code 😉
No problem... I'm definitely up for a challenge like that (and likely so are a half dozen other folks on this forum :-D). However, no one but you and I would benefit if you simply sent me the code. Rather, post it here. It sounds like a simple "running total/count" bit of code could solve the problem but we'll see. If it can't be done using some form of ROW_NUMBER or RANK code, the "Quirky Update" can usually solve the problem with the same amount of speed and all will absolutely blow the doors off of any explicit loop that can be written in T-SQL. 😉 Of course, if it's cross-database code across many databases that are calculated on the fly, a While Loop or even a Cursor may be the correct method so long as it all doesn't fall into the world of RBAR.
You can zip and attach rather large amounts of data in a readily consumable format using the methods outlined in the article at the first link in my signature below. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 1:10 am
Ok I have uploaded a text file with the sample data which contains 426 records. I import this into my database as Order_Table and run the code below (I tested this just in case :-D).
Yes once you run this you will realize since the records are already in order that this was pointless however, I have tons of code that got me to that point, this piece being one of them. The basis was that the records start and end dates had errors, and my process was to give them a sequential number so I could apply a few solutions in correcting them. They are correct now but basically if you run this code you will see how the concept works.
These are old records (inactive ones), so I took the ones where there were the most inactive records that needed to have a sequential number applied.
If you can do this without a loop or cursor I am all for it 😀
DECLARE @Record_Sequence tinyint, @Counter tinyint
SELECT
@Record_Sequence =
(Select Max(Record_Count) FROM (
SELECT Count(*) AS Record_Count, CustomerOrder_Number, CustomerOrder_LineNumber, CustomerORder_LineSuffix
FROM dbo.Order_Table
GROUP BY CustomerOrder_Number, CustomerOrder_LineNumber, CustomerOrder_LineSuffix) AS X)
SELECT @Counter = 1
WHILE @Counter <= @Record_Sequence
BEGIN
UPDATE F1 SET
Record_Sequence = @Counter
FROM dbo.Order_Table AS F1
JOIN(SELECT T1.CustomerOrder_Number, T1.CustomerOrder_LineNumber, T1.CustomerOrder_LineSuffix, T1.Rec_StartDate, T1.Rec_EndDate FROM (
SELECT F1.CustomerOrder_Number, F1.CustomerOrder_LineNumber, F1.CustomerOrder_LineSuffix, F2.Rec_StartDate , MIN(F1.Rec_EndDate) AS Rec_EndDate
FROM dbo.Order_Table AS F1
JOIN (SELECT CustomerOrder_Number, CustomerOrder_LineNumber, CustomerOrder_LineSuffix, Min(Rec_StartDate) AS Rec_StartDate
FROM dbo.Order_Table
WHERE Record_Sequence = 0
GROUP BY CustomerOrder_Number, CustomerOrder_LineNumber, CustomerOrder_LineSuffix) AS F2
ON F1.CustomerOrder_Number = F2.CustomerOrder_Number
AND F1.CustomerOrder_LineNumber = F2.CustomerOrder_LineNumber
AND F1.CustomerOrder_LineSuffix = F2.CustomerOrder_LineSuffix
AND F1.Rec_StartDate = F2.Rec_StartDate
WHERE Record_Sequence = 0
GROUP BY F1.CustomerOrder_Number, F1.CustomerOrder_LineNumber, F1.CustomerOrder_LineSuffix, F2.Rec_StartDate ) AS T1) AS F2
ON F1.CustomerOrder_Number = F2.CustomerOrder_Number
AND F1.CustomerOrder_LineNumber = F2.CustomerOrder_LineNumber
AND F1.CustomerOrder_LineSuffix = F2.CustomerOrder_LineSuffix
AND F1.Rec_StartDate = F2.Rec_StartDate
AND F1.Rec_EndDate = F2.Rec_EndDate
SET @Counter = @Counter + 1
END
select * from order_Table
order by CustomerOrder_Number, CustomerORder_LineNumber, CustomerOrder_LineSuffix, Record_Sequence
Link to my blog http://notyelf.com/
August 9, 2009 at 7:19 am
It would have really been handy if you did like I said and read the article at the first link in my signature below so that you would have provided the data in a readily consumable format along with a CREATE TABLE statement but I guess I can do a conversion pretty easily. I'll give it a whirl. Thanks for the data, Shannon.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 8:09 am
Heh... Like I said, it's best to mostly forget about loops. 🙂 If you're not actually using SQL Server 2005 and you have SQL Server 2000, there's still a solution but it's quite different because it doesn't have ROW_NUMBER available. Let me know.
Here's the code I've tested and the data I tested it against is attached. Take a look at the attachment and you'll see what I meant by "readily consumable format". Again, take a look at the first link in my signature line (below) for an article on how to easily accomplish such a thing. What it does is it really makes it easy on folks trying to help you and they'll basically jump through hoops to help you.
WITH cteSequenced AS
(
SELECT CustomerOrder_Number,
CustomerORder_LineNumber,
CustomerOrder_LineSuffix,
Rec_StartDate,
Rec_EndDate,
ROW_NUMBER() OVER (PARTITION BY CustomerOrder_Number,
CustomerORder_LineNumber,
CustomerOrder_LineSuffix
ORDER BY CustomerOrder_Number,
CustomerORder_LineNumber,
CustomerOrder_LineSuffix,
Rec_StartDate,
Rec_EndDate)
AS Record_Sequence
FROM dbo.Order_Table
)
UPDATE ot
SET Record_Sequence = cte.Record_Sequence
FROM dbo.Order_Table ot
INNER JOIN cteSequenced cte
ON ot.CustomerOrder_Number = cte.CustomerOrder_Number
AND ot.CustomerORder_LineNumber = cte.CustomerORder_LineNumber
AND ot.CustomerOrder_LineSuffix = cte.CustomerOrder_LineSuffix
AND ot.Rec_StartDate = cte.Rec_StartDate
AND ot.Rec_EndDate = cte.Rec_EndDate
;
SELECT *
FROM dbo.order_Table
ORDER BY CustomerOrder_Number, CustomerORder_LineNumber, CustomerOrder_LineSuffix, Record_Sequence
One of the ways to overcome the problem of having loops is a change in thinking. It's a lot easier to say than do because we're all humans and it's easier to think about what to do from row to row but the change in thinking requires a paradigm shift... Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column. Like I said, easier to say than do but once you make the shift, your code will be faster, smaller, and easier to read and troubleshoot than folks who still program using RBAR methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 2:20 pm
Ok sorry about that, I figured since it was easily importable that it was readily consumable...my apologies 😀
That is very cool and yes I use SQL Server 2005. Tomorrow when I get into work I am going to test that against the full table (which contains 5.4 millions records!).
I try to think more of a set based manner but every once in a while I come across a problem like this, and I can't force my brain to work like that...yet 😀
Link to my blog http://notyelf.com/
August 9, 2009 at 2:33 pm
Thanks for the feedback. 5.4 million rows, eh? If the solution I posted takes longer than, ummm... 40 seconds or so, post back... there's a little trick we can do that might be even quicker. Also, 5.4 million rows is probably going to be right at the "tipping point" for your server. That's when things suddenly take exponentially longer for a single update because the system just doesn't have enough resources to hold a single 5.4 million row transaction in mid air.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 6:42 pm
No problem.
Yes it is taking much longer than 40 seconds, but also the main table actually has over 50 columns. I am not sure if that would make a difference since I am just running the code as is against the table...
Link to my blog http://notyelf.com/
August 9, 2009 at 7:29 pm
Not good. I don't know if you've reached that "tipping point" I was talking about or if it's just that we're self-joining a 5.4 million row table. Can you let me know how long it takes? Also, how long did the original While Loop version take?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 7:36 pm
The original took about 40 minutes.
The current is still running but running right now at an hour and 25 minutes.
I appreciate all the help 🙂
Link to my blog http://notyelf.com/
August 9, 2009 at 7:44 pm
shannonjk (8/9/2009)
The original took about 40 minutes.The current is still running but running right now at an hour and 25 minutes.
I appreciate all the help 🙂
I'd just go ahead a kill it. It sounds like it reached the tipping point and it could take hours to resolve if you don't. I ran into a similar problem where an update took just a minute for a given number of rows and only two minutes for twice that. But, it took 20 hours to run on just 3 times that.
Didn't know you had 5.4 million rows when we started this. Maybe an index on the joined columns would help... you'd be able to tell by looking at the execution plan once an index was made but if this is a production system, I'd stop messing with it. The only way I'd feel comfortable working on this is if I were there and could make a copy of the table to tinker with.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply