• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

database Issue: optimising views

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author and iconoclast
Posts: 24207
46
Mac OS X Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic