Retrieve on timestamp

[Breck_Carter]

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

[VolkerBarth]

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.

[Breck_Carter]

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.

[former_SQLA_member1694911]

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,

WHERE DATE(LastMealTime) = TODAY(*)

won't use the index, and will probably be much slower than

WHERE LastMealTime >= TODAY(*) AND LastMealTime < TODAY(*)+1

(This changes if they've make the date functions "sargable", but last I checked, they weren't.)

[reimer_pods]

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.