Ted Zlatanov wrote:
> I realize much of this question belongs to database-specific domains,
> but here goes: I want RDBO insert operations to be faster. I need to
> load thousands of records per second into a database. I'm using
> PostgreSQL, and everything works correctly. The extra DB operations per
> insert are killing performance, however. I would rather optimize this
> through RDBO than hand-code it in SQL, hence the questions below.
I don't know the internals, but the first thing i'd ask here is are you
actually using the objects associated with these inserts, are you doing
a thousand inserts, then never actually using the objects? It seems to
me that if you don't need the objects, it might make more sense to batch
insert them into the DB outside of rose using whatever method is
quickest on your DB.
I assume you're using $obj->insert, not $obj->save right?
> So, first question: my tables are set up with minimal optimizations.
> Are there any easy PostgreSQL insert optimizations I can do? I have
> referential integrity constraints, and no indexing outside of primary
> keys. Pointers to outside references are welcome, but note that I've
> looked around and most information is about optimizing SELECTs and not
> INSERTs.
>
> Second question: RDBO loves to be thorough. Every time I use a foreign
> key in a one-to-many relationship (potentially storing it) it will check
> if the key exists, then either select or insert it, then use it. For
> certain tables, I know that's not necessary: if you have seen the key
> before, I'm sure it won't be deleted, so using it from memory is OK.
> All the work is inserts, in other words: the database is not touched by
> other processes so I know caching of simple foreign keys is OK.
>
> For example, say I have an order O100 with a single product P100 named
> "car." If I say (in pseudocode) load(O100).product("car"), and we've
> already seen that P100 is "car," the extra SELECT RDBO will normally do
> here should not happen. If we don't know P100 is "car" yet, RDBO can do
> the normal SELECT+INSERT cycle, of course. I have not seen a way to do
> this automatically in RDBO, and I think it is a common case.
You can avoid this extra select by pulling it from the DB immediately
using 'with', but I'm sure you're already aware of that.
> I think this optimization would save me at least 10 SELECTs per record
> because the products repeat very often. In addition, I'm curious if
> there's any other optimizations people can suggest, both in RDBO and
> outside it.
Not sure if any of this will be any help or not.
Adam
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
