开发者

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

开发者 https://www.devze.com 2023-01-16 19:50 出处:网络
Given the following tables: Orders (OrderID, OrderStatus, OrderNumber) OrderItems(OrderItemID, OrderID, ItemID, OrderItemStatus)

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 开发者_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();
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号