Hello. I am programming a VB-driven application using a Sybase Adaptive Server 5.05. I have a transactions table where I need to do lots of calculations when showing some forms. Everything was OK until the table was filled with about 10,000 records. I started noticing a 10- to 15-second delay while searching or showing reports. The table had no indexes at all. I added a couple of indexes on some of the fields, and this improved the speed by a factor of 10.
I also read an article once stating that indexes speed up search and record movement, but slow down saves. My question is: How and when do indexes speed up operations? And why not put an index on every field?
QUESTION POSED ON: 12 APR 2005
QUESTION ANSWERED BY: Samuel Matzen
Indexes on tables with more than 1,000 records generally improve performance. Most SQL engines optimize the use of the indexes when they can. You should almost always put an index on a primary index containing unique keys so you can perform updates on the table. You understand correctly that keys will slow down adding new records because the keys must be inserted into the indexes, so it is a balancing act to decide what indexes to implement verses records to add speed. If the database is relatively static you can add as many indexes as you need to get the performance you are looking for. If the database is large and many new records are being added, the number of indexes should be kept to a minimum. When loading large databases, some database administrators will add the records to the table with no indexes and create the indexes when all records are added. It is much quicker to create a new index after the records are loaded than it is to add the keys to the indexes one at a time.
|
|
|
