Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Efficiently Walking Large Data Structures Stored Across Multiple Tables

by jerrygarciuh (Curate)
on Feb 28, 2004 at 14:55 UTC ( #332478=perlquestion: print w/ replies, xml ) Need Help??
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
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

      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
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://332478]
Approved by DaWolf
Front-paged by hsmyers
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (7)
As of 2014-12-27 14:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (177 votes), past polls