Win a copy of React Cookbook: Recipes for Mastering the React Framework this week in the HTML Pages with CSS and JavaScript forum!
  • 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
  • Ron McLeod
  • Paul Clapham
  • Rob Spoor
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Holloway
  • Piet Souris
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Himai Minh

measuring db2 view performance

 
Ranch Hand
Posts: 156
  • 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?

db2
 
Saloon Keeper
Posts: 24189
166
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic