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

1. Standard SQL Query (From * Select ...)
2. LinQ Query
3. 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

Answer 1

How are you measuring? Are you using all of the query results, or just executing the query? (LINQ may well be fetching the results for you, for example.)

Does this happen the second time you execute the query too, or are you just measuring the time taken for the first execution?

Answer 2

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.

Answer 3

Just make sure that your queries are not cached, that can skew the results.