Blog Post

Indexes and their Effect on Query Performance


Indexes play a huge role in the performance of a query. Without indexes, you very simple query to get the last 100 records from a table could take forever to return. Well, maybe not forever, but you get the point. For example, I have a query and its results of the Execution Plan shown in figure 1. As you can see, it took 1 minute and 36 seconds to complete. Not very acceptable in my eyes! In this quick and dirty blog, I just want to show you an example of what results one might see by creating a simple index.


Figure 1: Execution Plan Results before Indexing

No Index 


After creating an index on OrderDate_SK for this table and re-executing the same exact query to return my last 100 records, the results were returned in 1 second as shown in figure 2 below.

Figure 2: Execution Plan Results after Indexing

After Indexed

As you can see, this little index will play a huge role when joining or filtering on the order date column in the future. It still isn't doing an index seek, but that is better left for another blog.

Until next time, “keep your ear to the grindstone” – Good Will Hunting



Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating