Cost of using @@PROCID & OBJECT_NAME()

Question

Cost of using @@PROCID & OBJECT_NAME()

jonashilmersson

SSC Eights!

Points: 842
More actions
December 2, 2003 at 3:04 am

#101781
Hi!
I'm using a tracing solution for all my stored procedures which requires that every procedure reveal its name when sending a trace message. Today I have implemented it by using a varchar variable which is used by all trace routines.
```
     SET @Source = 'DLG_Delegations_RegionalDel'
```
But what is the cost of using the following instead:
```
    SET @Source = OBJECT_NAME(@@PROCID)
```
This would simplify templates but at what performance cost?
Brgds
Jonas
Edited by - jonashilmersson on 12/02/2003 04:00:37 AM
BrgdsJonas

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

When you look at the execution plan, you'll see that this SET operation does a Constant Scan, which I guess *should* be pretty fast.

AFAIK a constant scan adds a small amount of CPU cost, so when so have a db with many objects, many users and use this frequently, it might affect performance.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jonashilmersson SSC Eights! Points: 842 More actions · Answer 2

Yes, there must be some kind of SELECT against the sysobjects table which could take some time if there are many objects, but I guess it is pretty much used by the system anyway so it should be in the cache all the time.

Brgds

Jonas

BrgdsJonas

cmore SSCrazy Points: 2452 More actions · Answer 3

I'm guilty of using this for pretty much the same reason. If I have a sp that I know will be ran frequently, i go modify my template code and type in the sp manually.

I don't use it for traces though... i've not had much trouble getting the sp names out of the traces so you must be doing something i've not tried with it.

jonashilmersson SSC Eights! Points: 842 More actions · Answer 4

I'm using it to get a trace functionality similar to that of ASP.NET, but it uses OutputDebugString via an extended stored procedure to output information about the execution of nested stored procedures, and also in event of an error.

It is no big problem to hardcode the name with a variable, I was just curious if someone knew about the performance hit.

Thanks for your time

Brgds

Jonas

BrgdsJonas

BanzaiSi SSChasing Mays Points: 611 More actions · Answer 5

Well, it just so happens I'm building a similar tracing mechanism myself at the moment!

As an experiment, I tried both SET @Source = OBJECT_NAME(@@PROCID) and SET @Source = 'String Literal' by putting them into stored procedures and executing each statement 1,000,000 times.

The results were:

OBJECT_NAME(@@PROCID) - 17.8 secs

SET 'String Literal' - 7.3 secs

As you can see, the use of OBJECT_NAME(@@PROCID) took just over double the time, but the average run time of 18 microseconds is really not anything to worry about!

--

Si Chan

Database Administrator

cmore SSCrazy Points: 2452 More actions · Answer 6

quote:
I'm using it to get a trace functionality similar to that of ASP.NET, but it uses OutputDebugString via an extended stored procedure to output information about the execution of nested stored procedures

Dunno - I just tab the text display - log the @@nestlevel and when the debugger prints it uses tabs to indent.

Sounds like your's could be more automatic and detailed, if you are actually using a profiler trace to capture the statements executed instead of relying on a home-made note/error logger. I'd be interested in seeing what you end up with. Can email it to me if you don't wish to post it. I'll email you my debugger setup scripts.