Posted by JayCallas on 10/02/53 11:43

I have a SQL 2000 table containing 2 million rows of Trade data. Here
are some of the columns:

[TradeId] INT IDENTITY(1,1) -- PK, non-clustered
[LoadDate] DATETIME -- clustered index
[TradeDate] DATETIME -- non-clustered index
[Symbol] VARCHAR(10)
[Account] VARCHAR(10)
[Position] INT
etc..

I have a view which performs a join against a security master table (to
gather more security data). The purpose of the view is to return all
the rows where [TradeDate] is within the last trading days.

The query against the view takes over around 30 minutes. When I view
the query plan, it is not using the index on the [TradeDate] column but
is instead using the clustered index on the [LoadDate] column... The
odd thing is, the [LoadDate] column is not used anywhere in the view!

For testing purposes, I decided to do a straight SELECT against the
table (minus the joins) and that one ALSO uses the clustered index scan
against a column not referenced anywhere in the query.

There is a reason why I have not posted my WHERE clause until now. The
reason is that I am doing what I think is a very inefficient clause:

WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())

The function calculates the proper trade date based on the specified
date (in this case, the current date). It is my understanding that the
function will be called for all rows. (Which COULD explain the
performance issue...)

However, this view has been around for ages and never before caused any
sort of problems. The issue actually started the day after I had to
recreate the table. (I had to recreate the table because some columns
where added and others where renamed.)

On a side note, if I replace the WHERE clause with a hard-coded date
(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine but
STILL uses the clustered index on the [LoadDate] column.

[Back to original message]