Learning about Indexes the Hard Way

[jim.clifford]

Hello, I am experimenting with indexes and hope people can shed light
on some of my problems.
I am using SLQ 2000 on Win 2000 Server. Using the following query for
discussion;
--------------------------------
SELECT TOP 1000000
E.EUN_Numeric, -- Primary Key
E.EUN_CODE, -- VarChar
E.[timestamp] --,
--E.Model -- Computed column (substring of EUN_CODE)
FROM dbo.Z1_EUNCHK E
--WHERE E.[timestamp]DATEADD ( wk , -48, getdate() ) AND
-- E.[timestamp]< DATEADD ( wk , -4, getdate() )
ORDER BY E.[timestamp] DESC
-----------------------------------

Problem 1) If I set up a single Index on the TimeStamp (plus the PK on
EUN_Numeric) then there is not improvement in performance.
It is only when I set up an Index on the Timestamp,EUN_Numeric,EUN_Code
then I get a good improvement. This is also the
case with the "where" clause added. I am using query analyser. The
improvement is 14 secs to 3 secs (mainly with the removal of the sort
process)

Why?
My expectation is that if my query uses [timestamp] column then surely
an index only on this is adequate.

Problem 2) Introducing the simple computed column into the query takes
the time to 15 secs (with Sort processes involved).

Why does revert back to sorting process when previous the index was
used ?
Regards JC......

[Bill]

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.
ji**********@gm.com wrote:

Hello, I am experimenting with indexes and hope people can shed light
on some of my problems.
I am using SLQ 2000 on Win 2000 Server. Using the following query for
discussion;
--------------------------------
SELECT TOP 1000000
E.EUN_Numeric, -- Primary Key
E.EUN_CODE, -- VarChar
E.[timestamp] --,
--E.Model -- Computed column (substring of EUN_CODE)
FROM dbo.Z1_EUNCHK E
--WHERE E.[timestamp]DATEADD ( wk , -48, getdate() ) AND
-- E.[timestamp]< DATEADD ( wk , -4, getdate() )
ORDER BY E.[timestamp] DESC
-----------------------------------

Problem 1) If I set up a single Index on the TimeStamp (plus the PK on
EUN_Numeric) then there is not improvement in performance.
It is only when I set up an Index on the Timestamp,EUN_Numeric,EUN_Code
then I get a good improvement. This is also the
case with the "where" clause added. I am using query analyser. The
improvement is 14 secs to 3 secs (mainly with the removal of the sort
process)

Why?
My expectation is that if my query uses [timestamp] column then surely
an index only on this is adequate.

Problem 2) Introducing the simple computed column into the query takes
the time to 15 secs (with Sort processes involved).

Why does revert back to sorting process when previous the index was
used ?
Regards JC......

[jim.clifford]

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.......

[jim_geissman]

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

ji**********@gm.com wrote:

Hello, I am experimenting with indexes and hope people can shed light
on some of my problems.
I am using SLQ 2000 on Win 2000 Server. Using the following query for
discussion;
--------------------------------
SELECT TOP 1000000
E.EUN_Numeric, -- Primary Key
E.EUN_CODE, -- VarChar
E.[timestamp] --,
--E.Model -- Computed column (substring of EUN_CODE)
FROM dbo.Z1_EUNCHK E
--WHERE E.[timestamp]DATEADD ( wk , -48, getdate() ) AND
-- E.[timestamp]< DATEADD ( wk , -4, getdate() )
ORDER BY E.[timestamp] DESC
-----------------------------------

Problem 1) If I set up a single Index on the TimeStamp (plus the PK on
EUN_Numeric) then there is not improvement in performance.
It is only when I set up an Index on the Timestamp,EUN_Numeric,EUN_Code
then I get a good improvement. This is also the
case with the "where" clause added. I am using query analyser. The
improvement is 14 secs to 3 secs (mainly with the removal of the sort
process)

Why?
My expectation is that if my query uses [timestamp] column then surely
an index only on this is adequate.

Problem 2) Introducing the simple computed column into the query takes
the time to 15 secs (with Sort processes involved).

Why does revert back to sorting process when previous the index was
used ?
Regards JC......

[Damien]

ji**********@gm.com wrote:

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 ?

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.

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

Regards JC.......

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

[jim_geissman]

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?

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

[Damien]

ji**********@countrywide.com wrote:

Damien Wrote:

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

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?

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