SQL Server request is very slow when nothing to retreive ?!

Question

Hi all,

We are facing a strange performance problem with "SQL Server Express 2005" in a very simple condition.

We have a table with: [timestamp], [id], [value] columns. and only one primary unique index on [timestamp]+[id].

The table contains around 68.000.000 records.

The request is: SELECT TOP 1 timestamp FROM table WHERE id=1234 ORDER BY timestamp

If there is at least one record for this id the result is given in few miliseconds. If there is no record for this id the result is given in at least 30 SECONDS!!!

We tried many other simple similar request, and as soon as we have no corresponding records for the id the processing time is awfully long.

Do you have any explanation and idea to avoid this?

Thanks.

Answer 1

TOP 1 ORDER BY what?

If it finds one record, it must scan the entire table to find more, since you don't have an index on id.

If you did, but wanted "ORDER BY timestamp", it would still table scan because it doesn't know the id is unique in the timestamp index (even though it might make sense to you because the id is declared unique, say - is it? How if it's not a unique index of its own or as the 1st field in a multicolumn index? - or they both increase monotonically, say - do they?)

Answer 2

If the ID is a unique ID then your ORDER BY isn't needed - and an index on just that field would be enough.