MySql: SQL in models vs. SQL in Stored Procedures

[eluser]obiron2[/eluser]
Hi guys,

I'm have a look see at stored procedures in MySQL.

If I am managing the front end formatting, back end business logic development and DB management, is there any benefit in using stored procedures to fetch data in the models rather than generating SQL select statements in PHP/CI.

I am wondering whether you get any significant reduction in processing overhead if you need to build a sequence of SQL statements (e.g. multi-dimensional array) because you will only be making one trip to the SQL server even though the procedure will execute many SQL selects.

Or am I just adding anohter layer of separation that makes life more complicated.

Obiron

[eluser]rogierb[/eluser]
Hi,

As for speed, don't expect any miracles. It will just be a fraction faster then the 'ol method of PHP/MySQL.

Is has benefits if you use a separate DB server. Then it will decrease traffic. Another nbenefit is if you have a large number of rows that need some kind of work. It will be faster since the large resultset doesn't have to be tranferred over the network.

I think there is an issue with access. Some vague memory of procs running under the user that nade them rather then the user that calls them. But I'm not sure whether this was with MySQL.

Hope this helps somewhat