What is better: sql string or stored procedures?

Question

I have a data access layer which uses the Enterprise Library.

Now I was wondering, which is better: Using stored procedures to create my SQL, or writing SQL strings within the data access classes?

Answer 1

It depends!

SP's have the benefit of better security and faster execution times. Strings have the benefit of rapid development and ability to make changes very quickly.

To answer your question, neither is really 'better'.

Answer 2

Bit of an old article, but worth the read.

http://articles.techrepublic.com.com/5100-10878_11-5766837.html

Answer 3

I would always try to use Stored Procedures, Functions, Views etc rather than keep the SQL inside my Data Access Classes.

If there is a small bug in the SQL, I find that it is much easier to change a Stored Proc on the SQL Server than than to change the class and have to rebuild and deploy it.

Plus there is the added benefit of caching and SQL Server Security.

Answer 4

These aren't the only two options if you are developing using C#.

You should also look at Entity Framework and NHibernate to see what they offer - as they have some advantages over and above both Stored Procedures and SQL Strings.

Whichever route you choose to take, the most important thing is keeping it all separated. Create objects to represent your data and use a repository to do any interaction with the database, including transforming the data into the objects to pass back to your application. This means that if you change your mind about SQL strings, stored procedures or ORM frameworks - you don't have to impact your entire application - you'll just need to replace the repository.

Repoisitory Pattern: http://martinfowler.com/eaaCatalog/repository.html

Entiry Framework: http://msdn.microsoft.com/en-us/library/aa697427%28VS.80%29.aspx

NHibernate: http://community.jboss.org/wiki/NHibernateforNET

Answer 5

There are several aspects to the answer: Performance, how well does it respond to changes, and how fast can new person figure out what's where without disrupting solution, development time, testing....

As for performance, stored procedures have a better execution time because they are precompiled and their execution plans are stored. The Sql text is compiled each time it's about to be executed (but, most of dbms are storing the plans for each query text so this impact is not that drastic).

How well can you maintain that solution? I've had worked on some relatively small projects (<40 tables), that had zillion of stored procedures - it was really a hell to modify anything since you can't know what other sp it will affect.

As for some mapper (like entity framework or similar) you will have to learn the tool first, but it really pays off - no writing sql, and if your dbmodel changes the mapper can update it's entities automatically so there's no "invalid column name" or similar errors.

Bottom line is - if you have time implement some ORM. If you don't then just use plain sql. Use stored procedures only in performance critical operations.

Hope it helps.

Best regards

Answer 6

Depends what you want to achieve, and how much data is involved.

From my personal experience if you have large amounts of data and you want fast processing use SP since it doesn't recompile each SQL statement every time you make a call. It doesn't make sense to implement a bunch of SPs if you only have small amounts of data.

If you don't have a lot of data that needs "db processing all at once" you might consider using some sort of ORM or plain SQL statements, because they'll probably fit better in your achitecture.

Answer 7

It's also valuable to mention that when you are using SQL statements, performance and security both gain from using parameterized queries.

Improved security is obvious, but performance is also improved. The DBMS (in the case of SQL Server) hashes the query's text (but not its parameters) to see if it already has an execution plan.

If your query is parameterized, the first execution will result in compilation, but for the second and any subsequent executions, the execution plan will have been cached since the query text is exactly the same.

On the other hand, if your query is plain SQL, every execution might yield a compilation because every query is slightly different.

Answer 8

Stackoverflow would be much better if there was a check box for

I need an enterprise class, big boy answer.

So many people who provide answers demonstrate that they aren't working in banking or utilities or investing or large retail or manufacturing. For example, all of these answers live in a simple world of one app per database. The second you add some complexity, these solutions show their warts. If you company needs a second application to use that database, you'll have to force that other development team to use your DAL or your ORM. What if they are using a technology which doesn't support your DAL or your ORM. Besides if you're here asking this question, you probably don't have the authority to force a standard on other teams.

BUT

If the other app/technology is coming to your database, then they CAN access your database. If your CRUD and data logic were in stored procedures, EVERY app would use the same logic, all in one place.

Several people said it was faster to make a change in the code rather than the database. Again, this is a very simplistic view of SDLC and seems primarily concerned with development and not maintenance. After it's left your box, it could be deployed on a thousand desktops. Or 40 app servers. But in the vast majority of cases, only one database. (Yes in the big boy world we have distributed databases too but apps are almost never in a single place, most databases are).

One answerer said that if you learn the ORM, you'll never need to learn SQL. SQL is a very valuable skill, it's more than worth learning, it's the life-blood of every business developer. Even companies that use NoSQL databases also use SQL databases, I'll bet you a pickle that Facebook and Digg have a RDBMS for AR/AP/GL.

Not to mention SQL Injection. The best way to avoid is never, ever dynamically build and execute SQL. And that's best accomplished with a Stored Procedure with parameters which are sized and typed appropriately and executed as bind variables in the SQL, not concatenated.