Posted by jim_geissman on 10/19/06 15:36

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

jim.clifford@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......

[Back to original message]