SQL Stored Procedures vs. InCode TSQL

[Adriaan Davel]

I have been coding in SQL for 10+ years, I am very aware of what a stored procedure is.

GuyThiebaut wrote:

Stored procedures in SQL Server use TSQL so just use IF statements within the stored procedure.

Ever seen what happens to performance when an IF statement send processing outside of a pre-compiled execution plan? I don't even know what you mean by saying that a stored procedure uses TSQL, we are comparing sending TSQL strings from an application as code versus calling a stored procedure in the database.

GuyThiebaut wrote:

Calling a stored procedure or running SQL directly via .Net requires a connection the command and parameters - so I can't see what this additional object is that needs managing.

1. You have a table in SQL to manage (security) 2) You have a stored procedure to manage (security) Has nothing to do with creating connections, are you suggesting that the LOB application manage security to SQL objects?

GuyThiebaut wrote:

What's this thing with comparing TSQL and stored procedures(see my response above - a stored procedure can contain TSQL)?

You don't seem to be understanding the question. If you are referring to the code in the stored procedure, I don't understand what you mean by "can contain TSQL", what else can it contain? If you are referring to calling dynamic SQL in a stored procedure, yes I agree, but that is detail we have not discussed and was not asked.

____________________________________________________________ Be brave little warrior, be VERY brave

[Sam Gorman]

Really can't believe people still advocate logic in stored procedures. Great way to get bugs that are very painful to track down. I wouldn't advocate stored procs or embedded SQL. Look at all the ORMs (NHibernate, entity framework etc). Also get your architecture sorted. My view is that the business logic goes in the middle tier (Web service). Then you have your unit test in that tier.

[Terry gilman]

These arguments sound a lot like the old debate between writing in C or writing in Assembler. Perhaps it's time for someone to develop a compiler which compiles (insert favorite coding language here) into stored procedures as needed to eliminate the need to write and maintain them ourselves.

[Lost User]

Putting business logic into a stored procedure is a very bad design. It's not 1990 client server anymore. There is a business object layer for that.

[code_junkie]

TSQL is only for INSERT, UPDATE, and DELETE statements, anything else should be avoided. They only exception that should be considered is network load and the fact that you have to share that bandwidth with everyone else. $.02

[Jonathan Shields]

Hi if you are not using EF or similar, stored procedures are vastly preferable except for simple statements which don't use user input. Keeping your data access logic separate from your business logic is good practice and being able to make changes to your data access logic without doing a release is handy. This is in the real world where mistakes do happen, design has to be rethought and changes have to be made in timescales you don't like. If you are in the Entity Framework world, you can still use them but its harder. Personally I prefer accessing SQL stored procedures directly from c# (still creating separation using a DAL project). Jonathan

[SteveOg]

Adam Machanic wrote an excellent article about this about eight years ago. To SP or not to SP in SQL Server: an argument for stored procedures, The database-as-API approach I don't really have much to add to this excellent article other than that if you are making a decision about using stored procedures on SQL Server versus not using stored procedures, don't do it based on "performance" - there is no difference on modern servers. Do it because you want a securable, testable, maintainable API to your database. Your application may not need that. If you don't need it, then you don't need to use stored procedures.

[RonDsz]

In my opinion; common re-usable code that manipulates the data and need performance at the database level needs to reside in the database in the form of stored procedure and triggers where the queries can be tweaked. If it is business logic then it should be in the middle tier. Data that needs to be modified and inputs validated resides at the client level avoiding unnecessary network round-trips. There are several other factors the DBA and architect have to take into account like latency, network round trip etc. All this should come with experience. Both Database and application group should work in tandem; The reason one will not agree with other are for reasons listed below: 1. politics 2. job security 3. bragging rights 4. blame game thanks.

[Michael Abramovitch]

