The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?



asked 29 Apr '10, 10:11

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

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.

(29 Apr '10, 10:31) Volker Barth

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.

permanent link

answered 29 Apr '10, 10:16

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

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.

permanent link

answered 30 Apr '10, 12:23

Reimer%20Pods's gravatar image

Reimer Pods
accept rate: 12%

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.)

permanent link

answered 05 May '10, 22:09

Dan%20Konigsbach's gravatar image

Dan Konigsbach
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 29 Apr '10, 10:11

question was seen: 559 times

last updated: 05 May '10, 22:09