CODEWITHSUNDEEP
c#performancelinq-to-sql
Svisstack
Svisstack

Reputation: 16616

LINQ to SQL - How to make this works with database faster

I have a problem. My LINQ to SQL queries are pushing data to the database at ~1000 rows per second. But this is much too slow for me. The objects are not complicated. CPU usage is <10% and bandwidth is not the bottleneck too.

10% is on client, on server is 0% or max 1% generally not working at all, not traversing indexes etc.

Why 1000/s are slow, i need something around 20000/s - 200000/s to solve my problem in other way i will get more data than i can calculate.

I dont using transaction but LINQ using, when i post for example milion objects new objects to DataContext and run SubmitChanges() then this is inserting in LINQ internal transaction.

I dont use parallel LINQ, i dont have many selects, mostly in this scenario i'm inserting objects and want use all resources i have not only 5% od cpu and 10kb/s of network!

Upvotes: 0

Views: 714

Answers (3)

Pleun
Pleun

Reputation: 8920

when i post for example milion objects

Forget it. Linq2sql is not intended for such large batch updates/inserts.

The problem is that Linq2sql will execute a separate insert (or update) statement for each insert (update). This kind of behaviour is not suitable with such large numbers.

For inserts you should look into SqlBulkCopy because it is a lot faster (and really order of magnitudes faster).

Upvotes: 2

grapeot
grapeot

Reputation: 1634

I also encountered this problem before. The solution I used is Entity Framework. There is a tutorial here. One traditional way is to use LINQ-To-Entity, which has similar syntax and seamless integration of C# objects. This way gave me 10x acceleration in my impression. But a more efficient (in magnitude) way is to write SQL statement by yourself, and then use ExecuteStoreQuery function to fetch the results. It requires you to write SQL rather than LINQ statements, but the returned results can still be read by C# easily.

Upvotes: 0

Michael Christensen
Michael Christensen

Reputation: 1786

Some performance optimization can be achived with LINQ-to-SQL using first off precompiled queries. A large part of the cost is compiling the actual query.

http://www.albahari.com/nutshell/speedinguplinqtosql.aspx http://msdn.microsoft.com/en-us/library/bb399335.aspx

Also you can disable object tracking which may give you milliseconds of improvement. This is done on the datacontext right after you instantiate it.

Upvotes: 1

Related Questions