On Fri, Oct 19, 2001 at 08:04:29AM -0500, Dave Peticolas was heard to remark: > Index: src/backend/postgres/PostgresBackend.c
I notice you've been working on the pg backend a lot, and at least some of your changes help performance. I suspect, am not sure, that my biggest design mistake was making the guid the 'primary key'. I beleive that there might be a big performance boost are (or at least, a more consistent behaviour) if the iguid was made primary key. sql db's use the 'primary key' is the primary index into whatever hash-table/b-tree they use to index the data. Clearly, an 'int' compare would be much faster than a string compare. I assumed, falsely, I surmise, that postgres would be smart enough to replace my string primary key with an int, but that doesn't exactly seem to be the case. So I assume that using iguid as the primary key should improve performance. The other thing I noticed was that seemingly inconsequential changes made big differences in performance, sometimes by 20% or so, sometimes by factors of 3 or 5. This seemed a rather unstable situation, and ... well, I'm not sure what to recommend. Just be careful; seemingly idempotent changes to the sql queries can make a big impact on performance. If you find you need to change the structure of the sql tables, then note that I added a fully general automatic upgrade mechanism. Its in upgrade.c, upgrade.h. It looks like its not documented .... hmmm. I thought I'd written documentation. Upgrades are treated like patches; the sql db is 'patched'. Each patch carries three version numbers: major, minor, rev. A client can work with a database only if the clients & databases major verion numbers are equal, and the clients minor number is equal or newer than the db. The third number, the rev number, is irrelevant if the above condition is met. The rev number is handy only for making sure that patches are applied in a specified order. The gncVersion table stores these three numbers. There's also a human-readable text string, so that the different upgrades can be labelled. The actual upgrade process is 'ad hoc'. The client pops up a dialog asking the user if they want to upgrade the DB. If so, then the pgendUpgradeDB() routine is called. This routine is a set of nested case statements that compare version numbers, and apply patches as needed. As of this writing, there's only one upgrade: 'put_iguid_in_tables()'. Note its ad-hoc manner. Everything else in upgrade.c is focused on trying to figure out when its appropriate to call 'put_iguid_in_tables()'. Other upgrades should follow this same pattern: create the adhoc routine, and plug it into a case statement in the pgendUpgradeDB() call. Everything else is automatic. --linas -- pub 1024D/01045933 2001-02-01 Linas Vepstas (Labas!) <[EMAIL PROTECTED]> PGP Key fingerprint = 8305 2521 6000 0B5E 8984 3F54 64A9 9A82 0104 5933 _______________________________________________ gnucash-devel mailing list [EMAIL PROTECTED] http://www.gnumatic.com/cgi-bin/mailman/listinfo/gnucash-devel
