Database Indexing and Performance

Overview 

Database Indexing can be a crucial tool for getting the most performance out of Decisions. The larger and more complex the database that is being used, the more important it is to have a viable indexing strategy.

How to Know Where an Index is Needed

Decisions provides a few tools that can be used to determine where an index could be added. Generally the tables that will benefit most from indexing are those that are either called the most, or those that take the longest to process their requests.

Under System > Administration > System Tools > Profiler are two Reports with information on database usage by the platform.

Database Calls shows the calls by how many times they were called. By default this is sorted from highest to lowest. It can also be sorted by how much time it took to complete the calls. 

Database Call Source shows similar information to Database Calls, but also provides the location of where the call was made from. In the example below Thread Jobs are running and making database calls. For more information on using the Profiler, read Troubleshooting Database Connection Response Times.

Heaviest DB Queries

Another place to look is the Heaviest DB Queries Report. This is found under System > Administration > System Tools > Event Viewer. If it does not appear on the page, click the More dropdown menu to find the Report.

This Report shows the top 10 individual queries that take the longest to run.

How to Apply an Index

There are two methods of applying an index to a table. Both require the work to be done through database management software. The examples below were written with SQL Server Management Studio in mind.

First there is built in tooling:

1. Login to SSMS. Expand Database > [DB Name] > Tables > [Table Name] > Indexes.

2. Right click the Indexes folder for the table you want to add an index to and select New Index. There are a few options there, but in most cases Non-Clustered Index is the best choice.

3. From there a new window appears that has all the settings for index creation. Do not go anywhere besides the General tab unless you have a specific setting you know you want to find and change. Give the index a descriptive name. One suggestion is TableName_EachColumnIndexed_Index for a descriptive name. Leave the Unique box unchecked unless you know you need it (checking it adds a Unique constraint to all key columns in the index so that no duplicate data can be inserted).

4. Click the Add button while in the Index key columns tab is selected on this window. This will launch a new prompt. Select the fields needed based on the review of the slow query. Once you Add them, make sure they are in the same order as the filter criteria from the query. These can be rearranged by using the Move Up and Move Down buttons on the New Index window.

5. Press OK and you will be able to find the new index under the Indexes folder for the table.

The second method is via SQL query. Run the below query within SSMS:

CREATE NONCLUSTERED INDEX [IndexName] ON [dbo].[TargetTableName]

(

[FilterColumn1] ASC,

[FilterColumn2] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

To run a query, right click on the Indexes folder, select Script Index as > CREATE To > New Query Editor Window.