# Usage of stored procedures

**URL:** <https://www.ruby-forum.com/t/usage-of-stored-procedures/106382>\
**Category:** Rails\
**Created:** [June 9, 2007, 1:42pm UTC](https://www.ruby-forum.com/t/usage-of-stored-procedures/106382 "2007-06-09T13:42:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/3da27b/32.png) [@Tyche](https://www.ruby-forum.com/u/Tyche)\
**Post date:** [June 9, 2007, 1:42pm UTC](https://www.ruby-forum.com/t/usage-of-stored-procedures/106382/1 "2007-06-09T13:42:49Z")

</div>

Hi,  
I am trying my hands on RoR for a dummy project and I use Mysql as my  
database. For data manipulation and querying purpose, should I write  
ruby code directly in active record or write a stored procedure and  
invoke a call through active record?  
I understand there are many advantages of stored procedures:

1. They can help enforce data security.  
2.Amount of information sent over the N/W is less
2. No compilation step is required to execute code
3. Once the procedure is present in the shared pool, retrival from  
disk is not required everytime different users call the procedure i.e.  
reduction in disk I/O

Many thanks,  
Tyche

---

<div class="post-metadata">

**Author:** ![Tyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/3da27b/32.png) [@Tyche](https://www.ruby-forum.com/u/Tyche)\
**Post date:** [June 9, 2007, 3:12pm UTC](https://www.ruby-forum.com/t/usage-of-stored-procedures/106382/2 "2007-06-09T15:12:59Z")

</div>

On 6/9/07, Tyche [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 1. Once the procedure is present in the shared pool, retrival from  
> disk is not required everytime different users call the procedure i.e.  
> reduction in disk I/O

With ActiveRecord, you can made all SQL querie that you want with  
several method of \_sql or with a connection by driver. But it’s not  
needed. ActiveRecord made several method to avoid this SQL querie in  
your Ruby Code.

So Avoid made SQL except for perform, but it’s not advisor by default.

–  
Cyril M.

---

<div class="post-metadata">

**Author:** ![Tyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/3da27b/32.png) [@Tyche](https://www.ruby-forum.com/u/Tyche)\
**Post date:** [June 10, 2007, 5:35pm UTC](https://www.ruby-forum.com/t/usage-of-stored-procedures/106382/3 "2007-06-10T17:35:34Z")

</div>

Hi Tyche,

We call stored procs when we have to query a particularly unfriendly  
part of our database, but for the most part, we let ActiveRecord  
handle creating all the SQL for us as Cyril said. Unless you have some  
big, nasty ultra-normalized parts of your database, you shouldn’t need  
to write any SQL statements at all, just let your relationships in  
ActiveRecord work their magic.

That being said, if in a particular instance, calling a stored proc is  
going to save you lots of time, refer to this page  
[http://wiki.rubyonrails.org/rails/pages/StoredProceduresInMySql](http://wiki.rubyonrails.org/rails/pages/StoredProceduresInMySql)  
but as a general rule I would recommend against it.

-Ryan