Query Efficiency with example - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Efficiency with example

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) = FiveTimesNumber
From tblUsers

So 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) = FiveTimesNumber
From tblUsers

It 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.UserID
GROUP 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.number
FROM	tblUsers
INNER JOIN
		tblNumbers
ON	tblNumbers.UserID	= tblUsers.UserID
CROSS JOIN
		dbo.MyTallyTable
WHERE	dbo.MyTallyTable.number	BETWEEN 1 AND @variable
GROUP BY tblUsers.Name

http://www.sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

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

select 
	name,
	number*1 as OneTimeNumbers,
	number*2 as TwoTimeNumbers,
	number*3 as ThreeTimeNumbers,
	number*4 as FourTimeNumbers,
	number*5 as FiveTimeNumbers
from
(
Select tblUsers.Name,
	(SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID) * 1) as number 
From tblUsers
) as t

Madhivanan

Failing 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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-08 : 08:38:45

I was too late

Madhivanan

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

tblUsers

ID | Name | Age
1 | Jim | 20
2 | Bob | 30
3 | Bill | 40

tblPointsScored

ID | UserID | Points
1 | 1 | 10
2 | 2 | 15
3 | 3 | 20

Results would be
Jim 10 | 20 | 30 | 40 | 50
Bob 15 | 30 | 45 | 60 | 75
Bill 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 Name
Get (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID)
Multiply result by 1
Get (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID)
Multiply result by 2
Get (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID)
Multiply result by 3
etc...

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 Name
Get (SELECT Sum(Number1) From tblNumbers WHERE tblNumbers.UserID = tblUserID.UserID)
Assign result to @Variable
Multiply @Variable by 1
Multiply @Variable by 2
Multiply @Variable by 3
Multiply @Variable by 4
Multiply @Variable by 5

So, 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 @tblUsers
SELECT 1 , 'Jim' , 20 UNION ALL
SELECT 2 , 'Bob' , 30 UNION ALL
SELECT 3 , 'Bill' , 40

DECLARE @tblPointsScored TABLE
(
ID int,
UserID int,
Points int
)
INSERT INTO @tblPointsScored
SELECT 1 , 1 , 10 UNION ALL
SELECT 2 , 2 , 15 UNION ALL
SELECT 3 , 3 , 20

SELECT u.[Name],
SUM(Points) * 1,
SUM(Points) * 2,
SUM(Points) * 3,
SUM(Points) * 4,
SUM(Points) * 5
FROM @tblUsers u
INNER JOIN @tblPointsScored p ON u.ID = p.UserID
GROUP BY u.[Name]

SELECT [Name],
Points * 1,
Points * 2,
Points * 3,
Points * 4,
Points * 5
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
/*
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

Madhivanan

Failing 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources