Inline Query vs Stored Procedures to use in PHP

I have been using inline query statements within my m开发者_Go百科odel of my application... As of now many people said that stored procedures would be a good practise... I have the following question

• How to use MySql stored procedures in PHP Codeigniter?

• What are the advantages of using Mysql stored procedures?

Stored procedures are a tool just like anything else (triggers, views, etc) and should only be used where the tool fits. Taking every inline SQL statement that you have and converting it into a stored procedure is not the right direction.

Stored procedures are written using very rough logic and using cursors to loop through data sets which is going to be a hindrance when you are used to PHP's ability to handle logic.

The only project I have worked on that used SP was a large accounting system where hundreds of thousands of records had to be updated frequently with very little direct input from code. We basically just told it when to start and didn't have need to pass values to the SP (though it is possible to do so). This was, however, only one or two out of thousands of inline queries we used in the system. I am not saying this is the only place to use them, but I have built some very large systems and have not yet had the 'NEED' to use them elsewhere.

In my opinion it would be better to spend your time learning to write high-speed, low-drag queries inline and use EXPLAIN to verify that you have indexes set up correctly. I think that unless you have a VERY specific need for Stored Procedures you would find them very unfriendly for every day use.

I'm not saying stored procs are necessarily the answer to everything. But, the use of cursors is not required. I agree that cursors are a hindrance (slow), and should therefore only be used when absolutely necessary. In my experience, cursors have only been used on badly designed (not normalized) databases, usually because normal queries can't extract the data as needed.

My background is with .NET, where I would say stored procs are the absolute best way to query the database. I think that's partially because ASP.NET is designed to facilitate them. It provides great separation between data and code - you write a stored procedure to return the data you need, then test it. Now I am just now getting into php, so I can't recommend best practice for that. It probably depends on the specifics of your project (scale, complexity, etc.).

But don't discount stored procs because of misinformation. Stored procs does not necessarily mean tying yourself to using slow, costly cursors. Except for setting output parameters, they use essentially all the same statements and logic that any SQL query uses. There's no special "rough" logic you need to learn.