none
Index scan instead of seek

    Question

  • I have a compact 3.5 SP1 database.
    My query is

    SELECT TOP(1) SysDate FROM Violator ORDER BY SysDate Desc

    The SysDate column is a datetime with unique values.
    I have an index on the Sysdate column which I created using SSMS.
    When I run the query the execution plan shows it is using an index scan instead of a seek.
    There are 191,000 rows in the table.
    It is using a scan because there are too few rows to use a seek?

    This query takes < 1 sec in SSMS but it takes 35 sec in a mobile device emulator which is what I am trying to fix with the index.

    Thursday, September 10, 2009 3:35 PM

Answers

  • Could you try adding a WHERE clause  - WHERE SysDate < GETDATE()  (or something)....
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    • Marked as answer by forwheeler4 Thursday, September 10, 2009 4:32 PM
    Thursday, September 10, 2009 3:45 PM

All replies

  • And the index is on SysDate DESC ?? (Not SysDate ASC...)
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Thursday, September 10, 2009 3:40 PM
  • Yes it is.
    Thursday, September 10, 2009 3:41 PM
  • Could you try adding a WHERE clause  - WHERE SysDate < GETDATE()  (or something)....
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    • Marked as answer by forwheeler4 Thursday, September 10, 2009 4:32 PM
    Thursday, September 10, 2009 3:45 PM
  • Yep, that works great in SSIS and in the mobile device.
    It seems silly to use a bogus where clause just to get the seek to work but I guess the order by statement doesn't cause the query to use the index.
    Thursday, September 10, 2009 4:23 PM