CODEWITHSUNDEEP
linq-to-sqlsql
Soni Ali
Soni Ali

Reputation: 19010

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

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?

Upvotes: 1

Views: 93

Answers (3)

Amy B
Amy B

Reputation: 110111

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();

Upvotes: 0

Rohrbs
Rohrbs

Reputation: 1855

Count on a single index rather than *

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 1

Related Questions