This week's book giveaway is in the Jython/Python forum.
We're giving away four copies of Murach's Python Programming and have Michael Urban and Joel Murach on-line!
See this thread for details.
Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

database Issue: optimising views  RSS feed

 
rita mistry
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First get the query plan.

Study the query plan to make sure the database engine is using the optimal index on each table. If not, you may be able alter the sql to use an index (i.e., by joining to another table you may be able to get additional columns to enable you to use an index on one of the original tables in the query.)

If there is no optimal index, you can add one.

If you are unable to optimize the query further, you can consider changing the view to a real table. This would cause a problem if the tables the view is based on are changing and you need to see the changes real time.

Some database like Oracle support "materialized views" that give you the best of both worlds -- table-like performance and updates like a view.
 
Virag Saksena
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. What query are you running against this view
2. What is the execution plan
3. What are the statistics on the table
If you are using Oracle, can you include the tkprofed results of the trace file of query execution
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good point, Virag. I forgot about collecting statistics.

Rita, make sure statistics have been collected recently on the table. That can make a big difference in the query plan the optimizer chooses.
 
Ernest Friedman-Hill
author and iconoclast
Sheriff
Posts: 24215
37
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sudheer,

Please don't post your questions into someone else's thread; start a new thread for a new topic like this. Furthermore, this really wasn't the right forum for the original question, and it's absolutely not the right place for yours. Please go post this question in the JDBC forum.
 
Tauri Valor
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ernest,

Im sorry I realised that my Question was unrelated to the one already there.. Thanks for the observation !! Im moving my post to JDBC forum.

Thanks,
Sudheer
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!