Indexing can affect the performance of a general-purpose database that has rough
ID: 3552420 • Letter: I
Question
Indexing can affect the performance of a general-purpose database that has roughly equal numbers of fetches and changes. The decision to add an index can improve the performance of a system but can also degrade it.
With these thoughts in mind, address the following:
When would creating an index reduce overall database performance? Explain.
Describe and explain a situation where an index would increase the performance of some database operations but degrade the performance of other operations.
How could you resolve a situation in which you are not sure whether an index will help or harm overall database performance?
Explanation / Answer
The basic thought of creating a index is to increase the search performance, but as well while creating an index , it occupies certain area of memory in the system.
That is why it is called "index can improve the performance of a system but can also degrade it".
For example , If I have a table containg 100Kb of data , but after creating an index its size increase slightly around 110Kb. Also before creating index if the search takes 100 Sec to fetch the data , after creation of the index on the searching column, it takes 10 Sec appx. So that is why it is called index increase the performance but degrades the system.
1. Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes need to be updated.
Analyze your application workload and create indexes as necessary to improve the performance of the most critical processes.
The Adaptive Server query optimizer uses a probabilistic costing model. It analyzes the costs of possible query plans and chooses the plan that has the lowest estimated cost. Since much of the cost of executing a query consists of disk I/O, creating the correct indexes for your applications means that the optimizer can use indexes to:
Avoid table scans when accessing data
Target specific data pages that contain specific values in a point query
Establish upper and lower bounds for reading data in a range query
Avoid data page access completely, when an index covers a query
Use ordered data to avoid sorts or to favor merge joins over nested-loop joins
In addition, you can create indexes to enforce the uniqueness of data and to randomize the storage location of inserts.
2. There are several points you must consider when choosing indexes. Each index you define can improve performance of a SELECT query, but on the other hand can decrease performance of INSERT and UPDATE queries. The reason for this is that SQL Server automatically maintains index keys. Therefore, each time you issue a data modification statement, not only data modification SQL Server provides, but also index it updates each index defined on affected table. The performance degradation is noticeable in case of large tables with many indexes., or few indexes with long keys. In some cases it is suitable to drop index before updating or inserting new records and than recreate index. This is in case of large tables where you need to update or insert a large amount of data. You will find out that insert into table without indexes will be much more faster than into table with indexes.
Hence Some cases araises where system memory get filled with insert statements and get the memory disruptions into the system.
3. We can decide ourselves to create index or not , first before creating an index we should ensure that there is sufficient memory in the system so that incase of data increase in future can also be taken cared. Then we have to choose proper index depending on the search criteria used in the queries. In production or real time evnvironments the space is always monitored by the dba and also the performance of the queries are monitored continously.