Usually when developers are building applications, one or more databases are created to store data for the application. In many development processes, the focus is on adding new features with the mantra “Performance will work itself out.” An unfortunate result is that there are many databases that get developed and deployed without indexes being built because of the belief that they aren’t needed.
Along with this, there are developers who believe their databases are somehow unique from other databases. The following are some reasons that are heard from time to time:
- “It’s a small database that won’t get much data.”
- “It’s just a proof of concept and won’t be around for long.”
- “It’s not an important application, so performance isn’t important.”
- “The whole database already fits into memory; indexes will just make it require more memory.”
- “I am going to use this database only for inserting data; I will never look at the results.”
Each of these reasons is easy to break down. In today’s world of big data, even databases that are expected to be small can start growing quickly as they are adopted. Besides that, small in terms of a database is definitely in the eye of the beholder. Any proof-of-concept or unimportant database and application wouldn’t have been created if there weren’t a need or someone wasn’t interested in expending resources for the features. Those same people likely expect that the features they asked for will perform as expected. Lastly, fitting a database into memory doesn’t mean it will be fast. As was discussed in previous chapters, indexes provide an alternative access path to data, with the aim of decreasing the number of pages required to access the data. Without these alternative routes, data access will likely require reading every page of a table.
These reasons may not be the ones you hear concerning your databases, but they will likely be similar. The general idea surrounding this myth is that indexes don’t help the database perform better. One of the strongest ways to break apart this excuse is by demonstrating the benefits of indexing against a given scenario.
To demonstrate, let’s look at the code in Listing 1. This code sample creates the table MythOne. Next, you will find a query similar to one in almost any application. In the output from the query, in Listing 2, the query generated 1,496 reads.
Listing 1. Table with No Index
SELECT * INTO MythOne FROM Sales.SalesOrderDetail; GO SET STATISTICS IO ON SET NOCOUNT ON GO SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal FROM MythOne WHERE CarrierTrackingNumber =&nbsp;'4911-403C-98'; GO SET STATISTICS IO OFF GO
Listing 2. I/O Statistics for Table with No Index
Table 'MythOne'. Scan count 1, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
It could be argued that 1,496 isn’t a lot of input/output (I/O). This might be true given the size of some databases and the amount of data in today’s world. But the I/O of a query shouldn’t be compared to the performance of the rest of the world; it needs to be compared to its potential I/O, the needs of the application, and the platform on which it is deployed.
Improving the query from the previous demonstration can be as simple as adding an index on the table on the CarrierTrackingNumber column. To see the effect of adding an index to MythOne, execute the code in Listing 3. With the index created, the reads for the query were reduced from 1,496 to 15 reads, shown in Listing 4. With just a single index, the I/O for the query was reduced by nearly two orders of magnitude. Suffice it to say, an index in this situation provides a significant amount of value.
Listing 3. Adding an Index to MythOne
CREATE INDEX IX_CarrierTrackingNumber ON MythOne (CarrierTrackingNumber) GO SET STATISTICS IO ON SET NOCOUNT ON GO SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal FROM MythOne WHERE CarrierTrackingNumber =&nbsp;'4911-403C-98'; GO SET STATISTICS IO OFF GO
Listing 4. I/O Statistics for Table with an Index
Table 'MythOne'. Scan count 1, logical reads 15 physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I’ve shown in these examples that indexes do provide a benefit. If you encounter a situation where there is angst for building indexes on a database, try to break down the real reason for the pushback and provide an example similar to the one presented in this section.
For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)
Alternatively click through the link if you found this article interesting. (This will help the companies Search engine rankings)
Leave a Reply