Re: Stored procedure overhead - baffling - database gurus
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/11/04
- Next message: Steve Z: "@@ERROR and @@ROWCOUNT"
- Previous message: Tibor Karaszi: "Re: Query Plan Question"
- In reply to: Praty77: "Stored procedure overhead - baffling - database gurus"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 11 Mar 2004 13:15:15 -0500
Some observations:
a.. The return code should be int - not decimal.
b.. What value should the @status_num variable have when there are > 0
rows in trade_transaction for the given @trade_id?
c.. Is the datatype if trade_id in the trade_transaction table of type
int?
d.. You can rewrite the IF as:
if exists (select * from trade_transaction where trade_id = @TradeID)
set @status_num = -1
a.. You can also use the following if you want to set @status_num to zero
when there are > 0 rows in trade_transaction for the given @trade_id:
select @status_num = case when exists (select * from trade_transaction
where trade_id = @TradeID)then -1 else 0 end
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "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
- Next message: Steve Z: "@@ERROR and @@ROWCOUNT"
- Previous message: Tibor Karaszi: "Re: Query Plan Question"
- In reply to: Praty77: "Stored procedure overhead - baffling - database gurus"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
