On Fri, Jan 21, 2011 at 12:29, Richard Quadling <rquadl...@gmail.com> wrote:
> Changing data in a database is the role of the database engine. It is
> much more efficient to have the cost on the insert than it is on the
> select.

Agreed. On insert I could even delegate the operation to another
thread which does not timeout with the pageload.

> The adjacent list model is very expensive at n-levels for the select,
> but trivial cost for the insert. If you are inserting millions of rows
> but only occasionally looking at the data, then stick with the
> adjacent list model. But if tags and n-levels are regularly accessed
> and form a main part to the functionality of the app, then you may
> want to reconsider.

I've already reconsidered after some sleep and coffee!

> Sure, the insert for the nested set model is more expensive in terms
> of the number of rows to amend, but indexing will certainly should
> certainly help. If you have tools to help optimize the tag table and
> the queries you use, then I'd follow the recommendations (I use MS
> SQL, so my Query Optimization tools help me here). The nested set
> model is extremely efficient on the select.

Interesting. I am using MySQL for this application, but another hat I
wear is learning C# with MS tools and I will have to look into the
Query Optimisation.

> It is a trade off that you have to decide upon, based upon your data
> and needs. If, as I suspect, you are going to be doing a LOT of
> selects on the tags and (in the future) to multiple levels, then this
> aspect needs to be very efficient.

You suspect correctly.

> For me it is well worth the effort of moving from the adjacent list
> model to the nested set model.
> Both mechanisms work. In my opinion, the adjacent list model is for
> truly simply lookups, not for complicated n-levels.
> One of the changes I made to the nested set model was for a Bill Of
> Materials module. The client made complex machinery (industrial
> lathes). The sum quantity for all the parts were in the 20,000 region.
> Each nut, bolt, screw, etc. Many sub-assemblies. The parent/child node
> logic was massive in dealing with retrieving questions like "How many
> machines can we build?", "What stock do we need to buy/make to
> complete an order of 20 lathes?". Lot's of recursion into each level
> to build the list. Getting the results would take 3 or 4 minutes (this
> is in a non SQL environment using a peer-to-peer modified D-ISAM
> database - it was already slow because of all that). When I moved to
> the nested set model, no recursion and 1 query (more or less) and I
> have all the results I needed. It was seconds in comparison.

Thanks, I enjoy reading these real-life scenarios. This was a terrific example.

Dotan Cohen


PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to