SQL - Make this statement faster i.e. less duration, reads

Question

Given the following tables:

Orders (OrderID, OrderStatus, OrderNumber) 
OrderItems(OrderItemID, OrderID, ItemID, OrderItemStatus)

Orders: 2537 records Order Items: 1319 records

I have created indexes on

1. Orders(OrderStatus)
2. OrderItems(OrderID)
3. OrderItems(OrderItemStatus)

I have the following SQL statement (generated by LinqToSql) which when executed, has: - duration = 8789 - reads = 7809.

exec sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Orders] AS [t0]
WHERE ([t0].[OrderStatus] = @p0) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[OrderItems] AS [t1]
    WHERE ([t1].[OrderID] = [t0].[OrderID]) AND ([t1].[OrderItemStatus] = @p1)
    ))',N'@p0 nvarchar(2),@p1 nvarchar(2)',@p0=N'KE',@p1=N'KE'

Is there anything else which I can do to make it faster?

Answer 1

make all those nvarchars parameters varchars if the columns in the table are varchars

))',N'@p0 varchar(2),@p1 varchar(2)',@p0=N'KE',@p1=N'KE'

See also here: http://stackoverflow.com/questions/3688015/sp-executesql-causing-my-query-to-be-very-slow/3688124#3688124

Answer 2

Count on a single index rather than *

Answer 3

This might generate some better sql.

IQueryable<int> query1 =
  from oi in db.OrderItems
  where oi.OrderItemStatus == theItemStatus
  select oi.OrderID;

IQueryable<int> query2 =
  from o in db.Orders
  where o.OrderStatus == theOrderStatus
  select o.OrderID;

IQueryable<int> query3 = query1.Concat(query2).Distinct();

int result = query3.Count();