This is a great answer! thanks Shuqian Ying. old topic though. A database server is a very expensive resource, and typically there's only ever one. stored procedures have a use I am sure, but on large systems where you need high concurrency, they are a killer. it would be like using your relational database server as a database server and an application server--just because you can, doesnt mean you should. if your app logic runs in c# code on an application server, then to scale the app would often just mean throwing in another cheap app server or two. The best illustration i have seen on how to properly scale apps from the db through to the clients is from an albeit quite dated book by Joseph Moniz: "Enterprise Application Architecture with VB, ASP, MTS". the first 100 pages which is on architecture and scaling, are still relevant today, and worth the effort to obtain the book and read. There are other really good reasons not to use stored procs if possible, two that I can think of are: lower all-around development costs (dev, testing, promotion, maintenance, versioning), and database-vendor neutrality (T-SQL wont run on Oracle; but DML SQL is at least very similar across db vendors).

[StatementTerminator]

I don't think that debugging has much to do with it, you would normally debug the SQL in SMSS anyway. Personally, I use a mix of parameterized in-line queries and SPs. There are advantages/disadvantages to both, and both have their place. If I'm just doing something like looking up a user's name to display, I'll often use inline SQL to do a quick query. No need to create a SP for every simple task, plus it's more convenient to have the SQL right there. However, if I'm doing something that will be done in other places, like looking up a customer's account info, I'll make that a SP. Why? Because that way you have one place with code to pull that data and you can maintain it there instead of digging into the C# code to find all the snippets of SQL that are doing the same thing (I guess you could do the same thing by creating a class that uses inline SQL, but at that point there's no reason not to make it a SP and every reason to do so). This makes things a hell of a lot easier if you, say, make changes to the accounts table and need to update the SQL accordingly. Performance is an issue as well, but the big problem probably isn't what you're thinking of: the real problem with heavyweight inline SQL is that page processing stops while you wait for the SQL to run. This--waiting for the SQL to run--is the main reason for performance problems on a website. Do you really want a complex query hitting heavy-use tables holding up your page loads? Sometimes you can't help it, but other times you can: if you make the query a SP then you get more than just caching, you can easily run it in a different thread and keep it from slowing down the presentation side. I've done this with things like running complex reports and generating tax forms: execute the SP in a different thread, dump to processing table, then display the data when it's ready. A nice view of the processing table with a status of "pending" is a lot better than staring at a spinning circle wondering what's going on.

[ClockMeister]

Yeah, but in either case you have to roll it out to all clients. That, unless the database is a host for multiple clients, then there might be an advantage. Whether you change code in the DB (requiring some kind of update script) or patching the executable code, the difference is highly debatable. Either update process can be boilerplated.

[andegre]

Hi Michael, I'd be very curious if you could expand a little bit on your reply. I'm the architect of the application at my business (inherited the architecture, which is in desperate need of re-architecting because of performance concerns). We have over 2000 stored procedures that implement ALOT of business logic. On the webservice side, we are running 25 application servers, with the 3 threads per server to try and handle the load. According to SQL Server (Quest Performance Analysis), it's handling everything just fine, but the response time in general of the application is very poor. Right now, our only way to try and improve the performance is by adding more application servers into the pool.

[GuyThiebaut]

CodeBubba wrote:

unless the database is a host for multiple clients

Yes - that is where I am coming from, an environment with one database that serves multiple clients.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[ru55r3353]

Personally, I'm with your architect. The most common reason given for using stored procs is that it provides some performance benefit but I have never seen that proven. The second reason that people like it is because it provides a layer of abstraction. Instead of looking at the SQL in your code, you simply call a descriptive SP like "getListOfThings" or "SetThingName". That does provide a benefit, especially if you have a large team with various experience levels working in the code - it makes the code more readable - but you can get the same benefit using other techniques. I personally have found that having a view of the actual SQL right there in the code is very valuable. It saves you the time of having to write SP's and/or go look up the SP to figure out if the bug you're looking for is in the code or in the SQL. A few years ago there was a lot of interest in Object Relational Models and basically finding a way to get the SQL into the code in a way that would allow the compiler to throw errors when there are mistakes in the SQL itself. I have not heard a lot about it lately But that whole concept sort of proves the point that having visibility of the SQL in your code is desirable. One last "benefit" that is often cited for using SP's is that you can fix a bug without having to do a new build of the app. And while that is true, there is a dark side to that as well. Many times the SQL needs to change from one build to the next. When you use SP's, you sometimes end up with "getListOfThings_1" and "getListOfThings_2" because you need one SP that works for one build and a different version of the SP that works with the next build. If you maintain the actual SQL in with the code, then the correct SQL for each build lives with that build.

