That isn't a simple question...
There are obviously overheads with LINQ-to-SQL (as with any ORM): it
needs to parse expressions and translate to sql, and there are
obviously a few layers in this stack (although perhaps no more than a
standard business/DAL setup); the raw query at the database should be
*broadly* comparable.
Custom developer-written SQL might sometimes (if done well) be faster,
but equally it could be a lot worse - and consider that LINQ-to-SQL
will automatically adjust to the server (for instance, "paging" will
use CTE on SQL 2005, but a TOP / subquery / TOP on SQL 2000) - so it
might take advantage of things your own SQL doesn't.
Then we need to consider composability: with LINQ-to-SQL, if you build
a query, then add additional constraints ("where", etc) or other
operations ("orderby", etc) - then all of that goes down in the same
query. Likewise if you do a projection in the select (i.e. you only as
for a couple of columns) - then only those columns are SELECTed in the
SQL; this reduces network IO, and server disk IO (compared to running
a fixed query, and then ignoring various rows/columns in the results).
And then we also need to consider development effort: if it is going
to take you 10 minutes to drop a few tables onto the designer surface
and use them, compared to a day-or-so to do similar by hand, writing
lots of very, very similar code...
So I'm quite interested in LINQ-to-SQL and Entity Framework - but
really, you are going to have to do performance tests on your own
data, using realistic queries, to truly get an answer. But don't
forget - all those (small) overheads are on you app-servers, which you
easily scale "out", rather than the database server which you have to
scale "up".
I asked Mike Taulty this exact question the other day (talking about
Entity Framework). His answer was more or less the same as my own
conclusions (above).
