|
Home > Archive > SQL Anywhere Feedback > January 2006 > Optimize predicates using DATE ( timestamp )
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Optimize predicates using DATE ( timestamp )
|
|
| Breck Carter [TeamSybase] 2006-01-19, 11:23 am |
| Timestamp columns are valuable because they can contain accurate
timestamps. However, queries involving date ranges, or single dates,
are also valuable, and they are hard to write so they run efficiently
using timestamp columns.
For example, the predicate "DATE ( timestamp_column ) = CURRENT DATE"
will not use an index on timestamp_column because of the presence of
the DATE() function call.
Rewriting the predicate to this ugly kludge makes the index usable:
timestamp_column BETWEEN CURRENT DATE
AND DATEADD ( MILLISECOND, 86399999, CURRENT DATE )
Using a COMPUTE column is also possible, but wasteful IMO.
Suggestion: Have the engine optimize references to DATE (
timestamp_column ) in a similar fashion to the way it optimizes LIKE
'xxx%'.
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| David Kerber 2006-01-19, 11:23 am |
| In article < t6dvs1589c76317hjegc
el70r96guht6h4@4ax.com>,
NOSPAM__bcarter@risi
ngroad.com says...
> Timestamp columns are valuable because they can contain accurate
> timestamps. However, queries involving date ranges, or single dates,
> are also valuable, and they are hard to write so they run efficiently
> using timestamp columns.
>
> For example, the predicate "DATE ( timestamp_column ) = CURRENT DATE"
> will not use an index on timestamp_column because of the presence of
> the DATE() function call.
>
> Rewriting the predicate to this ugly kludge makes the index usable:
>
> timestamp_column BETWEEN CURRENT DATE
> AND DATEADD ( MILLISECOND, 86399999, CURRENT DATE )
>
> Using a COMPUTE column is also possible, but wasteful IMO.
>
> Suggestion: Have the engine optimize references to DATE (
> timestamp_column ) in a similar fashion to the way it optimizes LIKE
> 'xxx%'.
SECOND!!!! This would be a huge boost for my apps if it can be done!
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Breck Carter [TeamSybase] 2006-01-19, 11:23 am |
| FWIW you can manually add the kludge now, if you have especially slow
queries AND the time :) And making the index CLUSTERED also helps... a
lot.
Breck
On 19 Jan 2006 08:45:33 -0800, David Kerber
< ns_dkerber@ns_Warren
RogersAssociates.com> wrote:
>In article < t6dvs1589c76317hjegc
el70r96guht6h4@4ax.com>,
> NOSPAM__bcarter@risi
ngroad.com says...
>
>
>SECOND!!!! This would be a huge boost for my apps if it can be done!
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
|
|
|
|
|