LINQ to SQL (CE) speed versus SqlCe

Question

I'm making an application that will analyze real-time data that has been stored to a SQL CE database. When I test the application as it is built now, with LINQ to SQL, I get slow results and I need to rethink how to do this.

To save me some time, can I trust that L2S is just as fast as the 'old' SqlCe methodes were? I like L2S and would prefer to stay with it, and if your experience says it's as fast as any other db connection, I can rest assured that I wouldn't increase performance by rewriting the L2S to old SQL statements.

Answer 1

The bottlenecks when using SqlCE doesn't stem from the SQL generated from Linq to Sql. Remember, CE is an in process db and therefore has it's limitations. For example, LEFT OUTTER JOINS are a DISASTER regardless of what you use to query it. Inserts and Updates aren't bad, but then again, if you'll be doing a high volume of either one of those, you'll suffer some serious performance issues. My point is, the slowness isn't because of LINQ to SQL. I've benchmarked it in the past (don't know if I still have that code) and from what I remember, Linq to SQL wasn't slower then querying it directly with ADO.NET. The performance issues are due to the constraints of CE itself.

Answer 2

If you are using SQL CE this video from last year's PDC is very informational. The idea we have of how to optimize queries for full blown SQL Server not always apply, and sometimes detriment performance on SQL CE.

I would recommend you watch it, as the presenter explains the differences and does benchmarks to show the results. Here you can find a link to his blog.