September 11, 2008 at 12:36 pm
I have posted about these a few times lately since I do not use them that often and this new project has them all over the place.
The table is a heavy use table sometimes with over 10 million records. Deletes, inserts, updates, and selects constantly. It looks like a normal self referencing table
Table
TableId not null
ItemId not null
ParentTableId nullable
Cluster Key is Itemid
seperate indexes on tableid, Parenttableid
There are two FK's one going to table item and one from tableid to parenttableid
I am seeing some problems on the deletes which I am experimenting with now. This has to be hardware independent since it can be installed on the most basic system possible.
We can delete by itemid or tableid. The management wants and says the itemid needs to be the clustered key. I can not find any real documentation saying self referencing tables under heavy use (lots of records) are bad.
I thought we should move away from this type of table to two tables Something like this
Table1
Table1Id not null
ItemId not null
Cluster Key is Itemid
seperate indexe on table1id
table2
table1id not null
parenttable1id not null
cluster key table1id,parenttable1id
why am I seeing these delays on these self referencing tables?
Is option2 a better solution?
Also for basic systems when can I use self referencing tables
September 11, 2008 at 2:36 pm
Self-referencing tables can work just fine.
You might need to add a cascading delete to the FK, or a trigger if the rules are more complex than that for your data.
Splitting it into two tables is more likely to slow it down than to speed it up, because of the join overhead. If a row can have multiple parents, you'll need to split it into two tables (like any many-to-many relationship), but if not, keep it as one table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 11, 2008 at 3:07 pm
I taught we couldn't have on cascade delete on when the reference is on the same table!!!
September 11, 2008 at 3:13 pm
I have to do a join already for the self reference
WITH tree (id)
AS (
SELECT PiecesID
FROM Pieces
WHERE [PiecesID] = @PiecesID
UNION ALL
SELECT PiecesID
FROM Pieces
INNER JOIN tree t
ON t.id = ParentPiecesID)
example being
id pid
1 null
2 1
3 2
It is not a many to many so I can see why you are saying that an extra table is just adding overhead. Can you tell me how to tune the table for the best results? currently for about 40 records I am seeing it take about 7-10 seconds on a local SQL box (beefy laptop). I just think it is to slow. This is going to be a heavily used table.
September 11, 2008 at 3:19 pm
Ninja is right cascade is not an option on self referencing tables
September 12, 2008 at 8:41 am
You're right, can't auto-delete on self-reference. Have to use a trigger for that, and make sure that it deals correctly with nesting.
On the point of joining, yes, you have to join. Any FK implies a join. With a separate table, you have two joins, with a self-referent table, you have have one join. That's why there's extra join overhead for that solution. Probably not that big a deal, but why have any performance hit that you don't actually need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 8:44 am
When you talk about tuning the performance, do you mean insert, update, delete or select, or all of the above?
The fastest way I know to select from such a table is a recursive Common Table Expression. If you look those up in Books Online, it has an example of exactly how to write a query for that. If you have an index on ParentID, this solution can be very fast on resolving hierarchies like this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 10:22 am
Is that not what I pasted in on the post above?
September 12, 2008 at 10:48 am
JKSQL (9/12/2008)
Is that not what I pasted in on the post above?
That's exactly what you posted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply