I think StoredProcedures are better for maintainability because they can create a clear interface to the database, Stored Procedures are going to have better performance for complicated queries, this is because when you store the procedure the DB has the opportunity to compile the procedure in advance, with parametrized queries it need to be compiled every time it is run.
Simple applications that use parameterized queries might very well perform as well as an equivalent application written using stored procedure; but as the application grows in complexity and needs to scale, stored procedures offer the best solution for growing and maintaining a robust, scalable, enterprise-level application.
Stored Procedures vs parameterized queries
1 Comment
Join the conversation! Your thoughts help the community grow.
Sign in to leave a comment
It is the same account you read, post and publish with — and you will come straight back to this page.
SenthilkumarPosted Sep 14, 2009, 11:43 PM
Stored procedures will be best for maintainability and performance than queries. I think we have the following advantages 1. Avoid sql injection. 2.If you hard code the queries in the application then every time it will compile and has to execute in the database. 3.If there is any changes in the query then hard coded query must be redeploy in the server. But in the stored procedure with out change the field name we can do any other business logic changes. 4.Stored procedure will be compiled and stored as object in the DB.Every time it uses the existing execution plan, so performance will be good. But if you have the temp tables in the stored procedure then it will compile every time.