should linq to sql be used for websites that have high traffic

Question

I have read many articles about linq to sql performance. The result which i got is it is slower than normal approach(DAL or Microsoft Enterprise library). Slower for both read and write operations even after performance tuning like disable ObjectTracking and other tricks. I know it has prons like quick development, clean code etc but what about the performance.

What if i used only for read operations.

Please give your suggestions.

Answer 1

It seems to work well enough for stackoverflow ;-p Especially if you use compiled queries, this is unlikely to be your bottleneck, compared (for example) to appropriate DB design and fetching the right columns / rows, and avoiding n+1 loading.

Answer 2

For read operations LINQ To SQL should be roughly as fast as directly writing the SQL because that's exactly what it does. The overhead of creating the SQL shouldn't be noticable. There might be a few queries that it doesn't get as optimal as if you wrote the query by hand, but in my experience it does very well in most cases.

For bulk updates, LINQ To SQL is typically slower because it handles rows one at a time. You can't do something like UPDATE Foo SET x = 0 WHERE id BETWEEN 100 AND 200 in LINQ to SQL without fetching all the rows. It's currently best to write the SQL by hand for this type of operation.

Answer 3

The updates and deletes is where LINQ to SQL currently suffers since a separate statement is generated for each affected object.

That said, this blog post details how to get both operations down to 1 statement, which should help improve performance: Batch Updates and Deletes with LINQ to SQL.

Compiled queries will also come in handy for frequently used queries, especially those where parameters are used to get specific results. You might also find this post helpful: 10 Tips to Improve your LINQ to SQL Application Performance.