tag:blogger.com,1999:blog-15319370.post5695204870015096598..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Stored procedures - Good or Bad?rpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-15319370.post-15952805698415891582007-11-20T08:14:00.000+01:002007-11-20T08:14:00.000+01:00I think people who are saying that stored procedur...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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-47498562305751740702007-03-08T16:23:00.000+01:002007-03-08T16:23:00.000+01:00Here are some of my thoughts:1. Stored procedures ...Here are some of my thoughts:<BR/><BR/>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.<BR/><BR/>2. Using stored procedures can make your code less portable if you have to switch to a different RDBMS.<BR/><BR/>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.<BR/><BR/>4. Stored procedures can give you an extra level of security if you need to restrict what a user can/cannot see/do.<BR/><BR/>I'm sure there's more to consider. In the end, you just have to pick the best tool for the job.blockcipherhttps://www.blogger.com/profile/08903366326668033596noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-89908003006885367662007-03-08T10:51:00.000+01:002007-03-08T10:51:00.000+01:00I don't see a good reason to use stored procedures...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.<BR/><BR/>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.<BR/><BR/>Just my two eurocents,<BR/>MattAnonymoushttps://www.blogger.com/profile/12263548900215476529noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-65729899464211804752007-03-08T09:36:00.000+01:002007-03-08T09:36:00.000+01:00I like stored procedures. It's good tool. Nothing ...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. <BR/><BR/>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 changesAnonymousnoreply@blogger.com