|
|
Posted by jim.clifford on 10/18/06 10:09
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......
Navigation:
[Reply to this message]
|