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

9

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.

Adeel

출처 c# asp.net performance linq-to-sql
  • +1 I dont think its LINQ to SQL as a whole but instead the fact its so deep, and so easy to do wrong. A misunderstood use of a collection property in a loop can lead to big performance issues fast. - Tj Kellie

3 답변

8

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.

  • @Gravel && @Byers. one question. suppose we have 3 servers. 1 for read, 1 for write and the other is a replication server. so if i use linq for read operations and for write operation i use queries by hand. Is this the best solution? or is there any other best solution. what you suggest - Adeel
  • @Adeel - db servers or app servers? It sounds like you mean the db server - but I'm not sure that the specific data-access API used is going to make a huge difference (compared to the different db setups) - Marc Gravell♦
  • You can always use SPs / UDFs for read, too (via L2S)... - Marc Gravell♦
  • @Gravel Server means Database Server. As linq approach is very slow for write operations compared to data access api, while for read the performance overhead is negligible. so it it better to use linq for only READ purposes? - Adeel
  • @Adeel - certainly it is better at read than write; it really depends on the scenario, though; most code isn't issuing large amounts of DML. If you do have this, then command batching is desirable, but you might also consider firing the data down to the DB in bulk (for example xml or SqlBulkCopy, or in SQL 2008 table-valued parameters) and process it using set-based updates/inserts at the db. Only the first of these is available via L2S. - Marc Gravell♦
4

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.

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.

Linked

Related

Latest