Re: stored proc vs sql query string

On Apr 6, 2006, at 6:39 AM, Sean Davis wrote:
> On 4/6/06 12:12 AM, "surabhi.ahuja" <surabhi(dot)ahuja(at)iiitb(dot)ac(dot)in> wrote:
>
>> i have heard somewhere that writing a stored procedure, is much
>> better than
>> firing a sql query(such as select * from table_name) onto the
>> database.
>> is it true and if yes how?
>
> This isn't going to be true most of the time, I think. Write SQL
> where you
> can, and where you can't (because you can't express something in
> SQL), write
> a procedure. There are places where using a stored procedure can
> be more
> efficient, but I think starting with SQL, benchmarking and testing,
> and then
> determining what queries need special attention is the best way to
> go at the
> beginning.

You're forgetting that (at least in plpgsql), "raw" queries get
compiled into prepared statements. Prepared statements are faster to
execute than queries that have to be manually parsed every time. Of
course you can pass in prepared statements from the client side as
well, but if you stick with using stored procedures as an API to the
database you don't have to worry about forgetting to do that. And as
others have mentioned there's non-performance-related benefits to
using stored procs as well.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461