I wanted to know how index on a table works. I have a table which is inserted, updated and deleted from one application only and is being selected from 10 Application.These 10 application use different where clause to select from the table.So I was planing to apply Index on the table based on the where clause in select query.
But what is the limit on the number of index on a particular table. Does more number of index affect the insert, update and delete operations done by Application 1.
And I am planning to apply index because I am getting timeout exception in Application 1 and other Applications also.
Will applying more index cause performance problems in Application 1 as the Application 1 updates, inserts more than 15-20K rows in one day and these queries are complex.
The purpose of the index is to aid in query - aka select performance - by providing a means of direct access to a row or range of rows.
However, indexes will slow down performance to some extent. For inserts, every row inserted into the table will also have to add keys / RIDs to any indexes on the table. Updates will only impact indexes if the update of the row changes any of the index keys. Deletes, have to delete the index keys / RIDs and the data row.
When creating an index(s) on a table, for performance reasons any unique indexes should be created first. The number of indexes is limited to 32767, although the maximum I have seen was around 200.
In your case 15-20k inserts per day is not large. I know of one system which does this amount of inserts per minute in one table with 12 indexes and HADR / WRS.
Also will the deadlock and timeout issues will be solved by creating some 10 index on the table?
And to correct the figure the table undergoes around 15-20K (Updates, Inserts, Select) per hour.The update, delete and insert operations are done by one application and select is done by around 10-15 applications.Also there are triggets on the table and they update/ insert and delete in other tables where the other 10-15 Applications also perform the select/update/ insert.