p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: stored proc performance benefit


Message #1 by "Jake Chapman" <jakech@b...> on Mon, 3 Dec 2001 13:18:20
Can anyone tell me how great is the performance benefit of using a stored 

procedure i.e. how much compile time is actually saved.  I assume that any 

time benefit is lost when the SQL statement is dynamic, i.e. relies on 

user-input.
Message #2 by Jack_Speranza <jsperanza@g...> on Mon, 3 Dec 2001 10:05:57 -0500
Hi Jake --

	I'm far from a database guru, however, I know enough to be able to

tell you that stored procs execute significantly faster than native SQL

commands.  This is because the RDBMS optimizes and compiles these procs on

creation (in the case of SQL Server, it's compiled C code).  



	Naturally, database performance is a function of many things (such

as how you index your tables, and whether you optimize your indices for

reading or writing, etc.), so there are no hard and fast rules as to how

much better your performance will be "across the board."  If you're

interested in measuring the difference for a particular table (or a

particular grouping of joined tables), run some benchmark tests  using both

native SQL and a stored proc.  I'll put my money on the stored proc each

time.



	Not sure if this is exactly what you were looking for, but hope it

helps.



Jack



	



-----Original Message-----

From: Jake Chapman [mailto:jakech@b...]

Sent: Monday, December 03, 2001 8:18 AM

To: ASP Databases

Subject: [asp_databases] stored proc performance benefit





Can anyone tell me how great is the performance benefit of using a stored 

procedure i.e. how much compile time is actually saved.  I assume that any 

time benefit is lost when the SQL statement is dynamic, i.e. relies on 

user-input.

 



jsperanza@g...


$subst('Email.Unsub')

Message #3 by "Serge Wagemakers" <swagemakers@d...> on Mon, 3 Dec 2001 16:04:18 +0100
Not quite... The stored procedure has been parsed in such a way that the

database

server knows the best way to handle the job. In a way it has been made

executable if

you like. That way no syntax checking has to be done. That's a time saver.

When dynamic data is used in a form and has to be executed within a stored

procedure,

the syntax isn't going to be checked, but the optimizer is going to look for

the best ways

of using indexes (if applicable).



In short version:

When you fire a SQL-statement instead of a stored proc. two things take

place:

- Parsing the SQL statement

- Optimizing the SQL statement.



When firing a stored proc:

- Optimizing the SQL statement.



How much time, depends on all kinds of things, like eg. do you use parallel

queries etc.



HTH,



Serge



----- Original Message -----

From: "Jake Chapman" <jakech@b...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, December 03, 2001 1:18 PM

Subject: [asp_databases] stored proc performance benefit





> Can anyone tell me how great is the performance benefit of using a stored

> procedure i.e. how much compile time is actually saved.  I assume that any

> time benefit is lost when the SQL statement is dynamic, i.e. relies on

> user-input.

>




$subst('Email.Unsub')

>




  Return to Index