What are the pros and cons to keeping SQL in Store

Question

What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to establish which, for this particular project, would be best.

So far I have:

Advantages for in Code:

• Easier to maintain - don't need to run a SQL script to update queries
• Easier to port to another DB - no procs to port

Advantages for Stored Procs:

• Performance
• Security

Answer 1

In my opinion you can't vote for yes or no on this question. It totally depends on the design of your application.

I totally vote against the use of SPs in an 3-tier environment, where you have an application server in front. In this kind of environment your application server is there to run your business logic. If you additionally use SPs you start distributing your implementation of business logic all over your system and it will become very unclear who is responsible for what. Eventually you will end up with an application server that will basically do nothing but the following:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

So in the end you have your middle tier running on this very cool 4 Server cluster each of them equipped with 16 CPUs and it will actually do nothing at all! What a waste!

If you have a fat gui client that directly connects to your DB or maybe even more applications it's a different story. In this situation SPs can serve as some sort of pseudo middle tier that decouples your application from the data model and offers a controllable access.

Answer 2

CON

I find that doing lots of processing inside stored procedures would make your DB server a single point of inflexibility, when it comes to scaling your act.

However doing all that crunching in your program as opposed to the sql-server, might allow you to scale more if you have multiple servers that runs your code. Of-course this does not apply to stored procs that only does the normal fetch or update but to ones that perform more processing like looping over datasets.

PROS

1. Performance for what it may be worth (avoids query parsing by DB driver / plan recreation etc)
2. Data manipulation is not embedded in the C/C++/C# code which means I have less low level code to look through. SQL is less verbose and easier to look through when listed separately.
3. Due to the separation folks are able to find and reuse SQL code much easier.
4. Its easier to change things when schema changes - you just have to give the same output to the code and it will work just fine
5. Easier to port to a different database.
6. I can list individual permissions on my stored procedures and control access at that level too.
7. I can profile my data query/ persistence code separate from my data transformation code.
8. I can implement changeable conditions in my stored procedure and it would be easy to customize at a customer site.
9. It becomes easier to use some automated tools to convert my schema and statements together rather than when it is embedded inside my code where I would have to hunt them down.
10. Ensuring best practices for data access is easier when you have all your data access code inside a single file - I can check for queries that access the non performant table or that which uses a higher level of serialization or select *'s in the code etc.
11. It becomes easier to find schema changes / data manipulation logic changes when all of it is listed in one file.
12. It becomes easier to do search and replace edits on SQL when they are in the same place e.g. change / add transaction isolation statements for all stored procs.
13. I and the DBA guy find that having a separate SQL file is easier / convenient when the DBA has to review my SQL stuff.
14. Lastly you don't have to worry about SQL injection attacks because some lazy member of your team did not use parametrized queries when using embedded sqls.

Answer 3

Stored procedures.

If an error slips or the logic changes a bit, you do not have to recompile the project. Plus, it allows access from different sources, not just the one place you coded the query in your project.

I don't think it is harder to maintain stored procedures, you should not code them directly in the database but in separate files first, then you can just run them on whatever DB you need to set-up.

Answer 4

When it comes to security, stored procedures are much more secure. Some have argued that all access will be through the application anyway. The thing that many people are forgetting is that most security breaches come from inside a company. Think about how many developers know the "hidden" user name and password for your application?

Also, as MatthieuF pointed out, performance can be much improved due to fewer round trips between the application (whether it's on a desktop or web server) and the database server.

In my experience the abstraction of the data model through stored procedures also vastly improves maintainability. As someone who has had to maintain many databases in the past, it's such a relief when confronted with a required model change to be able to simply change a stored procedure or two and have the change be completely transparent to ALL outside applications. Many times your application isn't the only one pointed at a database - there are other applications, reporting solutions, etc. so tracking down all of those affected points can be a hassle with open access to the tables.

I'll also put checks in the plus column for putting the SQL programming in the hands of those who specialize in it, and for SPs making it much easier to isolate and test/optimize code.

The one downside that I see is that many languages don't allow the passing of table parameters, so passing an unknown number data values can be annoying, and some languages still can't handle retrieving multiple resultsets from a single stored procedure (although the latter doesn't make SPs any worse than inline SQL in that respect).

Answer 5

Stored Procedures are MORE maintainable because:

• You don't have to recompile your C# app whenever you want to change some SQL
• You end up reusing SQL code.

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

What happens when you find a logic error that needs to be corrected in multiple places? You're more apt to forget to change that last spot where you copy & pasted your code.

In my opinion, the performance & security gains are an added plus. You can still write insecure/inefficient SQL stored procedures.

Easier to port to another DB - no procs to port

It's not very hard to script out all your stored procedures for creation in another DB. In fact - it's easier than exporting your tables because there are no primary/foreign keys to worry about.

Answer 6

Advantages for in Code:

• Easier to maintain - don't need to run a SQL script to update queries
• Easier to port to another DB - no procs to port

Actually, I think you have that backwards. IMHO, SQL in code is pain to maintain because:

• you end up repeating yourself in related code blocks
• SQL isn't supported as a language in many IDE's so you have just a series of un-error checked strings performing tasks for you
• changes in a data type, table name or constraint are far more prevalent than swapping out an entire databases for a new one
• your level of difficulty increases as your query grows in complexity
• and testing an inline query requires building the project

Think of Stored Procs as methods you call from the database object - they are much easier to reuse, there is only one place to edit and in the event that you do change DB providers, the changes happen in your Stored Procs and not in your code.

That said, the performance gains of stored procs is minimal as Stu said before me and you can't put a break point in a stored procedure (yet).