aspose file tools*
The moose likes JDBC and the fly likes Improving performance of view  Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Improving performance of view  " Watch "Improving performance of view  " New topic
Author

Improving performance of view

Parameswaran Thangavel
Ranch Hand

Joined: Mar 01, 2005
Posts: 485
Hi all
I liked to have pointers on improving the performance of view.
And also i liked to know to which extent indexing the table will help in improving the performance of view.
Say like Table1, Table2. The view is created based on Table1 and Table2.

so whether i need to index the table for each and every combination of column or single index including all the columns will be sufficient.
i.e say Table1 has 3 col, Col1, Col2 and Col3.
so indexing the Table1 on
Create index1 on Table1 (col1)
Create index2 on Table1 (col1, col2)
Create index3 on Table1 (col1,col2, col3)

will having the index like above will improve the view performance. or just the index3 having col1, col2 and col3 is enough.

Please note that i need to create view for 5 tables, each table with 7 column and combination of all the seven are considered unique. and also i had a million of records to return.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Parameswaran,
It doesn't really make sense to talk about the overall performance of a view. Similarly, it doesn't make sense to talk about the overall performance of a table because you can't optimize all scenarios simultaneously. Making queries faster makes updates slower due to the extra work in maintaining the index. This may or may not be significant. But it needs to be considered.

So what are you trying to optimize? A specific query? Try writing out the query without the view and see what fields are being accessed or joined. Those fields would benefit from an index.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
 
subject: Improving performance of view