Increase database performance with proper indexing (A practical experience explained)

Relational databases used index to find out data quickly. An Index is an underlying structure that determines how data is stored. Proper Indexing can increase your database's overall performance when your database becomes larger. Recently I have worked with a medical customer who uses software for their Pathology department developed by other party. Problem with that software was that it becomes slower by the end of month when data size overcomes 150MB. I was engaged to solve the problem.

I have changed inline queries to stored procedure which increased performance a bit. But when I have created Index, performance increased drastically.

Let’s see how to identify fields for indexing:

I have identified queries on tables that have heavy data load. Then I have identified fields of the table that are not primary key or don’t contain foreign key reference but they are used in query's where clause or group by clause. For example, I have a table named "RequestCompositList" with primary key field "ID" and I have following queries on this table that is called frequently:

SELECT ID, ReqID, CID,
FROM RequestCompositList
WHERE ReqID='0152435' AND CID ='546466'

or

SELECT Count(ID), ReqID
FROM RequestCompositList
GROUP BY ReqID

In this table I have created two non-clustered index on RequestCompositList table for example: IX_RequestCompositList_ReqID and IX_RequestCompositList_CID, this drastically increases my query performance on this table.

I kept some consideration in mind while creating index on this database:

1. Index a field which is frequently used for searching.
2. No indexing on tables with little data.

This worked for me. Hope will work for others also.