sql server (alternate): Learning about Indexes the Hard Way -- sql server (alternate)

Re: Learning about Indexes the Hard Way

Bill
10/18/2006 6:55:31 AM

Jim,

In query analyzer there is a handy dandy visual tool - "show execution
plan"

If you have not already, you should select this option and run your
query both ways to review the plan. By hovering over the icons in the
result, you can see if the index is used.

You can also investigate the use of SHOWPLAN which can give you a text
result.

I believe also that you may be experiencing some caching effect where
the second time you run a query it will run faster because the plan has
been cached from the first run.

[quoted text, click to view]

Learning about Indexes

jim.clifford NO[at]SPAM gm.com
10/19/2006 2:33:45 AM

I have been using display execution plan (of query analyser). This is
how I known if the task performed in 'top', 'sort', 'seek' and 'index
scan'. These tasks I correlate with durations taken.

So the questions still remain;
Problem 1) Why is the smaller index only involving 'timestamp' not used
when it's available and covers the details on the query involved ?

Problem 2) Why does the introduction of a simple computed column
dis-able the optimiser?

Regards JC.......

Re: Learning about Indexes the Hard Way

jim_geissman NO[at]SPAM countrywide.com
10/19/2006 8:36:23 AM

The variation in time may result from how the data is physically
arrranged on disk, plus the effect of a covering index.

The index {timestamp, eun_numeric, eun_code} is arranged by timestamp,
so it's very quick to search on that basis, plus it contains all the
information needed for the query -- so when it is used, the table
itself isn't even examined.

Using only the index on {timestamp} requires that the table itself be
accessed in order to get the values of eun_numeric and eun_code.
Depending on how the PK eun_numeric is ordered, the query might end up
jumping around all over the disk to locate the data.

Jim G

[quoted text, click to view]

Re: Learning about Indexes

Damien
10/19/2006 9:03:09 AM

[quoted text, click to view]

But it doesn't cover the details required (using the word cover as in
the phrase covering index), because it doesn't include all columns
which are included in the select list.

So becuase the optimiser know's it's going to have to go into the
clustered index anyway (especially with no where clause), it just uses
it instead.

[quoted text, click to view]

I don't believe the optimiser can break down a computed column to know
which underlying columns it is based upon. So it doesn't know that the
computed column is "covered" by a particular index. Of course, you can
index computed columns, and it might then use that index, if it was a
covering index.

Damien

Re: Learning about Indexes

jim_geissman NO[at]SPAM countrywide.com
10/20/2006 3:02:39 AM

How can this be true? In this case, the computed column is simply
a substring of one of the other columns that is already in the query,
and is also included in the 3-item index. Is this really too hard for
the
optimizer?

[quoted text, click to view]

Re: Learning about Indexes

Damien
10/24/2006 12:29:16 AM

[quoted text, click to view]
In the *very specialised* situation where the computed column is the
leftmost n characters, then the index would be usable. But why should
the good people writing the optimizer spend time on that particular
condition? In all other substring situations (where you do not start at
the leftmost character), and for any more complex calculated columns
(remember, they can reference any of the columns), the index would be
useless.

And you always have the option, if you need an index to be considered,
of indexing the computed column.

Damien

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.