|
From: Ontsnapt Newsgroups: sybase.public.sqlanywhere.general 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? Thanks Eric |
|
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. |
|
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. |
|
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.) |
Just to clarify: A TIMESTAMP (and the synonym DATETIME) is stored internally as 8 bytes, not as a string. The time resolution is up to microseconds, namely yyyymmddhhmmss:ssssss.