Using Linq with Stored Procedures
I want to schedule a data access layer and I decided to use Linq. I read that linq has its own performance issues and that you can use stored procedures with linq. Should I use stored procedures with linq when planning my data access layer? Is this important for performance? When should I use them? I know stored procedures are necessary for security, but linq uses type safe queries, so the only reason for stored procedures is performance.
Thank,
Omri
source to share
From what I've read, the server caches execution plans for queries generated by LinqToSql, so those queries will execute in much the same way as equivalent stored procedures. And if you use stored procedures, you lose the benefits you get with LinqToSql, as your query logic stays in the database.
See Rico Mariani 's LinqToSql performance blog series for more, as well as this blog post .
source to share
If you are just writing a simple CRUD, stored procedures are unlikely to help you with performance. As far as I know, the execution path of a stored procedure is almost the same as for a prepared statement - that is, it is optimized and cached accordingly.
They go a long way when you can work entirely on a database that would otherwise involve multiple return trips to the database (with network traffic and latency).
Where did you read that LINQ has performance issues? I am aware of one specific issue that was fixed by pre-compiling the request, but other than that I thought it was mostly ok ...
source to share
I would recommend going with LINQ or stored procedures, but not both - there is no real reason to combine the two, and you won't benefit from a performance standpoint. LINQ will be useful for small web applications, stored procedures for a larger web application or for better scalability
The problem is not only about performance as the web application grows in terms of the number of users, pages served, etc ... it is also a maintenace problem. If your queries get more complex over time, your LINQ code can become a maintenance nightmare. Stored procedures remain beautifully hidden in the database layer. And ... no matter what anyone says, a query executed at the database level is ALWAYS faster.
source to share