cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieve on timestamp

Breck_Carter
Participant
1,707

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Breck_Carter
Participant

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.

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
Participant

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.