What is the overhead associated with .NET stored procedures executing on Sql Server?

Question

Certainly there is some type of context switching, marshaling, serializing, etc that takes place when you choose to write a stored procedure in NET vs T/SQL.

Are there any hard facts about this overhead as it relates to making a decision about using .NET vs T/SQL for stored procedures?

What factors do you use in order to make the decision?

For me, developing in C# is 1000% faster than developing in T/SQL due to my learning curve, but when does this gain not offset the performance hit (if there even is one)?

Answer 1

The overhead is irrelevant in relation to switching, marshaling, etc, unless...your data is not already in the database.

That said, I tend to do almost no stored procedures do as much as possible with T-SQL -- wrapped in an ORM.

Really, that is the answer for there. Look into Entity Framework, NHibernate, Linq To SQL, etc. Get out of writing stored procs (I can hear the down votes now), and do it with C#.

EDIT: Added more info Stored procs are hard to test (inherently data bound), they offer not speed benefits over dynamic sql, and really are not any more secure. That said, for things like sorting and filtering, a relational database is more efficient than C# will be.

But, if you have to use stored procs (and there are times), use TSQL as much as possible. If you have a substancial amount of logic that needs to be performed, that is not set based, then break out C#.

The one time I did this was to add some extra date processing to my database. The company I was at used a 5-4-4 week-to-month fiscal calendar. That was almost impossible to do in SQL. So I used C# for that one.

Answer 2

It's sort of a religious question, but there can be substantial differences depending on what you're doing, your schema, and how many records you're doing it with. Testing is really the best way to find out for your circumstances.

In general, stored prcoedures will scale better; but that may not be true in your case, or that may not be an important consideration for you.

Answer 3

I'd say it depends.

Some things you will find using CLR procedres are better

1. Using native .NET objects -- file system, network, etc
2. Using features not offered by TQL or not as good as .NET e.g regular expressions

For yet others you will find TSQL procedures are better, either in terms of ease of use or raw execution speed

1. Using set based logic (where abc in def or abc not in ghi)
2. CRUD operations