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

Order
id
customerId
created

I need to create a list of all customers together with the total number of orders.

Since there are limitations in SQL Server CE, for example one can't use sub queries in the select-statement I figured I could query like this:

SELECT [id]
      ,[name]
      ,ordersCount.total as totalOrders
  FROM [customer] as c
  LEFT JOIN(
        SELECT customerId, Count(o.id) as total FROM orders AS o GROUP BY customerId
  ) as orderCount ON c.id = orderCount.customerId
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

This query works in both SQL Server CE and SQL Server and it's quite fast if the number of rows are low, but when we're having lot's or rows the query is getting really slow on SQL Server CE, even when using "FETCH NEXT 10 ROWS ONLY" to only fetch 10 rows. I guess that the LEFT-join is performed on all the rows even when I just fetch 10.

Executing the "parts" of the query takes less than 100ms,

SELECT customerId, Count(o.id) as total FROM orders AS o WHERE GROUP BY customerId

Takes 50ms on SQL Server CE

SELECT [id]
      ,[name]
      ,ordersCount.total as totalOrders
  FROM [customer] as c

Takes 80ms on SQL Server CE

But when I execute the whole query with the LEFT JOIN the query takes over 2 minutes (about 14 000 customers with 17-18 000 orders.

The execution plan indicates that "Nested Loops" takes out 100% of the time for the processing in SQL Server CE. But I can't seem to figure out why?

I have indexes on:

• customer.id (PK. Includes: customer.id)
• order.customerId (Included order.customerId)

I'm wondering if there is some way to make the query faster on SQL Server CE? And also, could I query in another way and get the same result but more efficient?

EDIT: I tried to divide the query into two queries, first getting the 50 customers that are about to be shown, then querying for the count.

SELECT [id]
      ,[name]
  FROM [customer] as c
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Then taking the ids from these 50 and query for the count:

SELECT customerId, Count(o.id) as total 
FROM orders AS o 
WHERE customerID IN ([ids from query above])
GROUP BY customerId

This executes in around 200ms.... it's sooo strange.

Answer 1

Split in to two queries, one to get the customer ids, and one to get the related orders and make sure you have good indexes for both queries.

If you a fetching all customers, consider fetching the count for all orders, to avoid a large IN condition.

Happy to help you investigate if you share database and queries privately.

Answer 2

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 SQL Server CE 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 1 ms on SQL Server and somewhere around 300 ms in SQL Server CE. 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!