LINQ to SQL (CE) speed compared to SqlCe

Question

I am creating an application that will analyze real-time data that has been stored in a SQL CE database. When I test the application as it builds, with LINQ to SQL, I get slow results, and I need to rethink how to do this.

To save time, can I assume that L2S is as fast as the old SqlCe methods? I like L2S, and I would rather stay with it, and if your experience talks about it as fast as any other db connection, I can be sure that I will not increase performance by rewriting L2S ​​into old SQL statements.

Answer 1

Bottlenecks when using SqlCE are not related to SQL created with Linq to Sql. Remember that CE is a db process and therefore has limitations. For example, LEFT OUTTER JOINS is DISASTER, regardless of what you use to query it. Insertions and updates are not bad, but again, if you do a large volume of one of them, you will encounter serious performance problems. I want to say that slowness is not related to LINQ to SQL. I compared this in the past (I don’t know if I have this code), and from what I remember, Linq to SQL was not slow, and then query it directly using ADO.NET. Performance issues are related to the limitations of CE itself.

Answer 2

If you use SQL CE this video from last year, PDC is very informative. The idea that we have about how to optimize queries for a full-blown SQL Server is not always applied, and sometimes it worsens the performance of SQL CE.

I would recommend you watch it, as the facilitator explains the differences and makes tests to show the results. Here you can find a link to your blog.