Re: Hard Design/Performance Issue (long) - Help!
Date: Mon, 20 Nov 2000 17:48:49 -0000
Message-ID: <bodS5.19100$8F2.2441357_at_nnrp4.clara.net>
It's difficult to give an informed opinion on a problem of this type (original posting appended at bottom) without spending a lot of time on it, which is unrealistic in a volunteer capacity. However, at a casual glance, the structure looks OK. Are you using heirarchical queries? ie. using the CONNECT BY clause of the SELECT statement? Perhaps you could post the particular queries (with their explain plans) which are giving the problem. We should be able to troubleshoot those fairly easily, and either solve your performance problems or gain a greater insight into the difficulties you're facing.
Is your regional structure constant? ie. are there always just four levels (client, region, territory, store)?
If so, a simple structure with a table for each level might work. (if not, read on anyway - I address that later)
eg.
client ( client_key, client_attributes .... )
region ( region_key, client_fk, region_attributes .... )
territory ( territory_key, client_fk, region_fk,
territory_attributes ... )
store ( store_key, client_fk, region_fk, territory_fk,
store_attributes )
Then, suppose you wanted to update the campaign attribute for an entire client you would (assuming you've looked up the client_key for the appropriate client):
update client
set campaign = 'Television'
where client_key = :client_key;
update region
set campaign = 'Television'
where client_fk = :client_key;
update territory
set campaign = 'Television'
where client_fk = :client_key;
update store
set campaign = 'Television'
where client_fk = :client_key;
To update the campaign for just a particular territory of a client you would (assuming you've looked up the territory key)
update territory
set campaign = 'Magazine'
where territory_key = :territory_key;
update store
set campaign = 'Television'
where territory_fk = :territory_key;
To view the campaign for a particular store is easy ... just look up the store record.
You probably want to create triggers on each of the tables to automatically cascade attribute updates to the lower levels.
Alternatively, you might choose not to copy the value of the campaign attribute down through all levels: just set it at a particular level, and have it null at the lower levels unless it is being overridden. Then to view the campaign for a particular store, you'd have to do the following:
select store.name,
store.address,
nvl(store.campaign,
nvl(territory.campaign,
nvl(region.campaign,
client.campaign)
)
) campaign
from client,
region,
territory,
store
where territory.territory_key = store.territory_fk and region.region_key = store.region_fk and client.client_key = store.client_fk;
ie. campaign is set to the store's campaign value if it is not null, otherwise it is set to the territory's campaign value if that is not null, otherwise it is set to the client's campaign value). You might want to hide all this in a view.
Although this approach is, in my opinion, more elegant, it has the drawback that the query
select .....
from store_view
where campaign = ......
requires a full tablescan of the store table unless there are other selection criteria.
Postscript: I've just read your reply to David Fitzjarrell. If I understand correctly, some clients will have more than 4 levels. If you can set an upper-limit on the number of levels (10 say), then you can just generalise the above approach: create tables for each level. You may need to give them rather blander names (level1, level2, level3 ... instead of region, territory etc.), but keep the top (client) and bottom (store) levels. Each level has foreign keys for each of the levels above it. When populating the structure you'll have to insert dummy rows for each of the missing levels. eg. for Well-Mart, level1 will hold the region, level2 will hold the territory. For each territory create a dummy level3 row with foreign keys pointing to the client, region, and territory rows. Then for each dummy level3 row create a dummy level4 row with foreign keys to the level3 and higher rows. And so on until you reach the store rows, where you create as many as you need with foreign keys pointing back to the dummy level9 and higher rows..
If you cascade-update attribute changes, the principle is the same, you'll just cascade the attribute changes through the dummy rows. (Effectively the dummy levels will always hold the same values as the last defined level for this organisation). If you instead use the null-value select method, the only difference is that the nested nvl condition will be longer and you'll be joining 10 tables together. The null values in the intermediate dummy rows won't affect the result of the query.
Performance-wise, neither of these is too horrendous. For the cascade updates it just means that you'll (worst-case) always update 10 rows, one at each level, when you update the client row, even if that organisation only has four real levels. For the nested nvl select it means that you'll always join to 10 tables, so you'll always retrieve 10 rows to perform a query on a store, instead of the four, or whatever, you've got now. Either way, it sounds that this might be an improvement on the problems you've got with your current (rather complicated?) SQL.
Dave.
-- Remove "nospam" from my address to reply by email Neef wrote in message <8v3udl$v0h$1_at_nnrp1.deja.com>...Received on Mon Nov 20 2000 - 18:48:49 CET
>We have a database that stores organization structure trees for many
>clients. For each client, the stored tree has a maximum depth, and each
>"level" of the tree has it's own name. Each client's tree contains a
>number of "nodes" that are the members of the organization (it's
>confusing to us, also!). We modeled this as a recursive structure.
>
>Tables look like:
>
>Client(Client_Key, Client_Name)
>
>Organization_Level(Organization_Level_Key, Client_FK, Org_Level_Name,
>Org_Level_Depth)
>
>Organization_Member(Org_Member_Key, Organization_Level_Key,
>Org_Member_Name, Parent_Org_Member_Key)
>
>An example may clarify:
>
>Say Well-Mart has two national regions: East and West. The East region
>has two territories: North and South. The South territory has three
>stores: Parkway Well-Mart, Midtown Well-Mart, and Suburban Well-Mart.
>Therefore we have:
>
>1 Client record
>(1, "Well-Mart")
>
>4 Organization_Level records
>(1, 1, "MASTER", 0) <-- every client has this record
>(2, 1, "region", 1)
>(3, 1, "territory", 2)
>(4, 1, "store", 3)
>
>8 Organization_Member records
>(1, 1, NULL, NULL)
>
>(2, 2, "East", 1)
>(3, 2, "West", 1)
>
>(4, 3, "North", 2)
>(5, 3, "South", 2)
>
>(6, 4, "Parkway Well-Mart", 5)
>(7, 4, "Midtown Well-Mart", 5)
>(8, 4, "Suburban Well-Mart", 5)
>
>Whew. Congrats if you're still with me. What we want to be able to do
>is specify parameters for a member (e.g. a Magazine campaign for the
>East region) and have those parameters applied to all descendents of
>that member (all three stores). Parameters lower in the tree "hide"
>those higher in the tree.
>
>This means we could assign a Television campaign to Well-Mart's MASTER
>member , and a Magazine campaign to the South region.
>
>Finally...the point. We are having a TERRIBLE time getting reasonable
>select performance from this structure (there are about 400,000 nodes
>total in all client trees).
>
>1)Are we using the right structure?
>
>2) What could we do to improve select performance? (typical query:
>"what campaign should Well-Mart Parkway use?")
>
>Any thoughts, critiques, or discussion would be helpful.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.