c# - SQL query by time too slow -

i have database of 700,000 entries fast-text search table. each row has time of day associated it. need page records 100 rows @ time efficiently. doing tracking end of day.

it taking long execute (15 seconds)

here's example query:

select *  objects o, ftsobjects f f.rowid = o.autoincid ,    o.timestamp > '2012-07-11 14:24:16.582' ,    o.timestamp <= '2012-07-12 04:00:00.000' ,    o.name='gpshistory'  order o.timestamp  limit 100 

the timestamp field indexed.

i think because order by statement sorting records returned, doing limit not sure.

suggestions?

the best way dba @ plan generated , make sure it's optimal plan (e.g. make sure there no table scans in plan, can happen if optimizer uses bad statistics)

here's things may help:

• add index on objects.name - possibly compound index on name , timestamp.
• add index on rowid in ftsobjects it doesn't exist
• update statistics on timestamp index periodically (ideally after large updates or daily if updates continuous)
• rebuild clustered index (if have one). if clustered index on field not sequential inserts (e.g. char field inserts in random places)
• don't select * if don't need - increases i/o time

you might try casting strings datetime, although think sql implicitly versus casting data string (which not use index on datetime)

select *  objects o, ftsobjects f f.rowid = o.autoincid ,    o.timestamp > convert(datetime,'2012-07-11 14:24:16.582') ,    o.timestamp <= convert(datetime,'2012-07-12 04:00:00.000') ,    o.name='gpshistory'  order o.timestamp  limit 100