Niroj Dahal

Indexing can be seen in analogy to a book.

If no indexing is present in the book, all the content of the books is to be read in order to get to the required lesson/chapter of the book.Whereas, if indexing (table of contents) is available in the book , then we can reach directly to the required lesson.

So, indexing can limit the time from searching the content in the entire book to only the section where it is required.

My Experience

I had a stored procedure that had the following query

SELECT Id ,(SELECT Max (v.dDate)	FROM	
	( 
		 SELECT TOP(1)  COALESCE(DateTime,LE.ModifiedDate) AS dDate FROM TableA WHERE LEId=LE.Id ORDER BY Id DESC UNION
		 SELECT LE.ModifiedDate UNION
		SELECT TOP(1)  COALESCE(DateTime,LE.ModifiedDate) AS dDate FROM TableB WHERE LEId=LE.Id ORDER BY Id DESC 
	) AS v
) FROM MainTable LE

TableA had the total count of 92205 rows and TableB has the total count of 12817 rows.

Basically, what this query is trying to do is get the maximum modification date from the combination of MainTable,TableA and TableB .

The result without indexing is

Notice the execution time is 09 seconds.

Since, the ModifiedDate is compared with the LEId of Main Table (foreign key to MainTable from TableA and TableB), I added index to column LEId in both TableA and TableB using the queries below

CREATE INDEX ix_TableA_LEId
ON TableA (LEId);

CREATE INDEX ix_TableB_LEId
ON TableB (LEId);

The result of executing the same stored procedure after adding indexing using the above queries is

Notice the execution time is less than a second.

The result is dramatic.

But be careful while using indexing. Indexing must be used in proper column to get its benefits. Also extensive use of indexes in a table can lead to many disadvantages

1. Additional Disk Space
2. Data modification (insert/update) might be slow

Thank you for reading !!