SQL Anywhere database - Window function question

Author

Window function question - performance

David Kerber

2005-04-12, 9:23 am

If somebody is pretty familiar with OLAP functions, I'd appreciate some
help with this:

I'm working with ASA 9.0.2's WINDOW (OLAP) function, and am having no
problem getting it to return what I need, but am wondering if there's a
way of speeding it up a bit.

Below are two slightly different versions of the query, both of which
work and take essentially the same time (about 35 seconds to return 235k
rows), and the execution plans are identical. What I'm doing is simply
finding the elapsed time in seconds between one row and the next when
they are ORDERed by ObservationTime.

My question is if there was any way of doing this without having to use
an aggregate query (the MIN(ObservationTime)
in this case) when there is
only one record to choose from? It doesn't like the FIRST clause.

SELECT ObservationTime,
min(ObservationTime)
over (ORDER BY ObservationTime rows BETWEEN 1
preceding AND 1 preceding) AS LastTime,
datediff(second, LastTime, ObservationTime) AS ObsDiff
FROM rt_messagelog ml WHERE observationtime > '2005-03-01' ORDER BY
observationtime

SELECT ObservationTime,
min(ObservationTime)
over (ORDER BY ObservationTime rows 1 preceding) AS
LastTime,
datediff(second, LastTime, ObservationTime) AS ObsDiff
FROM rt_messagelog ml WHERE observationtime > '2005-03-01' ORDER BY
observationtime

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Matthew Young-Lai $iAnywhere Solutions$

2005-04-12, 11:24 am

There's no way to avoid the aggregate if you use the olap features. You
could try this: drop the window function, fetch rows one at a time, and
subtract the last from the current in procedure logic. That might be a bit
faster.

Matthew

"David Kerber" < ns_dkerber@ns_wraenv
iro.com> wrote in message
news:MPG. 1cc5a29f99966cb49897
35@forums.sybase.com...
> If somebody is pretty familiar with OLAP functions, I'd appreciate some
> help with this:
>
> I'm working with ASA 9.0.2's WINDOW (OLAP) function, and am having no
> problem getting it to return what I need, but am wondering if there's a
> way of speeding it up a bit.
>
> Below are two slightly different versions of the query, both of which
> work and take essentially the same time (about 35 seconds to return 235k
> rows), and the execution plans are identical. What I'm doing is simply
> finding the elapsed time in seconds between one row and the next when
> they are ORDERed by ObservationTime.
>
> My question is if there was any way of doing this without having to use
> an aggregate query (the MIN(ObservationTime)
in this case) when there is
> only one record to choose from? It doesn't like the FIRST clause.
>
>
>
> SELECT ObservationTime,
> min(ObservationTime)
over (ORDER BY ObservationTime rows BETWEEN 1
> preceding AND 1 preceding) AS LastTime,
> datediff(second, LastTime, ObservationTime) AS ObsDiff
> FROM rt_messagelog ml WHERE observationtime > '2005-03-01' ORDER BY
> observationtime
>
> SELECT ObservationTime,
> min(ObservationTime)
over (ORDER BY ObservationTime rows 1 preceding) AS
> LastTime,
> datediff(second, LastTime, ObservationTime) AS ObsDiff
> FROM rt_messagelog ml WHERE observationtime > '2005-03-01' ORDER BY
> observationtime
>
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).

David Kerber

2005-04-12, 1:23 pm

In article <425bf246$1@forums-1-dub>, "Matthew Young-Lai $iAnywhere
Solutions$" <myoungla@> says...
> There's no way to avoid the aggregate if you use the olap features. You
> could try this: drop the window function, fetch rows one at a time, and
> subtract the last from the current in procedure logic. That might be a bit
> faster.

That's the way I thought it would work out, but just wanted to be sure I
wasn't missing something. Thanks for the resonse!

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Sponsored Links