linq performance: linq vs t-sql -- where does the time go?

Question

I am testing the Linq performance now and I cannot figure out what for some time is wasted (MS-SQL Server 2005).

So here is what I have: single table with clustered and non-clustered indices, all searches are done using columns which are covered in 100% by non-clustered index. I have 10 000 records, and all operations "touches" all records one by one. And now the times:

T-SQL:

• checking if each record exists (IF EXISTS...); yes, it exists -- 0:30 (30 seconds)
• updating each record -- 1:16

LINQ:

• fetching single records (SingleOrDefault) -- 5:58
• updating each record (it contains the fetching part) -- 9:34

Ok, checking for existence is not actually equivalent Linq and T-SQL but what really bothers me is the fact:

LINQ(fetch)+TSQL(update) < LINQ(update)

Also note that real update part is faster than fetching alone!

The updating in LINQ is already optimized to use primary (clustered) index not entire table (WHERE clause).

So, where did 2 minutes go?

EDIT

When answering please answer the question why is such difference between LINQ and TSQL. Please do not discuss subject like: TSQL is for sets, you should do it in a batch (all records in once), you should make TSQL faster. Thank you.

edit2:

The structure of the table is pretty easy: id (PK), Name (int, external index), bit field + 40 fields or something like this

To focus on specific query I run such loop:

declare @i int;
declare @nr int;
declare @p1 bit;
declare @p0 int;

set @i = 0;
set @nr = 1000000000;

while @i<10000
begin
  select @p0=id,@p1=bit_field from test_mono where name=@nr;
  set @p1 = 1- @p1;
  update test_mono set bit_field=@p1 where id=@p0;
  set @i = @i+1;
  set @nr = @nr-1;
end

A little comment to that: bit_field here serves me as assurance that something will be modifier for sure. Counters which go up and down are from real data, I had to make sure with each select I get one record. And the last one -- with LINQ the only difference is SELECT, I fetch all fields (UPDATE is copied&pasted). However I can take time of LINQ SELECT and add it to this time (of TSQL) and I should get LINQ SELECT+UPDATE. But I don't get such time.

With previous edit I described a bit different structure, the reason is I try to make this as clear as possible. I don't like the examples of seriously real cases myself, so I'll try to make things easy for readers ;-)

Answer 1

You might start by using a tool such as LINQPad to see what the actual SQL queries are being executed by LINQ.

Personally though, I've seen this time and time again, thus I tend when performance is the top priority I stay with TSQL.

More than likely the generated SQL has some oddities in it. We would need to get more information though on the nature of your queries to get an idea of what you might be working with.