StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Use a closure table. If your basic structure is a parent-child with the fields ID and ParentID, then the structure for a closure table is ID and DescendantID. In other words, a closure table is an ancestor-descendant table, where each possible ancestor is associated with all descendants. You may include a LevelsBetween field if you need. Closure table implementations usually include self-referencing records, i.e. ID 1 is an ancestor of descendant ID 1 with LevelsBetween of zero.</p> <p>Example: Parent/Child<br> ParentID - ID<br> 1 - 2<br> 1 - 3<br> 3 - 4<br> 3 - 5<br> 4 - 6<br></p> <p>Ancestor/Descendant<br> ID - DescendantID - LevelsBetween<br> 1 - 1 - 0<br> 1 - 2 - 1<br> 1 - 3 - 1<br> 1 - 4 - 2<br> 1 - 6 - 3<br> 2 - 2 - 0<br> 3 - 3 - 0<br> 3 - 4 - 1<br> 3 - 5 - 1<br> 3 - 6 - 2<br> 4 - 4 - 0<br> 4 - 6 - 1<br> 5 - 5 - 0<br></p> <p>The table is intended to eliminate recursive joins. You push the load of the recursive join into an ETL cycle that you do when you load the data once a day. That shifts it away from the query.</p> <p>Also, it allows variable-level hierarchies. You won't be stuck at 4.</p> <p>Finally, it allows you to slot products in non-leaf nodes. A lot of catalogs create "Miscellaneous" buckets at higher levels of the hierarchy to create a leaf-node to attach products to. You don't need to do that since intermediate nodes are included in the closure.</p> <p>As far as indexing goes, I would do a clustered index on ID/DescendantID.</p> <p>Now for your query performance. This takes a chunk out but not all. You mentioned a "Top 10". This implies ranking over a set of facts that you haven't mentioned. We need details to help tune those. Plus, this gets only gets the leaf-level sections, not the products. At the very least, you should have an index on your CatalogueProduct that orders by SectionID/ProductID. I would force Section to Product joins to be loop joins based on the cardinality you provided. A report on a catalog section would go to the closure table to get descendants (using a clustered index seek). That list of descendants would then be used to get products from CatalogueProduct using the index by looped index seeks. Then, with those products, you would get the facts necessary to do the ranking.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload