Here is the scenario. I'm using SA as the data abstraction/access layer between a desktop application and postgresql database. The user interacts with the application primarily by browsing large numbers of records (on the order of tens of thousands of records at once). Occasionally the users needs to create new records. After the user has interacted with the application for a while, there may be over 100,000 objects that have been fetched into memory. These objects appear in tables that the user sorts and searches, drop down lists for changing foreign key relations and so on.
The problem appears when it comes time to create and save a new record. When saving a new record, the commit often takes more than 20 seconds. This is an awfully long time for the user to wait while looking at a spinning beach ball. In some extreme cases, saving a new record can take minutes. Now, my question is, am I using SA the way it was intended to be used? For example, I create a single session when the application starts up and use that single session throughout for all interactions with the database. The reason behind this is because the objects that are used to populate drop down lists and look-up tables in the user interface, need to persist, such that when editing existing records or adding new ones, I don't want to have to merge all of those objects into a new session (there are a LOT of user interface elements that contain references to instances of SA objects) all the time just to make a simple edit to a record. I posted a TRAC ticket which was immediately closed as invalid because, I was told, I should be flush()'ing the session ever 1,000 objects or so. I turned on autoflush in the sessionmaker() as a test and I have not seen any improvement in performance. Furthermore, the behavior of autoflush is incompatible with the way users interact with the application in that when autoflush is on, the session attempts to flush "incomplete" objects to the database, which end up violating non- null rules, foreign key constraints and whatnot. Is there a particular idiom that I'm missing when it comes to using SA as the DAL for a desktop application? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
