09-12-2007 2:41 PM
Hi Experts,
How can we improve the performance of the select with out creating an secondary index?
In my select query am not using primary fields in where condation.
so i want to know that how can we improve the performance .
one more thing is that if we r creating secondary index what are the disadvantages of that?
Thanks & Regards,
Amit.
09-12-2007 2:56 PM
Please check:
<a href="/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields">Using an Index When You Don't Have all of the Fields</a>
Creating a secondary index will cause any transaction that updated the table to slow down slightly. When the table is updated, the new index has to be maintained as well.
The new index will also take up some of the table space assigned to the table.
Rob
09-12-2007 2:56 PM
Please check:
<a href="/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields">Using an Index When You Don't Have all of the Fields</a>
Creating a secondary index will cause any transaction that updated the table to slow down slightly. When the table is updated, the new index has to be maintained as well.
The new index will also take up some of the table space assigned to the table.
Rob
09-12-2007 3:35 PM
If you select from a table without using an appropriate index or key, then the database will perform a table scan to get the required data. If you accept that this will be slow but must be used, then the key to improving performance of the program is to minimise the number of times it does the scan of the table.
Often the way to do this is not what would normally be counted as good programming.
For example, if you SELECT inside a loop or SELECT using FOR ALL ENTRIES, the system can end up doing the table scan a lot of times because the SQL is broken up into lots of individual/small selects passed to the database one after the other. So it may be quicker to SELECT from the table into an internal table without specifying any WHERE conditions, and then delete the rows from the internal table that are not wanted. This way you do only a single table scan on the database to get all records. Of course, this uses a lot of memory - which is often the trade off. If you have a partial key and are then selecting based on non idexed fields, you can get all records matching the partial key and then throw away those where the remaining fields dont meet requirements.
Andrew