• 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 ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
  • Mikalai Zaikin

measuring db2 view performance

Ranch Hand
Posts: 160
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to compare the performance of a DB2 view before and after adding an index. So I am trying to measure the performance of it using below query:

As you might guess, the idea is to measure how much time the above query takes to complete in both cases. i.e create a temp table with the required data.

Can I please get some feedback on how good this method is for comparison?

Saloon Keeper
Posts: 27885
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Views come in 2 flavors: standard and materialized. A standard view is basically an alias for a standard query, so the amount of time it takes to access the view should be approximately the same time as doing the query directly.

A materialized view, on the other hand is a snapshot taken at a fixed time, and so it's essentially a brand new table. I don't know that such a view would inherit indexes or constraints offhand. I'd probably query the schema to make sure. And try to find whatever formal assurances the documentation may offer.

Since a standard view is just a shorthand query, the index/no-index performance is tied to whether you have an index on the underlying table(s) - plural because a popular use of Views is to normalize JOINs.

Raw timing is a start, but the DB2 server is smart enough to optimize things if it sees the same query over and over, so a more accurate measurement would be to run EXPLAIN.
    Bookmark Topic Watch Topic
  • New Topic