Subject: SQL Anywhere 11 retrieve on timestamp
Date: 28 Apr 2010 13:13:53 -0700
In most table we have a timestamp. I thin the format is yyyymmddhhmmss????
When we do a retrieve on timestamp on a table with a lot of records, the perfomance is not so good. Putting an index on timestamp is probably not a good idea?
What's the way to increase performance if the timestamp column is in the where clause?
asked 29 Apr '10, 10:11
If the column values are more-or-less unique, and you do queries with predicates like this
WHERE col = value
then an index might help.
If the rows are inserted in (approximate) order by column value, and you do "range queries" like this
WHERE col BETWEEN from-value AND to-value
then marking the index as CLUSTERED might help a lot.
The Index Consultant may help you determine what indexes you need for your actual workload.
answered 29 Apr '10, 10:16
We had a case of many request for rows comparing the timestamp with a given date or date range. Performance was significantly improved by using a "compute column" (date column with default value "Date"(MY_TIMESTAMP), never modified) and a clustered index on that column.
answered 30 Apr '10, 12:23
This might not be related to your problem, but just in case...
Be sure that your WHERE condition doesn't put your timestamp column inside of a function. For example, if LastMealTime is indexed,
won't use the index, and will probably be much slower than
(This changes if they've make the date functions "sargable", but last I checked, they weren't.)
answered 05 May '10, 22:09