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