optimising views
-
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
-
Reet
Just a few ideas:
What indexes do you have on the seven underlying tables? Look at the execution plan of the query and look for table scans or clustered index scans. Use the Index Tuning Wizard to suggest some indexes for you.
Can the outer joins be replace by inner joins? Why do you have an ORDER BY in your view definition?
I noticed that you have qualified some table names with the owner. Qualifying the others as well may give a small performance improvement.
Take out the TOP (100) PERCENT - you don't need that. And if all the columns in all the tables have the same collation then you also don't need the COLLATE statement.
John
-
Thanks for your help John I will try what you have suggested and hopefully that will resolve my issue.
Many thanks
Reet
-
As well as following Johns advice you sould create an indexed view. See code below i have guessed some details about you data inparticulare the the columns to place the index over. this must be a candidate key. Obviously the code below is untested but this should speed the view up considerably.
If you are using SQL200 you will need to use count instead of count_big i think..
create
view your_view
with
schemabinding
AS
SELECT
count_big(*) as_count, 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 OMS
LEFT
OUTER JOIN dbo.suppliers sup
ON OMS.omDistID = sup.idSupplier
JOIN
dbo.customers cust
ON OMS.omCustomerID = cust.idcustomer
JOIN
Status
ON OMS.omStatusID = Status.statusID
JOIN
dbo.products AS prod
ON OMS.omPart = prod.MFID
LEFT
JOIN OMS_ProductSupplier OMSps
ON OMS.omPart = OMSps.partCode
LEFT
JOIN dbo.suppliers sup2
ON OMSps.supplierID = sup2.idSupplier
ORDER
BY OMS.omOrderNumber DESC
go
CREATE
UNIQUE CLUSTERED INDEX [IX_V_your_view] ON [dbo].your_view
(
omCustomerID
ASC,
omDistID
ASC,
omDistID
ASC
)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
-
Jules
Unfortunately that won't work. From Books Online (Creating an Indexed View):
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
.
.
.
- Outer or self joins.
This is one of the reasons I asked whether the outer joins were necessary.
John
-
Hi Guys,
I have double checked and the only outer join that I need is the first one. The reason for this is that some of the values in OMS (omDistID) are NULL, so if there is a way to get round this would I be able to use the method that Jules has suggested?
Cheers
Reet
-
Reet
What I'd say about indexed views is that just because you can create one, it doesn't necessarily mean you should. In my opinion, it's more important to have proper indexing on your underlying tables, after which the performance of your existing view may reach a level that is acceptable, making an indexed view (and its overheads) unnecessary.
But I think the answer to your question is yes. Check the long list of conditions in the Creating an Indexed View topic just to make sure. And by all means, experiment with it (but not on your production server), and if you find that the indexed view gives the best performance, then go with it.
John
-
Bet you a quid that it is...
reet -But how you going to get rid of that Left join?
It would certainly not be advisable to use any kludge ('or' join predicates for example)in getting rid of the left join. As John says check your indexes (is there an index on every column that is used in the join predicates?), get rid of the order by, and remove all unnecesscary left joins.
-
Cheers for all you help guys. I really appreciated it!
-
Hi John and Jules,
I wanted to thank you both very much. I ran the index wizard and implemented the recommendations and performance has improved by 85%!!!!
Absolutely marvelllous!!!
Thank you!
Reet x
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply