Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Does the version of SQLite you're developing on support Foreign Keys? If so, your closure table design should have a FK referencing the hierarchy table you're supporting with the closure table. In TSQL: </p> <pre><code>constraint fk_a FOREIGN KEY (ancestor) REFERENCES &lt;hierarchy_tablename&gt; (nodeid) constraint fk_d FOREIGN KEY (descendant) REFERENCES &lt;hierarchy_tablename&gt; (nodeid) </code></pre> <p>You'll have to look up the relevant SQLite syntax, sorry.</p> <p>Since you are already maintaining a depth field, which is the distance between the descendant and its ancestor, you could make use of it to tell if a given node has children. </p> <pre><code>select top 1 'EXPANDABLE' as whatever from closure C where exists (select ancestor from closure where depth &gt; 0 and ancestor = C.ancestor) and ancestor = @Node </code></pre> <p>That should come back fairly quick regardless of the size of your closure table. If you get an empty set from that, then your given node cannot be expanded any more, because it has no children. Exists returns true as soon as it finds one instance that meets your criteria, and you're only taking the top 1 so you don't return a row for every row in your closure table for the passed @Node. </p> <p>As for improving the performance of finding the roots, try something like the below. It's what I use for finding roots, but my closure table is only ~200,000 rows. I compared the plans generated for each though, and your code uses a Hash, which could be impacting performance due to processor requirements on the device (I'm assuming here that SQLite is for iPhone/iPad or sometype of small distribution on devices). The below uses less processing power and more reads from indexes in its plan and makes use of the relationship of the hierarchy to the closure table. I cannot be certain that it will improve your performance woes but it's worth a shot. </p> <pre><code>select a.node_name, a.node_id from test.hier a left outer join (select coo.descendant /* coo = CHILD OF OTHER */ from test.closure_tree coo right outer join test.closure_tree ro on coo.ancestor &lt;&gt; ro.descendant /* ignore its self reference */ and coo.descendant = ro.descendant /* belongs to another node besides itself */)lo on a.node_id = lo.descendant where lo.descendant is null </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POClosure table Root Nodes Query performance with 10s of millions of nodes
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDarren W Henderson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POClosure table Root Nodes Query performance with 10s of millions of nodes
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COForeign keys does seem like a good idea and I actually have a flat table with a list of all nodes that exist for other types of lookup so I could foreign key off of that and use that table for deletion. I like your suggestion about using EXISTS and will test it next week. SQLite doesn't support right outer joins so we just added a `Root` column to our flat list of nodes and that has been working pretty well since it can be indexed.
      singulars
      1. PO
      1. USNtscCobalt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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