Don't use sp_ for stored procedure - Benchmark

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

Transact-SQL (2000)

Don't use sp_ for stored procedure - Benchmark

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 14:02:24

OK, so I thought I'd actually try this.


USE Northwind
GO
CREATE PROCEDURE dbo.sp_test1
	@param	int OUTPUT
AS
SELECT @param = 1
GO

CREATE PROCEDURE dbo.xx_test2
	@param	int OUTPUT
AS
SELECT @param = 2
GO

And here's my test rig:


DECLARE	@intLoop int, @startTime datetime, @param int
SELECT @intLoop = 100000, @startTime = GetDate(), @param = 1
WHILE @intLoop > 0
BEGIN
	EXEC sp_test1 @param = @param OUTPUT
	SELECT @intLoop = @intLoop-1
END 
SELECT [@param]=@param, [Elapsed]=DATEDIFF(millisecond, @startTime, GetDate())

Tests, with times:

Seconds Command
2.623 EXEC sp_test1 @param = @param OUTPUT
2.436 EXEC xx_test2 @param = @param OUTPUT
2.736 EXEC dbo.sp_test1 @param = @param OUTPUT
2.656 EXEC dbo.xx_test2 @param = @param OUTPUT
6.466 EXEC Northwind.dbo.sp_test1 @param = @param OUTPUT
6.343 EXEC Northwind.dbo.xx_test2 @param = @param OUTPUT

The "non sp_" version is faster in all instances, but I thought 2 and 3 part naming was supposed to be faster too?

Or have I mucked up my test rig somewhere?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-19 : 14:15:59

This is a good candidate for a blog on SQLTeam.com. Ask Graz for one:

http://weblogs.sqlteam.com/billg/contact.aspx

Tara

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 14:31:56

You've got one, what are you waiting for?!

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 14:33:32

Actually, more serious question: what would the Blog achieve that a posting here doesn't?

Thanks

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-19 : 15:21:11

"I thought 2 and 3 part naming was supposed to be faster too"
You are dbo right ?, Sql Server looks for objects owned by current user first.
So saying exec objectName and exec dbo.objectName would be equivalent.

/rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-19 : 15:34:51

But seems not. Weird.
2-Part, One Theory: when specifying "dbo." Sql Server has to parse the string (and resolve specified user).
3-Part, also weird, maybe looks up the database from somewhere.

/rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-19 : 15:36:30

quote:
Originally posted by Kristen

Actually, more serious question: what would the Blog achieve that a posting here doesn't?

Thanks

Kristen

Sometimes it's hard lo relocate a thread after some time...

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Aint that a thing of beauty...

Brett

8-)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-19 : 17:43:17

I modified your test rig w/an outer loop and an average to calculate average runtime for 10 runs (I drop the min and max time from 12 runs). I also modified the original sprocs with SET NOCOUNT ON.

I get the following timings (obviously, my test server is much slower than yours).

Seconds Command
------- -------
8474 sp_test1 (sproc created in master, not northwind)
8421 sp_test1
8414 xx_test2
8423 dbo.sp_test1
8419 dbo.xx_test2
8946 northwind.dbo.sp_test1
8889 northwind.dbo.xx_test2

Here is the test rig I used:

DECLARE	@extloop int, @intLoop int, @startTime datetime, @param int, @elapsed int
SELECT @extloop = 12
CREATE TABLE #DATA (runid int, elapsed int)
WHILE @extloop > 0
BEGIN 
SET @startTime = getdate()
SET @intLoop = 100000
  WHILE @intLoop > 0
  BEGIN
     EXEC sp_test1 @param = @param OUTPUT
     SELECT @intLoop = @intLoop-1
  END
INSERT INTO #DATA (runid, elapsed) VALUES (@extloop, DATEDIFF(millisecond, @startTime, GetDate()))
SET @extloop = @extloop -1
END
-- Get the results
--SELECT * FROM #DATA
DELETE FROM #DATA WHERE elapsed = (SELECT MAX(elapsed) from #DATA)
DELETE FROM #DATA WHERE elapsed = (SELECT MIN(elapsed) from #DATA)
--SELECT * FROM #DATA
SELECT AVG(elapsed) Average FROM #DATA
DROP TABLE #DATA
  
GO

-ec

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-19 : 18:38:04

Ok,

This is with ec's TestHarness on my LapTop.
Seconds Command
------- -------
1572 sp_test1 (sproc created in master, not northwind)
1517 sp_test1
1395 xx_test2
1624 dbo.sp_test1
1537 dbo.xx_test2
4867 northwind.dbo.sp_test1
4625 northwind.dbo.xx_test2

select db_name,current_user
------------------------------ ------------------------------
Northwind dbo

/rockmoose

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-19 : 18:42:10

on a side note, my workstation sucks!

I have a 2way xeon 2.8ghz HP wx6000 w/2gigs RAM. THis thing should kick the crap out of everything out there... I just got my ass beat by a laptop!

I'm going to turn off hyperthreading and run this baby again.

EDIT:

re-ran with no hyperthreading (2 cpus still) and it was slower (11 seconds now).

I'm running Personal edition build 919 (8.00.919) w/winXP SP1.

-ec

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-19 : 19:52:53

Sorry ec...

Got a 2.66GHz laptop w 512MB RAM.
Running Developer Edition Build 2600: (8.00.760) on XP SP1.

/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 01:32:23

My figures were from a 2 CPU Dev box (modest in all dimensions).

I ran it on our 4 x Xeon turbo-nutter-bastard and it was so slow I had to abort it :-)

changing it to only 1,000 iterations it was taking 2.873 seconds ...

Yikes!

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-20 : 12:04:00

quote:
Originally posted by Kristen

Actually, more serious question: what would the Blog achieve that a posting here doesn't?

Thanks

Kristen

Blogs actually get more exposure. It just seemed to me that this would be a great article that would draw people to discuss it other than those in the forums.

Tara

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 12:31:03

Ah, OK got it, thanks. I've not been that keen on having my own blog (probably irrational, but).

I suppose I could write some Simple Answers and point people to those rather than retyping the aswers here ...

Hmmm ... perhaps I should write a series of acticles entitled "Kristen says ..." <g>

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-26 : 11:37:36

Interesting. I ran this and got:

All run from the RG_SCRAP_PAD database
1445 for sp_test1
1313 for xx_test2

1489 for dbo.sp_test1
1415 for dbo.xx_test2

3850 for master.dbo.sp_test1
3153 for rg_scrap_pad.dbo.sp_test1
2910 for rg_scrap_pad.dbo.xx_test2

4004 for [opsws148\opsws148].Master.dbo.sp_test1
3260 for [opsws148\opsws148].rg_scrap_pad.dbo.sp_test1
3119 for [opsws148\opsws148].rg_scrap_pad.dbo.xx_test2

I was under the impression that explicitly specifying the owner was a GOOD performance practice, yet it would seem not....

I was considerably surprised to note the BAD performance when adding DB name. Anyone know why that might be? I seem to recall once before reading that adding a servername did cause a slowdown, I think because it checks to see if it is local, or a linked -server.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 12:24:43

I don't understand why it was so slow on our Mammoth Machine either.

Has Vyas or somesuch got a White Paper do you suppose? <fx:sits lazily in chair>

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-27 : 07:00:57

I'll see if I can get a mate to run it on their pre-prod box : 8x1.5ghz IBM x440 with 4 GB ram, running SQL 2K EE...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources