Re: Stored procedure overhead - baffling - database gurus

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 13:09:55 -0500

This article has instructions on determining whether your sproc is
recompiling and/or not caching properly:

http://www.sqlservercentral.com/columnists/bkelley/procedurecache_printversi
on.asp

Also, you should consider switching

If (Select count(trade_id) from trade_transaction where trade_id =
@TradeID) = 0

to

If NOT EXISTS (Select * from trade_transaction where trade_id =
@TradeID)

This will generally perform much better, as the server only has to find one
row matching the criteria to answer the EXISTS question, whereas for a
COUNT(), all rows must be found.

"Praty77" <praty77-google@yahoo.com> wrote in message
news:6c19a8f5.0403111003.540f2c4e@posting.google.com...
> Hello -
> I hope some database guru will enlighten me with this unique
> situation. There appears to be significant overhead in encapsulating a
> simple sql query in a stored procedure.
>
> I have a simple procedure fsp_getstatus (@tradeid integer)
> defined as:
> declare @status_num decimal
> If (Select count(trade_id) from trade_transaction where trade_id =
> @TradeID) = 0
> set @status_num = -1
> return @status_num
>
>
> If I run the proc, it takes 30 ms. If i run the raw statements
> (without the return of course), it takes 0 ms. This proc is called
> hundreds of times by one of our other stored procedures, and this 30
> ms delay (that has surfaced recently) is causing us severe performance
> problems.
>
> how much overhead is caused by running a stored procedure, compared to
> a simple sql statement? Is there any reason why this overhead would
> shoot up all of a sudden?
>
> I could not find any help in documentation, hopefully some gurus can
> enlighten me.
>
> Thanks in advance
> praty

Relevant Pages

  • Stored procedure overhead - baffling - database gurus
    ... I hope some database guru will enlighten me with this unique ... simple sql query in a stored procedure. ... how much overhead is caused by running a stored procedure, ...
    (microsoft.public.sqlserver.server)
  • Re: Replacing IIF with a UDF (not CASE)
    ... A function like that may add a lot of overhead to your queries if they ... It's like executing a stored procedure for each ...
    (microsoft.public.sqlserver.programming)
  • Re: Comparision between Datatables Select method and stored procedure
    ... Just on the time for the Select, I would hope a stored procedure is better. ... The database's optimization engine and indexing should be superior to ... Now, the only other factor in this, is that calling your stored procedure ... additional overhead that would take, since we don't know your setup. ...
    (microsoft.public.dotnet.languages.csharp)