in reply to CDBI and multiple databases

Did we err by organizing tables within different databases, vs. one database?

Yes, I'm afraid so, if you want to use Class::DBI. You should consider changing your design to use a single database.

Replies are listed 'Best First'.
Re: Re: CDBI and multiple databases
by waswas-fng (Curate) on Mar 22, 2004 at 06:12 UTC
    Which kinda sucks if you have large blob fields as they can impact performance if they are in the same db as your other fields -- but then again didn't you toss your right to performance when you useed Class::DBI? =)


    -Waswas
[reply]
      Class::DBI follows all of the DBI best practices for performance. Separating fields into different databases for performance reasons is a very specialized task that no general tool is ever going to do for you. In general, Class::DBI can be helpful to performance because it implements things like lazy-loading that most people don't bother to do when they write their own database access objects.
[reply]
        Sorry, it was late last night when I posted that, I just meant to state that Class::DBI, being a general use layer, can never be as fast as well written specialized DBI and SQL. I was assuming that the OP may come back with an issue about blob fields in the same database being too unoptimized. I agree with you 100%.


        -Waswas
[reply]
Re: Re: CDBI and multiple databases
by water (Deacon) on Mar 22, 2004 at 13:58 UTC
    Thanks perrin.

    One other off-topic question: if we refactor the DBs into one monster DB over time, what are the downsides? It is harder for our power users to find tables, maybe, if everything is in one big bag. But beyond that, are there any CDBI downsides? Any database performance issues? Backup issues? The earlier comment about BLOBs was instructive -- while we're not using them, the comment made us realize that refactoring many small INNODBs into one large INNODB could have other consequences of which we're not aware......

    (apologies for this OT post, but it continues the thread, and we're curious.)

[reply]
      I'm not aware of any downsides to having all of your tables in one database, especially if you are already using them together.

      If multiple databases in MySQL are similar to multiple schemas in Oracle (i.e. you can just access tables by saying db_name.table_name), you may be able to convince Class::DBI to use that syntax. Try setting the table name in one of your classes to that fully-qualified syntax and see what happens.

[reply]
        A thousand ++'s, perrin. Hadn't thought of the obvious -- have all CDBI classes point at one database (hence they all get the same Ima::DBI handle), then use fully qualified names in the table statement. IT WORKS.

        This lets us have our cake and eat it too -- simple txns (mpeppler's comment was right on -- I didn't really like the app-level txn rollback handling trick) and different DBs. Hurrah!

        Many many thanks, all

[reply]

In Section Seekers of Perl Wisdom