The topic of database stored procedures does not cease to divide the community of database application developers and DBAs. There are many articles around, expressing various opinions about this subject. But what do you think?
Please let me know and take this survey. The survey contains 5 multiple choice questions, and will take about 1 to 5 minutes to complete.
The results will be posted soon here on my blog at http://rpbouman.blogspot.com/. Thank you in advance!
(Disclaimer: I'm an employee of MySQL AB, but this survey does not in any way relate to my position with my employer. Also, I have made an utmost effort to write an unbiased survey. If you feel that something is wrong in this regard, please post a comment on this post.)
Subscribe to:
Post Comments (Atom)
DuckDB Bag of Tricks: Reading JSON, Data Type Detection, and Query Performance
DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB . This post is about a particular challenge...
4 comments:
I like stored procedures. It's good tool. Nothing less, nothing more. I see one problem with some stored procedures languages. T-SQL, MySQL long time doesn't support some basic statements, types (ex. arrays). Nobody speak abou useability of stored procedures in Oracle.
I hope so portability will be better. Currently PostgreSQL support full set of SQL/PSM statements .. PL/pgPSM. I tested it and I ported some procedures from MySQL with minimal changes
I don't see a good reason to use stored procedures as part of the application itself. I actually see a lot of reasons for NOT using them. The main reason is not even portability, but scalability from a CPU perspective. It prevents you from going to a proper 3-tier architecture. Remember that a database server is not always equiped with the raw CPU power that a real application server needs. It's often better tuned to do I/O. Running all kinds of (sub-optimal scripting like) procedure languages on the database is in these situations not a good idea.
However, there is not reason to become fanatical about it. For example, Stored procedures can come in handy to manage the eco-system surrounding the applications: manage indexes, truncate temp tables, do cleanups, etc.
Just my two eurocents,
Matt
Here are some of my thoughts:
1. Stored procedures can actually slow you down if they really hammer your database server. A rule of thumb I heard once was that unless the stored procedure reduces the bandwidth you use, it may slow you down.
2. Using stored procedures can make your code less portable if you have to switch to a different RDBMS.
3. Stored procedures allow you to control the queries used by any application. This can be useful if you know the same query is used in many apps or if your developers are not good with databases. Also, you can update your structure and queries without forcing the users to change their code. Of course, I've done the same thing on a couple applications by creating a class/model that handles all of the queries.
4. Stored procedures can give you an extra level of security if you need to restrict what a user can/cannot see/do.
I'm sure there's more to consider. In the end, you just have to pick the best tool for the job.
I think people who are saying that stored procedures are harder on the DB server are forgetting the fact that the DB server is still running the query regardless of if the query comes in the form of a SP or inline SQL. SP's will usually be a bit faster then queries from inline code.
Post a Comment