Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Improving performance of view

 
Parameswaran Thangavel
Ranch Hand
Posts: 485
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic