Posted by Bill on 10/18/06 13:55

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.


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]