best way to calculate aggregate products from materialized paths?

14 Jul 2005 4:01 PM

Paul

I need to implement some complicated hierarchy manipulation features. To
make this happen I can no longer maintain expanded structures relationally -
doing it purely in SQL would be a nightmare. I'm going to move the logic
that maintains them into OO; this will force me to do row updates
individually instead of relationally, which makes the nested set model
unsuitable because of its volatility. I'm considering using materialized
paths but noticed that it might make computing aggregate products trickier
and less efficient than in nested set. I'd rather not have to deonromalize
the aggregate product... I thought someone here would be able to suggest a
good (fast) way of calculating it without recursion or cursors. There
probably isn't, is there?

Paul

14 Jul 2005 6:46 PM

Razvan Socol

For aggregate products, see:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/fa3ce455caa8be87

Razvan

14 Jul 2005 7:14 PM

Paul

"Razvan Socol" <rso***@gmail.com> wrote in message
news:1121366806.049164.221420@g43g2000cwa.googlegroups.com...
> For aggregate products, see:
>

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/fa3ce455caa8be87

>
> Razvan

Already familliar with that, works great with nested set. Are you suggesting
that there is an easy way to apply it to the materialized path model or did
I not state my question clearly enough?

Paul

14 Jul 2005 7:31 PM

Steve Kass

Paul,

It depends how you materialize the path, and what you want the
aggregate product for. I'm not aware of any standard aggregate
product one might compute in hierarchies, but assuming you wanted
the product from root to a node, it would be something like

select
CAST(ROUND(EXP(SUM(LOG(ABS(NULLIF(X,0)))))*
(1-SUM(1-SIGN(X))%4)*(1-SUM(1-SQUARE(SIGN(X)))),0) AS INT)
from Hierarchy
where <path to specific node> like Path + '%'

I don't think the issue has to do with aggregate product, but
just whether you can identify the nodes in question easily.

Steve Kass
Drew University

Paul wrote:

Show quote

>I need to implement some complicated hierarchy manipulation features. To
>make this happen I can no longer maintain expanded structures relationally -
>doing it purely in SQL would be a nightmare. I'm going to move the logic
>that maintains them into OO; this will force me to do row updates
>individually instead of relationally, which makes the nested set model
>unsuitable because of its volatility. I'm considering using materialized
>paths but noticed that it might make computing aggregate products trickier
>and less efficient than in nested set. I'd rather not have to deonromalize
>the aggregate product... I thought someone here would be able to suggest a
>good (fast) way of calculating it without recursion or cursors. There
>probably isn't, is there?
>
>Paul
>
>
>
>

14 Jul 2005 8:07 PM

Paul

Man I'm braindead today. Of course that's a very easy way to do it.

But that way (with a varchar (256) path column) is more than 7 times slower
than using nested set to return a typical 1500 row, 7 level result set. I'll
have to see how much I can speed it up with tweaking :(

Paul

Show quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:OplNeqKiFHA.3568@TK2MSFTNGP10.phx.gbl...
> Paul,
>
> It depends how you materialize the path, and what you want the
> aggregate product for. I'm not aware of any standard aggregate
> product one might compute in hierarchies, but assuming you wanted
> the product from root to a node, it would be something like
>
>
> select
> CAST(ROUND(EXP(SUM(LOG(ABS(NULLIF(X,0)))))*
> (1-SUM(1-SIGN(X))%4)*(1-SUM(1-SQUARE(SIGN(X)))),0) AS INT)
> from Hierarchy
> where <path to specific node> like Path + '%'
>
> I don't think the issue has to do with aggregate product, but
> just whether you can identify the nodes in question easily.
>
> Steve Kass
> Drew University