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