SQL-query slow in SQL Server CE but fast in SQL Server

Question

I'm working on a product where I need to support both SQL Server and SQL Server CE (4.0).

I have two tables where one is an entity and the other is relation-table, something like this:

Customer
id
name

question from:https://stackoverflow.com/questions/65649463/sql-query-slow-in-sql-server-ce-but-fast-in-sql-server

Answer 1

First of all, thanks a lot to @ErikEJ who was kind enough to help me out privately. Such a nice and helping person!

ErikEJ pointed out one thing that I did not know that SQLCE only uses one index per table and question which might be good to know for the future.

After fiddling around and reading up on this I came up with the idea to try a "CROSS APPLY"-query.

Something like this:

SELECT [id]
      ,[name]
      ,ot.total as totalOrders
  FROM [customer] as c
  CROSS APPLY (SELECT Count(o.id) as total FROM orders AS o WHERE o.customerId = c.id) as ot
ORDER BY id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

This query executes in about 1ms on SQL Server and somewhere around 300ms on SQLCE. In terms of performance, in this case, it's still around 225% faster to fetch the first 50 rows and based on them fetch the COUNT() with a group query. But if one needs to sort on the count (which id need) the CROSS APPLY-query seems to be the best option.

Thanks to everyone who took the time to help me!