Efficiently Walking Large Data Structures Stored Across Multiple Tables

jerrygarciuh has asked for the wisdom of the Perl Monks concerning the following question:

Honored monks,
I am in the last stages of creating an online curriculum tool using Class::DBI, Template-Toolkit, and CGI::Application. The administration tools are almost done. In our first migration from our dev server to the production server we are finding that performance is taking a hit. This is not as much of a concern for the admin tools as it is for the next piece of the puzzle the curriculum interface for students.

Our curriculum model has a very clean structure with all objects having a single parent object type and a single child object type. If speed were not a concern walking this structure is quite easy using CDBI. However, it involves quite a few record lookups, eg pull the pages for this section of this chapter and see if $page_number + 1 exists.

Since speed is a concern I am looking for alternatives for looking up a page id without having to potentially look up sections and chapters. My initial impulse is to alter the wizarding process for creating the curriculum so that it maintains a map of the whole structure of each curriculum as a var in a single record. I am imagining a hash that nests at each level the id for the current objects and an array of it's children. Each child also has it's id and the array of it's children. In this manner one could look up Curriculum 1, Chapter 3, Section 2, Page 5 or see if it has a page 6 and finding none go on to Section 3 or Chapter 4 etc. without multiple lookups in the db.

My question to you is what solutions have my betters applied to similar problems? If my solution has any merit please advise me of any gotchas you see.

TIA
jg

_____________________________________________________
"The man who grasps principles can successfully select his own methods.
The man who tries methods, ignoring principles, is sure to have trouble.
~ Ralph Waldo Emerson

Comment on Efficiently Walking Large Data Structures Stored Across Multiple Tables Download Code

Replies are listed 'Best First'.
Re: Efficiently Walking Large Data Structures Stored in Multiple Tables by kvale (Monsignor) on Feb 28, 2004 at 15:34 UTC
It sounds like your production app is slowing down due to the all the database accesses to find the next page, so what you want to do is minimize those. You can think of progressing through the cirriculum as a sequential walk through the records, which makes me think of a linked list. So a simple solution is to add a column to your db that gives the location of the next page 'next_page_ID' (assuming that you have a pages table in which each page has a unique ID). Then finding the next page has no extra lookups or tricky hash walking--everything has been precomputed beforehand. -Mark	[reply]
Re: Re: Efficiently Walking Large Data Structures Stored in Multiple Tables by jerrygarciuh (Curate) on Feb 28, 2004 at 15:40 UTC
Mark, That is a great idea! I can make the curriculum admin wizard do the heavy lifting as the items are being added/reordered. Thanks! Off to work on it... jg _____________________________________________________ "The man who grasps principles can successfully select his own methods. The man who tries methods, ignoring principles, is sure to have trouble. ~ Ralph Waldo Emerson	[reply] [d/l]
Re^3: Efficiently Walking Large Data Structures Stored in Multiple Tables by adrianh (Chancellor) on Feb 28, 2004 at 17:28 UTC
If you're dealing with a hierarchy of information (OT?) Recursive sql queries? might be of interest.	[reply]
Re: Re^3: Efficiently Walking Large Data Structures Stored in Multiple Tables by aquarium (Curate) on Feb 29, 2004 at 21:51 UTC
Re^5: Efficiently Walking Large Data Structures Stored in Multiple Tables by adrianh (Chancellor) on Feb 29, 2004 at 22:29 UTC
Re: Re^3: Efficiently Walking Large Data Structures Stored in Multiple Tables by dragonchild (Archbishop) on Feb 28, 2004 at 18:51 UTC
Re: Re: Re^3: Efficiently Walking Large Data Structures Stored in Multiple Tables by tilly (Archbishop) on Feb 29, 2004 at 01:42 UTC
Some notes below your chosen depth have not been shown here
Re^5: Efficiently Walking Large Data Structures Stored in Multiple Tables by adrianh (Chancellor) on Feb 29, 2004 at 22:41 UTC
Some notes below your chosen depth have not been shown here
Re: Efficiently Walking Large Data Structures Stored Across Multiple Tables by iburrell (Chaplain) on Mar 01, 2004 at 20:59 UTC
First, can you write a SQL statement that gives the information you need from the current schema? Class::DBI can be inefficient when doing traversals across multiple objects because it does lots of individual queries instead of a single query with joins. If you can write some SQL statements that do the job with the current schema, then you don't need to deal with trees or nested sets or anything like that.	[reply]


"be consistent"
	PerlMonks