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
- Orders(OrderStatus)
- OrderItems(OrderID)
- 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 开发者_JAVA百科to make it faster?
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: sp_executesql causing my query to be very slow
Count on a single index rather than *
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();
精彩评论