Optimizing views

[Reet81]

Hi,

I'm having an issue with my view. It is joining to seven tables and as a result it has significantly caused this view to run extremely slowly. I need to use all these tables to retrieve the data but can anyone possibly suggest a way that would speed up running this view.

The view query is as follows:

SELECT TOP (100) PERCENT OMS.ID, OMS.omOrderDate, OMS.omPart, OMS.omDesc, OMS.omOrderNumber, OMS.omCustomerID, OMS.omQuantity,
OMS.omCostPrice, OMS.omSellPrice, OMS.omDistStock, OMS.omDistID, OMS.omPO, OMS.omETA, OMS.omPriority, OMS.omShipdate,
OMS.omConsignment, OMS.omNotes, cust.name, cust.lastName, sup.supplierName, Status.statusName, OMS.omStatusID, prod.Manufacturers,
cust.email, prod.idProduct, OMSps.costPrice, OMSps.stockLevel, OMSps.supplierID, sup2.supplierName AS spSupplierName
FROM OMSystem AS OMS LEFT OUTER JOIN
dbo.suppliers AS sup ON OMS.omDistID = sup.idSupplier INNER JOIN
dbo.customers AS cust ON OMS.omCustomerID = cust.idcustomer INNER JOIN
Status ON OMS.omStatusID = Status.statusID INNER JOIN
dbo.products AS prod ON OMS.omPart = prod.MFID COLLATE Latin1_General_CI_AS LEFT OUTER JOIN
OMS_ProductSupplier AS OMSps ON OMS.omPart = OMSps.partCode LEFT OUTER JOIN
dbo.suppliers AS sup2 ON OMSps.supplierID = sup2.idSupplier
ORDER BY OMS.omOrderNumber DESC


Many thanks Reet

 

[r937]

one way to speed it up is to drop the ORDER BY

and also drop that TOP 100 PERCENT, that always strikes me as, um, how should i put this, silly



do you have indexes defined on all the join columns?

r937.com | rudy.ca