Do we still need stored procedures when using compiled queries?

Question

When using compiled queries in entity framework (or linq-to-sql) in combination with SQL Server, is there actually still any performance benefit in using stored procedures?

Compiled queries will be cached as parameterized queries, so performance should be near equal to stored procedures. Is there any situation where stored procedures would perform significantly better?

-- EDIT --

In response to Yakimych's answer below, I didn't mean to imply that compiled queries are the same as stored procedures. I am trying to figure out if sprocs are still necessary if you have done all possible optimizations on the application side (in this case compiled queries). So I guess I'm looking for reasons why a stored procedure would be better than the combination of application-side optimizations and parameterized queries (which is what compiled queries effectively are).

One of the reasons I'm asking this, is because there are many people who seem to think that stored proedures are no longer necessary for different reasons (i.e. this post).

Answer 1

First of all, compiling EF queries has nothing to do with performance benefits that can be achieved by using stored procedures.

According to http://msdn.microsoft.com/en-us/library/cc853327.aspx - the following operations occur when a query executes against a conceptual model:

• Loading metadata
• Opening the database connection
• Generating views
• Preparing the query
• Executing the query
• Loading and validating types
• Tracking
• Materializing the objects

And the explanation regarding Preparing the query:

Includes the costs to compose the query command, generate a command tree based on model and mapping metadata, and define the shape of the returned data. Because Entity SQL query commands are cached, later executions of the same query take even less time. You can also use compiled LINQ queries to reduce this cost in later executions.

So, if you compile the query and re-use it later, what happens is that you save time on this operation in your application during every subsequent query execution. What you don't do, however, is you don't influence the generated SQL code that is executed against the database. The performance benefits you get when compiling queries are at the application level.

On the other hand, you would typically use stored procedures in case you aren't satisfied with the generated SQL code and would like to optimize performance at the database level.

EDIT in response to your comment and edit.

It seems to me that you are under the impression that compiling an EF query would somehow change the generated SQL code that will be run against the database (you mention that compiled queries result in parametrized SQL queries?). That is not the case. No matter whether you run the query directly or use compiledQuery.Invoke, the same SQL code will be run against the DB. Furthermore, you don't have full control over it, you rather rely on the ORM to generate it in the best possible way. In some cases it is not optimal, and this is where SP's come in.

So to sum up:

• Compiling queries is purely an application-side optimization. It saves the time to compile a query that gets re-used in the code.
• Stored procedures can be used to tweak your SQL code and get it to match your goal as closely as possible, thus providing a possibility to get the best performance at the database level.

In no way is one technique a substitute for the other.

Answer 2

"Is there any situation where stored procedures would perform significantly better?"

Given a comparable piece of parametrized SQL generated in either EF or a stored proc, they will perform equally.

However, a DBA always has the opportunity to further optimise a query based on their experience with the DB schema and its usage patterns. A stored procedure allows them to do this easily in isolation of the applications using it, whereas an ORM doesn't.

We have an extremely complicated SQL Server DB that has many external systems replicating data in and out via triggers. The issue for us with EF is that the responsibility for the SQL that gets fired at the DB will become the application developers responsibility when using any ORM rather than the DBAs.

Answer 3

A few canned answers from well known experts: Paul Nielsen Why use Stored Procedures?

Adam Machanic: No, stored procedures are NOT bad