Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re^2: DBIx::Class with two kinds of otherwise identical table types

by dwm042 (Priest)
on Sep 15, 2010 at 14:41 UTC ( [id://860223] : note . print w/replies, xml ) Need Help??

in reply to Re: DBIx::Class with two kinds of otherwise identical table types
in thread DBIx::Class with two kinds of otherwise identical table types


My first job as a professional in IT involved writing C++ wrappers for embedded SQL, so handling SQL statements isn't an issue for me. I'm certain I can craft decent enough SQL to do the job.

But like most Perl folks, I'm interested in the new technology and so am designing a Catalyst app in my mind as I work through the Catalyst tutorial (recommended, btw, as a great intro to TT, DBIx, Moose, and Catalyst).

So the question, in abstract terms, is at what point do you need to abandon the two table representation of data and go to a partitioned table implementation? Understand, from my POV, anyone who has ever thought of a file system layout understands where a partitioned table set is headed. You have a table with a column of pointers. The pointers point to the table to use. You would only do this when performance of a single table representation becomes an issue.

I've had and maintained databases with a single huge table. It's no fun when your data load takes several hours and the database is flaky. So I'm thinking about ways around that.

So, knowing whether DBIx supports partitioning is useful to know. Knowing that the TypePad people at Six Apart have run into this issue, and created Data::ObjectDriver to specifically deal with partitioning issues is useful to know too, when considering ORMs.


Replies are listed 'Best First'.
Re^3: DBIx::Class with two kinds of otherwise identical table types
by roboticus (Chancellor) on Sep 15, 2010 at 15:45 UTC


    Just a couple minor clarifications on partitioned tables:

    First, as far as the application is concerned, there's a single table. While it *could* access the subordinate tables individually, it normally wouldn't. When you submit your query, the database server has the task of converting your query against the main table into queries against the subordinate tables: so your application doesn't get more complicated--only the database management does.

    Secondly, MS SQL Server doesn't keep a table of pointers to the other tables: Instead there's a function that returns the table. I doubt that other database servers use a table of pointers, either, as that would be another table and index to maintain.

    Another advantage of partitioned tables is that a single query on your table can break into a query per subordinate table, and those can be queried in parallel. So many queries are faster that would occur in a non-partitioned table.



      Clarification much appreciated! I know I'm otherwise thinking in terms of analogies to files in file systems, with blocks containing pointers to blocks.