What Are the Pros and Cons to Keeping SQL in Stored Procs Versus Code

What are the pros and cons to keeping SQL in Stored Procs versus Code

I am not a fan of stored procedures

Stored Procedures are MORE maintainable because:
* You don't have to recompile your C# app whenever you want to change some SQL

You'll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can 'transparently' change the SQL out from underneath your app is pretty small on the whole

• You end up reusing SQL code.

Programming languages, C# included, have this amazing thing, called a function. It means you can invoke the same block of code from multiple places! Amazing! You can then put the re-usable SQL code inside one of these, or if you want to get really high tech, you can use a library which does it for you. I believe they're called Object Relational Mappers, and are pretty common these days.

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

Agreed, which is why storedprocs are a bad thing. It's much easier to refactor and decompose (break into smaller parts) code into functions than SQL into... blocks of SQL?

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

Why are your windows apps connecting directly to a central database? That seems like a HUGE security hole right there, and bottleneck as it rules out server-side caching. Shouldn't they be connecting via a web service or similar to your web servers?

So, push 1 new sproc, or 4 new webservers?

In this case it is easier to push one new sproc, but in my experience, 95% of 'pushed changes' affect the code and not the database. If you're pushing 20 things to the webservers that month, and 1 to the database, you hardly lose much if you instead push 21 things to the webservers, and zero to the database.

More easily code reviewed.

Can you explain how? I don't get this. Particularly seeing as the sprocs probably aren't in source control, and therefore can't be accessed via web-based SCM browsers and so on.

More cons:

Storedprocs live in the database, which appears to the outside world as a black box. Simple things like wanting to put them in source control becomes a nightmare.

There's also the issue of sheer effort. It might make sense to break everything down into a million tiers if you're trying to justify to your CEO why it just cost them 7 million dollars to build some forums, but otherwise creating a storedproc for every little thing is just extra donkeywork for no benefit.

What are the pros and cons to keeping SQL in Stored Procs versus Code

I am not a fan of stored procedures

Stored Procedures are MORE maintainable because:
* You don't have to recompile your C# app whenever you want to change some SQL

You'll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can 'transparently' change the SQL out from underneath your app is pretty small on the whole

• You end up reusing SQL code.

Programming languages, C# included, have this amazing thing, called a function. It means you can invoke the same block of code from multiple places! Amazing! You can then put the re-usable SQL code inside one of these, or if you want to get really high tech, you can use a library which does it for you. I believe they're called Object Relational Mappers, and are pretty common these days.

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

Agreed, which is why storedprocs are a bad thing. It's much easier to refactor and decompose (break into smaller parts) code into functions than SQL into... blocks of SQL?

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

Why are your windows apps connecting directly to a central database? That seems like a HUGE security hole right there, and bottleneck as it rules out server-side caching. Shouldn't they be connecting via a web service or similar to your web servers?

So, push 1 new sproc, or 4 new webservers?

In this case it is easier to push one new sproc, but in my experience, 95% of 'pushed changes' affect the code and not the database. If you're pushing 20 things to the webservers that month, and 1 to the database, you hardly lose much if you instead push 21 things to the webservers, and zero to the database.

More easily code reviewed.

Can you explain how? I don't get this. Particularly seeing as the sprocs probably aren't in source control, and therefore can't be accessed via web-based SCM browsers and so on.

More cons:

Storedprocs live in the database, which appears to the outside world as a black box. Simple things like wanting to put them in source control becomes a nightmare.

There's also the issue of sheer effort. It might make sense to break everything down into a million tiers if you're trying to justify to your CEO why it just cost them 7 million dollars to build some forums, but otherwise creating a storedproc for every little thing is just extra donkeywork for no benefit.

Is it better to write sql query as a stored procedure or write it inside C# and execute it dynamically

"Better" is opinion-based. But there are hard facts:

Stored procedures can be compiled by the database. That means you have syntax checking. Comma missing or field mistyped? In a stored procedure this will bring up a compile time error, in your program it will be a runtime error.

SQL code that is in the program can not be optimized beforehand. For the database, a statement from the program is random. It may be cached and maybe the database is intelligent enough to see that the statement never changes, but if it's inside a compiled stored procedure, it can be compiled and prepared. Once on compile. Not once per call.

If you change the database, your stored procedures will become invalid. Right there on the spot. It's obvious. If you have code in the program, it may generate a runtime error when the user clicks a button three weeks later.

Having it all in code is easier to handle for the developer.

And there are probably more pros and cons. You will have to weight them all and decide for yourself.

Edit:

This question is tagged sql-server and therefore the comments are appropriate. Please note that my answer above was programming language and DBMS agnostic. They describe what can be done. MS-SQL at the time of this post is a pretty good DBMS but there are others that existed for decades and are way more optimized than MS SQL. MS SQL may not support those features I'm talking about.

If you put your SQL in code, from the perspective of the DBMS it's a new query every time. It may use statement caching to optimize it for every call that is inside it's caching mechanism scope. However, it will never be able to tell that your are using the very same statement every time, instead it will need to parse the statement and explicitely check against all it's cached statements. If you put your statement inside the database, the DBMS has the chance to compile the statement and use a mechanism where it does not need to check any statement cache because it knows that this statement is the same. It's the difference between an interpreted language and a compiled language if you will.

What is the downside of using stored procedures all the time?

A good article on the subject

http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

So I think you should do what you prefer. There is no performance difference (for msot of the query you'll have to run).

I'd say go for no stored procedure : stored procedure are a pain in th a.. :

• no overloading : If you want to add a parameter you'll have to update all your calls (or create a new SP)

• no complex type : with dynamic sql you can build all your sql filter like you want depending on your complex objects

• securiy is not a reason : if your sql query are sql injection proof and your database is not available for everybody, you can handle your data access security policy at the application level (any dba would kill me saying this, but any dev would agree... I guess)

SP are "pre-compiled" (at the first execution, the database server will find the best execution plan, for SQL server), BUT in our time we can forget about it, the "compilation" time is really little so we don't have to worry about it. I never saw a case when I thought "OMG the compilation time is my application bottleneck", most of the time your application bottleneck will be the query itself, so don't worry about performance when you don't have to.

And this "pre-compilation" depends on the parameters you send to the SP (on the first call), so sometimes you can have a lot of performance problem (called "parameter sniffing") with SPs (see here :http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html).

Pros and Cons putting all the logic in Stored Procedure (SQL Server)

I found some of stored procedure having some performance issue when increase number of user

when stored procedures/any code runs slow, you may need to find the cause by seeing wait stats and execution plan.

Also you said, this happens when load is high,then you need to further check which metric is high..Is it CPU,RAM,IO and start your troubleshooting from there

What are the pros and cons to writing DB2 stored procedures in SQL versus Java?

Not just Java but any procedural language: procedural, that's the key.

DB2 is a relational algebra, not a programming language. If you need to do something complex, it's better to use a procedural language than try to bend SQL into something it's not meant to be.

We actually use REXX for our DB2/z work but I've seen guys here use bash on the mainframe for quick prototyping. Some who only use SQL create all these horrible temporary tables for storing things that are best kept in structures and away from the DB.

My thoughts are that complexity is the only differentiating factor between using SQL directly in a stored procedure and a procedural approach. We have two rules of thumb when making these decisions.

1/ If a unit of work requires more than two trips to the database, we make it a stored procedure.

2/ If a stored procedures creates a temporary table, then we use a procedural language to do it.

---

Related Topics