on 2010 Apr 29 11:11 AM
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
30 | |
8 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.