SQL Server CE - bad performance

Question

I'm developing an application that uses a SQL Server CE database and I am having trouble with the performance of the queries. For example, a simple query that gets the customer's last order:

SELECT 
    Customer.Name, Orders.Amount AS LastOrderAmount
FROM   
    Customers
LEFT JOIN 
    Orders ON Orders.OrderId IN (SELECT TOP(1) OrderId
                                 FROM Orders 
                                 WHERE CustomerId = Customer.CustomerId 
                                 ORDER BY OrderNum DESC)

This query is incredibly slow. With only 30 costumers and about 300 orders it takes almost 10 seconds to finish!

I'm querying using SqlCEDataAdapter. I also tried using SqlCeResultSet, but the difference is negligible. Also, I have an index on Orders.CustomerId (creating it didn't make much difference, though).

Now, I'm not expecting miracles from SQL Server CE, but this is just terrible. So, is there something I can do to improve performance or is it just THAT slow?

Answer 1

Try this:

SELECT cus.Name
     , outerOrd.Amount AS LastOrderAmount
  FROM Customers cus
  LEFT JOIN Orders outerOrd on outerOrd.CustomerId = cus.CustomerId
 WHERE not exists (SELECT *
                     FROM Orders innerOrd
                    WHERE innerOrd.CustomerId = cus.CustomerId
                      and innerOrd.OrderId > outerOrd.OrderId 
                  )

Edited, probably this is better:

SELECT cus.Name
     , ord.Amount AS LastOrderAmount
  FROM Customers cus
  LEFT JOIN Orders ord on ord.CustomerId = cus.CustomerId
 WHERE ord.OrderId = (SELECT max(OrderId)
                        FROM Orders
                       WHERE CustomerId = cus.CustomerId
                     )