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

asked 29 Apr '10, 10:11

Breck%20Carter's gravatar image

Breck Carter
26.9k437609883
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
26.9k437609883
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
4.2k344583
accept rate: 11%

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
455101220
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×246

question asked: 29 Apr '10, 10:11

question was seen: 647 times

last updated: 05 May '10, 22:09