On Apr 6, 2006, at 6:39 AM, Sean Davis wrote:
On 4/6/06 12:12 AM, "surabhi.ahuja" <surabhi.ahuja@xxxxxxxxxxx> 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@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
