Question

In 250-500 words evaluate the times it took for the system to process the insertion of the records and determine which type of insert ran the fastest (with or without index) and why. As part of this, advise on your recommended approach. This should be evaluated from a performance perspective solely for inserting data.

Answer 1

Back ground

The importance of using index in database can be well explained by the following example

consider a book having many chapters without index page.
searching of chapters will take more time.
the purpose of index in a book is to provide the page number of every chapter. a book reader can first see the index page to get the page number of a particular chapter,and can search the chapter directly with the help of page number provided by the index of the book.

similarly, in data base index can be used to search a record very fast.
index can increase the speed of select query(retrieval of records), but insert operation, in the presence of indexes will become slow.this can be well explained with the example of book example.

consider a book having no index, and one wants to add a new chapter in a book,this chapter can be added easily and quickly in the book without taking care of the index.

if there is index in a book,then addition of a new chapter also requires an update in the index of the book which makes addition of new chapter slower.

in the same way, if indexes are present, then insert operation will be slower because of extra overhead for indexes

Ans

indexes are not good for insert operation.
insert operation gives poor performance in the presence of index.
if there is a need to increase the performance of insert operation, then number of indexes should be minimized

it can be well explained in the following manner
addition of a new row in a table takes few steps
in the first step database finds a free space to store this new row.

if there is a regular heap table which does not follow any particular row order,database takes any table block(having enough space) that can accommodate this new row.this is a very fast process,most of the times it happens in main memory.

if indexes are present with the table, database makes sure the new entry is found due to these indexes.now it will add the new entry to each index on that table.
so if indexes are present, the insert operation is a costly affair.