Standard SQL Query much faster than Linq 2 SQL

Question

Hey there I got a problem with my code, I compared three ways of implementing a query on a database

Standard SQL Query (From * Select ...)
LinQ Query
Precompiled LinqQuery (CompiledQuery)

Now the result I got is that the Precompiled LingQuery is 5 times faster than the LinQ Query but much more interesting ist that the Standard SQL Query is approximately ten times fast than the Precompiled LinqQuery. The table that my Queries run against is one simple table with no associations to other tables.

The fact that makes the table interesting is that it consists of 5 columns which represent all together the primary key I mean. all columns together is unique so there is no row in the table that looks like the other. All columns are indexed.

Does anyone have an idea about what goes wrong with LinQ?

With kind regards

Sebastian

Arjan Einbu Arjan Einbu · Accepted Answer · 2009-04-17T13:54:18

Have you had a look at the actual query that linq generates? Comparing those with your SQL query might give you some answers.

You can do this either using SQL Profiler or listening to the linq datacontext's Out property? You can do it like this in a console app:

DataContext ctx = new ...;
ctx.Log = Console.Out;
//Now execute the linq query, and the query will be output to the console.

Standard SQL Query much faster than Linq 2 SQL

3 Answers