Deciding on LINQ to SQL vs StoredProcs

While developing applications, I usually go for Stored Procedures to con开发者_开发知识库tain CRUD logic, so as improve performance and maintainability. But after experimenting with LINQ to SQL, I was wondering whether, using compiled LINQ-to-SQL queries over stored procedures will that help improve performance?

LINQ to SQL will not improve your performance, because you will be sending each CRUD operation as a string over the wire.

Performance will still be better with Stored Procedures, but ORM's like Linq to SQL usually make development time faster.

From my experience, I can rank performance as following:

1. Stored procedures
2. Native queries (using DBCommand)
3. Linq to entity (compiled query, EF4)
4. Linq to SQL (compiled)
5. Linq to entity (not compiled EF4)
6. Linq to SQL

7. ESQL

   2,3,4 may change their order depends on the nature of the queries, but in general raw sql query is executed fater.

Based on your comments to both DevSlick and a1ex07, it seems you have a fundamental misunderstanding of what LINQ is. In order for LINQ queries to allow chaining, like

var activePeople = peopleList.Where(o => o.Active).OrderBy(o => o.Ordering).Select(o => o.Name);

the execution of the LINQ query must be delayed until it is enumerated:

foreach(var person in activePeople)
{
   //If this is LINQ-to-SQL, the query to peopleList has waited until now to request anything from the database
}

This means that the query .Where(o => o.Active).OrderBy(o => o.Ordering).Select(o => o.Name) is not actually interpreted by your computer until that point as well. If you run the same query 100 times, that means the computer has to reinterpret that query 100 times. For LINQ-to-SQL, that means translating the query to SQL 100 times before that SQL is sent to the database each time, even if the SQL is exactly the same every time.

Compiling the query ahead of time causes it to generate the SQL only once, and use that SQL every time the query is called. This has nothing to do with stored procedures - you would compile a query-to-a-stored-procedure in the same way that you would compile any other query. Asking "which gives better performance" is meaningless, as they are not mutually exclusive.

Though compiling a query sounds like a good thing, in practice interpreting a LINQ query (usually called "evaluating the expression tree") takes very very little time compared to actually executing the SQL against the database, so you get very little benefit for compiling the query. In the meanwhile, the syntax for compiling a query is atrocious:

static readonly Func<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>> s_compiledQuery2 = 
    CompiledQuery.Compile<AdventureWorksEntities, Decimal, IQueryable<SalesOrderHeader>>(
        (ctx, total) => from order in ctx.SalesOrderHeaders
                        where order.TotalDue >= total
                        select order);

var orders = s_compiledQuery2.Invoke(context, totalDue);

For this reason, it is usually recommended to simply not compile your LINQ-to-SQL queries, because the ratio of code-noise-to-benefit is terrible.