Hi,

I believe some experienced db designers out there must have run into this
situation at least once. I hope to hear your comments or advice on this
topic. Basically I have a calculation-intensive application that also needs
lots of information to do the calculations. So I'm pressed to deal with both
time performance and space issue. The relationship with among the tables
are a bit complicated. I'm listing the tables below hope to capture the core
issue:

Table:Source( PK:sourceId, LotsOfSourceInfo)

Tableestination( PK:destinationId, LotsOfDestinationInfo,
FK:destinationParentId )

Table:SourceToDestinationDirectly( PK:sourceId, PK:destinationId )

My calculations involved: given a sourceId, resolve the hierarchy in the
Destination table so that I need to have the all Destinations that it's
associated directly and indirectly (ancestors of the direct ones). The 3
tables are updated frequently and the update cannot be wrapped within a sql
native transaction so I have a trigger to keep increasing a version # so my
code can detect new changes to load up new version from db when there is not
change to the tables through out the loading period. When it's fully loaded
and the version doesn't get changed, then it knows that it has a good
snapshot of a version. Since I load all of them in advance into memory and
let code (such as C#) figure out the relationship, it occupies too much
memory, it can cause out-of-memory situation occasionally, especially if I
have load the new version in the background thread to make sure I don't
replace the working version prematurely. I have multiple web servers that
run parallel to split the calculation tasks to speed up things

Here are the approaches that I have considered:

1) Indexed View: View can only work with the direct ones, it doesn't have a
way for me to travel up the hierarchy to pull the out indirect ones.
2) If I leave it on db the way it is and pull up into the memory only the
ones I really need, then it would be too slow, and it also have the version
issue as it can pull up

The only thing left that I can think of is to calculate them upfront then
store it in the db in a format and structure that it's fast to pull up. This
should make the db bigger in physical size. I heard something about OLAP
that might solve this situation but I'm not really sure. Any advice or idea
how to implement this and anything that I should watch out for? Thank you
very much.

Assuming that you're using SQL Server 2005, can you create a recursive
CTE to get all ascendants of a Direct Destination, then add an indexed
view on top of it?

Unfortunately, I don't have appropriate permissions to test the indexed
view; but here's a simple CTE for the Adventure Works DW Employee table,
where LeafKey could then be indexed and joined to an associative table:

LastName) as
(
select EmployeeKey as LeafKey, EmployeeKey, ParentEmployeeKey,
FirstName, LastName
from dbo.DimEmployee as e

Union All

select a.LeafKey, e.EmployeeKey, e.ParentEmployeeKey, e.FirstName,
e.LastName
from dbo.DimEmployee as e
join Ascendants as a
on a.ParentEmployeeKey = e.EmployeeKey
)

select *
from Ascendants

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***