[jschell]

Sam Gorman wrote:

My view is that the business logic goes in the middle tier (Web service).

Presumably you mean that in terms of best practices and not as an absolute. There are of course other reasons for using stored procedures besides business logic. But in larger systems there can be cases, with other mitigating circumstances, where business logic would go in the database. And this of course ignores business logic that can naturally live in the database - such as uniqueness constraints.

[jschell]

Ahmad Dabo wrote:

he says that there is no need for stored procedures because we can't debug it (within c#)

Hopefully their knowledge in other areas does not suffer from the same ignorance and/or wrong headed bias. There certainly might be reasons one might not want to use stored procedures but that most definitely isn't it.

Ahmad Dabo wrote:

except that stored procedures are precompiled and ready to execute (which I know it is a huge advantage)

First one must presume that this does not just become an excuse to due app processing versus database processing. If you move the entire database (effectively) across the wire to do processing then that is a problem. You can of course write SQL in the executable that doesn't require data moving. However I suspect that at some point you can reach a complexity level that is going to be hard to read the code unless you are using some external source to load the SQL from. Even then it might not be possible to produce an effective solution.

[jschell]

SteveOg wrote:

if you are making a decision about using stored procedures on SQL Server versus not using stored procedures, don't do it based on "performance" - there is no difference on modern servers

I seriously doubt that in terms of actual practice. A senior programmer who actually knows a great deal about both sides would likely get the same performance with either solution. However an excellent DBA might be able to get even better performance. And a middle level programmer is unlikely to get close. They might have similar problem with doing it in a stored proc but in a case like that one can contract a good DBA to optimize stored procs much easier than doing the equivalent with embedded SQL. This also presumes that one spots the problem early. Many businesses, especially with complex applications don't do code reviews and don't do significant (or even any) performance testing before they hit problems in production. Consequently one can find that a implemented solution that could have been done much more effectively in a number of ways as a database only process has been implemented with simple CRUD and extensive application processing. I have seen this happen numerous times.

[crazedDotNetDev]

There is little difference in how a modern SQL Server processes a stored procedure vs. inline TSQL. Most of the older “rules of thumb” not longer apply. Still, there are significant differences between the two methods. The biggest I’m aware of is the location the query executes and the thought processes involved. Where does your inline TSQL execute; is it inside of SQL Server, in your executable, or both? The answer isn’t always clear and matters for scalability… and it probably matters a lot. I believe the biggest (and most overlooked) is the thought processes involved in writing TSQL vs. procedural/object-oriented code. Most people jump on the multi-tier, client/server, SoC, etc… bandwagons. Procedural/object-oriented code tells a computer to do “x”, then “y”, then “z”. On the other hand TSQL can be best description as Set Theory (Set Theory) for a database. The two have completely different strengths and weaknesses. Also, don’t forget .NET doesn’t have access to a CPU’s SIMD (SIMD) instructions. SQL Server does. I’d have a hard time finding an example where SQL Server couldn’t aggregate raw data faster than .NET code. IMHO… just use the best language for the algorithm you’re trying write.

- great coders make code look easy

[ClockMeister]

In your case, then, yeah - updating the SP's makes the most sense. Of course you have to watch the API!

[PIEBALDconsult]

GuyThiebaut wrote:

IF statements within the stored procedure

That's a code smell.