Stored procedure vs standard query

Stored procedure vs standard query

• JLuis

  I do have some remarks on the above, so I'll state them by the numbers used:

  2. Yes, SQL Server stores all T-SQL exec plans in cache, but... be aware that SQL server matches the cache based on the exact text that makes up the statement. Therefor, if you use a value of some kind (and who doesn't), then you should always, ALWAYS use params in your sql to include them.

  3. Again correct, only most of the time, if this is where you have to get your performance improvement, then I would think your problems are somewhere else. Most of the time, the return data is much more costly in bandwith then the actual statement send.

  6. I agree strongly on this statement. I've been doing some bigger string manipulation in T-SQL and it becomes slow very easily. Try to avoid it! I mean it!

  8. You should also consider Service Oriented Architecture. Basicly you could use webservice calls as easy as you can use SP's and still apply most of the advantages of SP's (including using the DB servers hardware) but then have it in managed code and making life a lot easier if you switch databases.

  
  

• Vaish

  I have seen some pretty poorly performing applications based on too much reliance on simple CRUD stored procedures. A stored procedure that inserts a single row for example is not significantly more performant than an insert statement from the data tier. What does tend to happen is that developers start to rely on a basic set of CRUD procedures and end up doing very sub-optimal things. For example calling a read procedure to get a list of closed orders and then calling a delete procedure for each item on the list instead of writing a single delete statement that does the required deletes in a set-oriented way. On the other hand, a stored procedure can encapsulate a complete process much more efficiently that a series of calls from the middle tier. With CLR stored procedures, a single database call can include significant text or XML manipulation and complex calculations as well as database calls. Bottom line is I'm in favor of stored procedures for database manipulation but against a set of simple minded CRUD procedures generated for every table in the database. Developers should be comfortable enough with SQL to write stored procedures (or have someone else write them) whenever they need to manipulate data. Reusing procedures is a good thing but using a procedure that doesn't fit what you need to be just to avoid writing a new procedure. Many database design tools will automatically generate CRUD procedures - mainly because it's pretty easy to do and looks impressive in the demo - but over using these prcedures can lead to highly procedural logic which won't perform nearly as well as a set based procedure or even a set based statement from the middle tier. That's why I get nervous when people make blanket statements about doing all SQL through stored procedures.
  

• TRID

  Hi

  Amazing discussin going on..

  Can Roger (or somebody else) can comment on his statement that Stored procedures doing only CRUD operations are poor performing.

  We need some technical details on the same as how it slow down the performance in this particular case

  Cheers

  
  

• tonofit

  "14. Enhance security by minimizing some forms of attack"

  Expanding on this

  • you can give a database user access to an SP and prevent them from executing direct "select" statements against a table. This is fine grain access control which will help control what data a user has access to
  • using basic select statement can leave you open to SQL Injection attacks - SPs can help you here http://en.wikipedia.org/wiki/Sql_injection

  
  
  

• Dan_Dan

  Another biggest advantage on SPs, you can increase the Security & control the SQL Injection...

  You can control the DB Objects more secured, giving permission on SPs rathere than the Direct objects. So using those credential they are allowed to execute the SP rather than controlling the real time tables using (Insert/Update/Delete)..

  
  
  

• wadnerk

  Roger's answer was well said. I've worked on applications with a dynamic "chatty" dal that was very hard to optimize. The get method of many objects was tied to a sql statment to read on column. Often simple crud operations aren't performed on all tables and set based operations are what's needed.

  In addition, the db can capture audit trails of what each user does and more importantly verify the state of the data and that the user has permission before allowing the operation. This is often done close to the data because most client apps now days are stateless.

  A dynamic sql DAL's can be very hard to optimize if t-sql best practices aren't followed. It's so hard to accurately determine what sql is called most often and make sure its indexed properly. You can run sql traces to capture workfiles but you never feel confident you fully understand what's going on.

  Using SP's in SQL 2005 applications will allow you to benefit from the runtime sql meta data that's new in sql 2005. This will make optimization so much easier.

  The biggest reason to use SP's is to maintain an abstraction layer between the database schema and the object schema. If the layers have schema coupling it's incredibly difficult to optimize the database design independent of the object model and vice versa. Preserving that quality is invaluable when maintaining an application over time.

  
  

• David Richards

  I don't think I said they were poor performing. A simple CRUD stored procedure performs at least as well as the equivalent SQL statement and probably a little better. What I was trying to point out was the danger of writting apps that used these simple CRUD procedures instead of a well written set-oriented statement. For example, calling a delete procedure thousands of times rather than writing a single statement that deletes all the rows older than a certain date. My issue with CRUD procedures is that people use them blindly instead of taking the time to think through the requirements and write efficint SQL statement whether they are in stored procedures or not.
  

• Benjamini

  Anything from Microsoft is going to almost always recommend using a stored procs. From what I have seen, there are only two disadvantages to using stored procs for CRUD:

  1. You have to actually write them and maintain them

  2. It ties your app to a specific RDBMS product (syntax is different in MS SQL, Oracle, Postgresql and no support in MySQL, etc.)

  #2 is often not really an issue for most enterprise apps that are firmly settled into using a specific rdbms.

  As for #1, keep in mind that there are many persistence frameworks out there that will automatically generate the CRUD sql statements for you, either from meta-data or pre-generated code.

  Performance wise, there is very little (if any) difference in speed that I have personally ever noticed between a stored procedure and a parameterized query that is just doing simple inserts, updates or selects by primary key.

  Hope that helps.

  
  

• Saania

  Actually for simple queries it doesn't make much difference whether you use sp or queries from app code.

  only when it comes to complicated queries you have to make decision between performance vs maintainability/IPR/Security.

  But CLR coming in SQL server may be that will also not be required.

  http://DotNetWithMe.blogspot.com
  vikas goyal

  
  
  

• Gwenna

  Does usage of Stored Procs affect scalability since it has often been lead to believe that the most expensive and the least scalable piece of the entire system is the database. Can anyone shed some light on this pertaining to scalability
  
  

• Pbr4Me

  In my opinion you should always try to use Stored Procedures as Part of the Logic Layer. I have always seen Stored Procedures as a Logic Service, in the same manner as a Web service. Separation of Logic through Stored Procedures would then be the same as Separating Logic in a Web Service. In stored procedures there is even a performance boost in some cases.
  
  There is an article I really like that also talks about this. I hope this helps.
  
  

• AlucardHellSing

  Why Consider Stored Procedures

  1. The first time the stored procedure is run, it gets compiled. This produces an execution plan—essentially a record of the steps that MicrosoftR SQL Server must take to get the results specified by the T-SQL in the stored procedure. The execution plan is then cached in memory for future use. This improves the performance of the stored procedure in that SQL Server does not need to analyze the code again to figure out what to do with it; it can simply refer to the cached plan.
  2. Performance : The cached execution plan used to give stored procedures a performance advantage over queries. However, for the last couple of versions of SQL Server, execution plans are cached for all T-SQL batches, regardless of whether or not they are in a stored procedure. Therefore, performance based on this feature is no longer a selling point for stored procedures.
  3. Stored procedures can still provide performance benefits where they can be used to reduce network traffic. You only have to send the EXECUTE stored_proc_name statement over the wire instead of a whole T-SQL routine
  4. Using stored procedures allows you to enhance execution plan re-use, and thereby improve performance,
  5. Are you using set-based operations, or doing other operations that are strongly supported in T-SQL Then stored procedures are an option, although in-line queries would also work.
  6. Are you trying to do row-based operations, or complex string manipulation Then you probably want to re-think doing this processing in T-SQL, which excludes using stored procedures,
  7. Maintainability and Abstraction: In a perfect world, your database schema would never change and your business rules would never get modified, but in the real world these things happen
  8. Also, by abstracting the implementation and keeping this code in a stored procedure, any application that needs access to the data can get it in a uniform manner. You don't have to maintain the same code in multiple places, and your users get consistent information.
  9. Another maintainability benefit of storing your T-SQL in stored procedures is better version control
  10. Another issue to consider is that using stored procedures to encapsulate business logic limits your application portability, in that it ties you to SQL Server. If application portability is critical in your environment, encapsulating business logic in a RDBMS-neutral middle tier may be a better choice
  11. Improve performance by reducing network traffic and stop sql injection attack also.
  12. Provide a single point of maintenance and can redo the SP without recompiling application plus take the pressure of the applicaiton (IIS server) and use a DB server to execute a procedure thus hardware wise also it is one up.I know in an instance where we use SP only because we wanted to use the full power of our DB server and distribute our workload.
  13. Abstract business rules for consistency and security as such we can have those % discount calculation changed any time you want during christmas time.
  14. Enhance security by minimizing some forms of attack
  15. Encourage execution plan re-use to know and optimize the data or even have db load balanced if the replan and reuse show a downfall if the workload is increasing.
  16. If your environment allows you to exploit the benefits stored procedures provide, as outlined above, I'd highly recommend them. They provide a good tool for improving the way data is handled in your environment. On the other hand, if portability, working heavily with non-T-SQL-friendly processes, or an unstable database schema negates these benefits in your case, you may want to consider other alternatives.

  
  
  

• Mateusz Rajca

  Hi,

  Not sure if we do have any MS URL which can state this fact out of the box, but surely you can find performance tips on MSDN which will surely help you in this decision.

  But I will take this opportunity to state my views here:

  Regarding Mixing of Layer:

  SP is always preferred way to do all operations in DB. Sorry so say, but my understanding is by using SPs, you are not mixing data layer with business logic layer. In my view SPs should be considered part of the BL Tier, and should be used in that way. Consider database as a two layered structure which has the core data storage and a layer on top of that which access the data as and when required. Now other app layers access this BL tier in DB directly or through other BL tiers. Once your SP start acting as BL tier, much of the DB related code moves inside them. So now we have all data related operations "nearer" to the data. Remember the old mantra from the gurus, all data intensive operations should be done as near to the data as possible. This holds true in this n-tiered architecture world also.

  Regarding performance consideration:

  As you have stated, SP will always have better performance output. SPs are compiled units and they are parsed, syntactically checked and execution plans already created. In case of arbitrary queries executed from DAL will be parsed and syntax checked every time the query is executed. If prepared statements are not used then a query execution plan creations also comes in play each time. So a new overhead.

  In my opinion and in opinion of many industry Gurus whom I have read, maintain that SPs should be used as a part of BL and majority of DB operations should be done in them.

  Hope this helps,

  Regards,

  
  
  
• Stored procedure vs standard query