When Is The Right Time To Create Indexes

This case was raised to me by my colleagues in the company. I thought it would be nice to write this on my weblog. This isnā€™t a technical article but rather a reflection on when I think is the right time to create indexes based from the experiences I have been through every time I design databases and use them. I am not sure if this is a good practice but it has worked for me ever since I got hooked with SQL Server since version 6.5.

So when do I start creating indexes? Read on ā€¦

When I layout my DB objects, I seldom define indexes right away. But of course I make notes as to where and what kind of indexes I’ll be creating later on. I let the database drag on for quite sometime during the development stage without my custom indexes. I let myself write queries without the benefit of indexes. I also allow for sometime that the database gets populated with enough data. Most often, I start creating indexes when the system is almost feature complete and most of the complex queries were written and run against some amount of data. We usually call this phase the load testing phase.

Why do I do this? Here are my two major reasons:

  • Indexes can improve your query performance drastically. During the query writing phase, having indexes can give you an impression that your scripts are blazingly fast. This can be worsen by the fact that typically, your database contains just a token amount of data.
  • Since we all create optimal indexes based on the kind of queries sql server will often encounter and process, creating indexes too early could potentially give you skewed performance impression.

Both reasons can potentially affect your capability to detect query performance problems.

By having the indexes created at a later stage of the development phase, you subject your database to work harder without the benefits of indexes. Performance problems will begin to appear if you have crappy queries. Still, at this point, resist creating an index. Exhaust your means to optimize the query without the benefit of indexes. If you want to create indexes at this point for the sake of testing, just be sure to delete them after the testing.

Doing this will give you a certain level of assurance that you are writing optimized queries without the benefit of indexes. You will even appreciate this practice when there are too many in your team writing so many queries. Once you have the indexes, your queries will be lightning fast.

So how about you? When do you create your indexes?

**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com

Advertisements

Visual Studio 2005 Team Edition for Database Professionals

At last, something we all DB enthusiasts are waiting from Microsoft.

Visual Studio 2005 Team Edition for Database Professionals
http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx

Now we can do the following all in one tool:

  • Automated Data Generation
  • Automated Testing
  • Refactoring (Wow!)
  • Schema and Data Compare

Hope we can have a free Express edition of this wonderful stuff šŸ™‚

Thesaurus Based Searching in SQL Server 2005

It’s been a while since I have posted something here. I was able to got some time to check out again some features of SQL Server which I have been eager to explore. I have been waiting for this eversince I experimented on it in version 2000.

Ever heard of thesaurus-based searching in SQL Server? This feature has been present since version 2000. However, it was an undocumented feature which I learned from Hilary Cotter. Google this guy and he has a lot of things to say about SQL Server. But he always remind me everytime I do fulltext stuff with SQL Server. He is the go to guy when it comes to fulltext searching. I hope he still uses the bibliographic data I gave him few years ago.

So what is this feature? For example, if you want to search the word “color“, records having the words synonymous to “color” such as “hue” and “tint” can be returned as well. The feature also allows you to expand your search keys. For example, you are searching for “100“, it can return records containing the words “100“, “one hundred” or “isang daan” (100 in Filipino).

How can this be done?

First check out the files in \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData. The folder contains XML-based thesaurus definition files. SQL Server uses one of these files depending on the language setting you have. For English, check out tsENU.xml. In the file, you may add the following entries:

<replacement>
<pat>color</pat>
<sub>hue</sub>
</replacement>

<replacement>
<pat>hue</pat>
<sub>color</sub>
</replacement>

<expansion>
<sub>100</sub>
<sub>one hundred</sub>
<sub>isang daan</sub>
</expansion>

Then, enable your database for fulltext. I’d leave this for you to explore. You just need a few mouse clicks to enable your DB to fulltext searching.

The following queries will somehow give you an idea on how to use this feature. You may try and modify the scripts for you to use in your applications.

SELECT title FROM book
WHERE CONTAINS(title, ‘ FORMSOF (THESAURUS, “hue”) ‘)

This query will return records having the words “color” and “hue”.

SELECT title FROM book
WHERE CONTAINS(title, ‘ FORMSOF (THESAURUS, “one hundred”) ‘)

This query will return records having the word “100”, “one hundred”, “isang daan”.

SELECT title FROM book
WHERE CONTAINS(title, ‘ FORMSOF (THESAURUS, “isang daan”) ‘)

This query will return records having the word “100”, “one hundred”, “isang daan”.

However, SQL Server search is limited to what is defined in your XML-based thesaurus definition files. As far as I know, Microsoft has not provided a fully functional thesaurus definition file. MS left us only with templates (Don’t forget to remove the comments in the files). It is up for us to define words we want included in searching. Yeah Yeah … quite a lot of linguistic work for us techies. Let us hope MS be kind enough to include a fully functional thesaurus definition file next time.

This is all folks. I hope this is enough to give you an idea and start implementing the feature in the apps that you develop.

**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com