Author |
Topic |
MaccGaz
Starting Member
5 Posts |
Posted - 2008-08-08 : 08:15:17
|
Hi,I've got a stored procedure which is quite complicated and goes over numerous records. As a result its quite slow. One of the ways I'd like to improve it is to create a variable and assign it to a mini-select for each row select.Even that sounds complicated! Here's what I mean.Lets say we have 10 Users in tblUsers.The query runs like this:Select tblUsers.Name, ((SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 1) = OneTimesNumber,((SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 2) = TwoTimesNumber,((SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 3) = ThreeTimesNumber,((SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 4) = FourTimesNumber,((SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 5) = FiveTimesNumberFrom tblUsersSo this would result in a list of users followed by 5 numbers.It just seems very inefficient that I'm querying "(SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID)" 5 times per row.Is there a way that I can set the result of that query to a variable and then reuse it for the next 4? So, instead of running that mini-select 5 times per row, I only run it once and then make use of the value.(Just to be clear... this would mean the statement looks something like...)Select tblUsers.Name, (@MyVariable = (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 1) = OneTimesNumber,(@MyVariable * 2) = TwoTimesNumber,(@MyVariable * 3) = ThreeTimesNumber,(@MyVariable * 4) = FourTimesNumber,(@MyVariable * 5) = FiveTimesNumberFrom tblUsersIt seems like something that would be common but its quite hard to find an answer for this.Thanks in advance. |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-08 : 08:20:49
|
i know it's friday afternoon, but i don't really get what you're trying to acheive. can you give us some sample data from each table and an example output?Em |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-08 : 08:26:02
|
It's Friday evening, I think this is what you want ?SELECT u.Name, SUM(Number1) * 1 AS OneTimesNumber, SUM(Number1) * 2 AS TwoTimesNumber, SUM(Number1) * 3 AS ThreeTimesNumber, SUM(Number1) * 4 AS FourTimesNumber, SUM(Number1) * 5 AS FiveTimesNumber,FROM tblUsers u INNER JOIN tblNumbers n ON u.UserID = n.UserIDGROUP BY u.Name KH[spoiler]Time is always against us[/spoiler] |
 |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-08 : 08:28:54
|
can it really be that simple? perhaps i was blinded by the original over-complexity (that and the pint at lunchtime)...and friday night where you are? why aren't you out partying?Em |
 |
|
pootle_flump
1064 Posts |
Posted - 2008-08-08 : 08:29:19
|
Yay! Tally tables again:SELECT tblUsers.Name , Sum(tblNumbers.Number1) * dbo.MyTallyTable.numberFROM tblUsersINNER JOIN tblNumbersON tblNumbers.UserID = tblUsers.UserIDCROSS JOIN dbo.MyTallyTableWHERE dbo.MyTallyTable.number BETWEEN 1 AND @variableGROUP BY tblUsers.Name http://www.sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspxFriday arvo for us Em - Macc might be on the East coast and not had his first coffee yet :) |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-08 : 08:31:20
|
Actually on sick leave at home today. On drugs and got bored with all that sleeping. Home i am posting something sensible  KH[spoiler]Time is always against us[/spoiler] |
 |
|
pootle_flump
1064 Posts |
Posted - 2008-08-08 : 08:31:24
|
Damn - typed too slow in my excitement (I kept hitting two keys at oncve) and misunderstood the requirement |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-08 : 08:34:13
|
pootle, You too have too much of it during lunch ?  KH[spoiler]Time is always against us[/spoiler] |
 |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-08 : 08:35:37
|
Not sure. But try thisselect name, number*1 as OneTimeNumbers, number*2 as TwoTimeNumbers, number*3 as ThreeTimeNumbers, number*4 as FourTimeNumbers, number*5 as FiveTimeNumbersfrom(Select tblUsers.Name, (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 1) as number From tblUsers) as t MadhivananFailing to plan is Planning to fail |
 |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-08 : 08:36:08
|
all this drugs and alcohol, sql is just SO rock n roll Em |
 |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-08 : 08:38:45
|
I was too lateMadhivananFailing to plan is Planning to fail |
 |
|
pootle_flump
1064 Posts |
Posted - 2008-08-08 : 08:39:00
|
I never have too much alcohol at lunch - on a Friday I have 5 pints and a couple of Tequilas - absolute tops.Full marks to Madhi - except is that a little syntax error I see in there? |
 |
|
MaccGaz
Starting Member
5 Posts |
Posted - 2008-08-08 : 08:40:59
|
Most of that was all made up really :)As an exmaple, though, you might havetblUsersID | Name | Age1 | Jim | 202 | Bob | 303 | Bill | 40tblPointsScoredID | UserID | Points1 | 1 | 102 | 2 | 153 | 3 | 20Results would beJim 10 | 20 | 30 | 40 | 50Bob 15 | 30 | 45 | 60 | 75Bill 20 | 40 | 60 | 80 | 100----here's the problem though, in terms of efficiency, using the first query I mentioned.. for Jim's row the processing would be:Get NameGet (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) Multiply result by 1Get (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) Multiply result by 2Get (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) Multiply result by 3etc...What I want to do is assign a variable to the results of "(SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID)" so that it is only called once. So the processing path would then be.Get NameGet (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) Assign result to @VariableMultiply @Variable by 1Multiply @Variable by 2Multiply @Variable by 3Multiply @Variable by 4Multiply @Variable by 5So, essentially, I save 4 queries for each row. So 4 queries for Jim, 4 for Bob and 4 for Bill.I hope that is a bit clearer :) |
 |
|
pootle_flump
1064 Posts |
Posted - 2008-08-08 : 08:43:30
|
So in what way did the above three queries fail? |
 |
|
MaccGaz
Starting Member
5 Posts |
Posted - 2008-08-08 : 08:45:22
|
Wow thats lots of replies very quickly. I'd written that post above after the first reply. I'll go back and read through your answers :) |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-08 : 08:47:49
|
[code]DECLARE @tblUsers TABLE( ID int, Name varchar(4), Age int)INSERT INTO @tblUsersSELECT 1 , 'Jim' , 20 UNION ALLSELECT 2 , 'Bob' , 30 UNION ALLSELECT 3 , 'Bill' , 40DECLARE @tblPointsScored TABLE( ID int, UserID int, Points int)INSERT INTO @tblPointsScoredSELECT 1 , 1 , 10 UNION ALLSELECT 2 , 2 , 15 UNION ALLSELECT 3 , 3 , 20SELECT u.[Name], SUM(Points) * 1, SUM(Points) * 2, SUM(Points) * 3, SUM(Points) * 4, SUM(Points) * 5FROM @tblUsers u INNER JOIN @tblPointsScored p ON u.ID = p.UserIDGROUP BY u.[Name]SELECT [Name], Points * 1, Points * 2, Points * 3, Points * 4, Points * 5FROM( SELECT u.[Name], SUM(Points) AS Points FROM @tblUsers u INNER JOIN @tblPointsScored p ON u.ID = p.UserID GROUP BY u.[Name]) a/*Name ---- ----------- ----------- ----------- ----------- ----------- Bill 20 40 60 80 100 Bob 15 30 45 60 75 Jim 10 20 30 40 50 (3 row(s) affected)*/[/code] KH[spoiler]Time is always against us[/spoiler] |
 |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-08 : 08:48:35
|
quote: Originally posted by pootle_flump I never have too much alcohol at lunch - on a Friday I have 5 pints and a couple of Tequilas - absolute tops.Full marks to Madhi - except is that a little syntax error I see in there?
Thanks. I corrected it MadhivananFailing to plan is Planning to fail |
 |
|
pootle_flump
1064 Posts |
Posted - 2008-08-08 : 08:54:06
|
Oh My Gods KH - are you running the Kristen bot or something? I time that lot at 6 minutes 10 seconds |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-08 : 08:57:40
|
I am on drugs  KH[spoiler]Time is always against us[/spoiler] |
 |
|
MaccGaz
Starting Member
5 Posts |
Posted - 2008-08-08 : 08:58:32
|
Excellent, thanks for the quick replies!Would khtan's method mean that the:quote: FROM( SELECT u.[Name], SUM(Points) AS Points FROM @tblUsers u INNER JOIN @tblPointsScored p ON u.ID = p.UserID GROUP BY u.[Name]) a
This bit of code is called once, or would it be called each time a quote: SUM(Points)
is written?As you might have guessed, the Users and PointsScored things are just made up examples. My real stored procedure has joins of about 10 tables and there is one "sub-query" which searches through 20k+ records each time. This "sub-query"is called 5 times for each row in the select.Since my results bring back, say 3000 results. It seems like I'm wasting 4 of these 20k record searches per row.. which will add up to a lot of processing power (and time) wasted! I'll try the last method to see if I can fit it in.Once again thanks for the quick replies. |
 |
|
pootle_flump
1064 Posts |
Posted - 2008-08-08 : 08:59:01
|
Dude - I gotta get me some of them |
 |
|
Next Page
|