Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

10774 - bad performance (index scan)

Hi experts

i have this query

select soh.AccountNumber, sod.LineTotal, sod.OrderQty, sod.UnitPrice,p.Name
from Sales.SalesOrderHeader soh
            Inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
            inner join Production.Product p
on sod.ProductID = p.ProductID
where sod.LineTotal > 1000;

That should create indexes, thanks

view execution plan attached.

sp_helpindex 'Sales.SalesOrderHeader'
index_name      index_description      index_keys
AK_SalesOrderHeader_rowguid      nonclustered, unique located on PRIMARY      rowguid
AK_SalesOrderHeader_SalesOrderNumber      nonclustered, unique located on PRIMARY      SalesOrderNumber
IX_SalesOrderHeader_CustomerID      nonclustered located on PRIMARY      CustomerID
IX_SalesOrderHeader_SalesPersonID      nonclustered located on PRIMARY      SalesPersonID
PK_SalesOrderHeader_SalesOrderID      clustered, unique, primary key located on PRIMARY      SalesOrderID

sp_helpindex 'Sales.SalesOrderDetail'
index_name      index_description      index_keys
AK_SalesOrderDetail_rowguid      nonclustered, unique located on PRIMARY      rowguid
IX_SalesOrderDetail_ProductID      nonclustered located on PRIMARY      ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID      clustered, unique, primary key located on PRIMARY      SalesOrderID, SalesOrderDetailID

sp_helpindex 'Production.Product'
index_name      index_description      index_keys
AK_Product_Name      nonclustered, unique located on PRIMARY      Name
AK_Product_ProductNumber      nonclustered, unique located on PRIMARY      ProductNumber
AK_Product_rowguid      nonclustered, unique located on PRIMARY      rowguid
PK_Product_ProductID      clustered, unique, primary key located on PRIMARY      ProductID
execution-plan.png
exution-plan.csv
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of enrique_aeo
enrique_aeo

ASKER

SSMS suggest
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX IDX_SOD
ON [Sales].[SalesOrderDetail] ([LineTotal])
INCLUDE ([SalesOrderID],[OrderQty],[ProductID],[UnitPrice])
GO

seen in plan execution using the new index.
You can not do anything else? to improve scan
execution-plan-better.png
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial