Page 82 - DLIS405_INFORMATION_STORAGE_AND_RETRIEVAL

Unit 8: Indexing

application will need to go through to meet the business rules you have defined. Your programs will Notes
need to read data a particular way to build reports. Alternatively, you will have an online screen that
will allow users to enter some search criteria, so different screens can be displayed. Play attention to
these different data access requirements of the application. If a report needs data to come back in
sorted order based on a column value then this column would be a good candidate for being in an
index. If customers need to enter an ID and date range to return some customer records on a screen,
then the ID and date column associated with the date range would be additional candidate columns
for indexes.
In the design phase, you need to get a sense for which columns are being used, and what order
those columns will be returned to the application. You can then use this information to design some
best guesses at what indexes your application will need. By doing this data analysis you will have
the information you need to start identifying some indexes that will most likely be useful for your
application.

Development Phase

In the development phase, you will review how well those best guess indexes are meeting the needs
of your application. Just like any other development phase, this is the phase where you will be tweaking
those indexes in your design when you realize they are not meeting your application data access
requirements.

As you find your code is performing poorly you will add more indexes. Keep in
mind indexes don’t come for free. The more indexes you have the more costly an
INSERT, UPDATE and DELETE statement will be if it has to update a bunch of
indexes.

Therefore, create indexes for those queries in your application that are going to be run frequently. If
you also have monthly or yearly queries that take a long time, possibly you can do without an
index. Possibly you can create these indexes once a month, or year for these monthly/yearly
processes. These are the kinds of decisions you need to make when you are developing indexes.
You need to have well balanced indexes so your application performs acceptable most of the time
for the frequently run queries. You need to make sure you do not have too many indexes that cause
the INSERT, UPDATE, and DELETE statements to take a long time.
Once you are done with developing your application code, but prior to moving into user acceptance
testing, you need to go back and review the indexes you have. Make sure all the indexes you created
during your development phase are the ones that really made your queries run faster and they are
not ones you created that provided little value. If you removed those useless indexes as you went
along then you probably can omit this step. Another thing to do is verify that you don’t have any
duplicate indexes. If you have duplicate indexes you are just wasting resources, both disk space
and the cost of maintaining them over time. Here is a script, written by Paul Neilson, that can be
used to identify those duplicates:
— From Paul Neilson: http://sqlblog.com/blogs/paul_nielsen/archive/2008/
06/25/find-duplicate-indexes.aspx
— exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]

LOVELY PROFESSIONAL UNIVERSITY 77