Thank you for the advice - we have thought of writing it all in a stored procedure, but never got around to it. Will reconsider that decision now. :)
After some more investigation I found that our main user interface client app starts a single long running read transaction used to pull data for display in its grids. All updates are executed in small/short 'write' transactions. It is common for our client app to run 23 hours in a day (in a warehouse running shifts). We use FIBPlus components which works great, but now I'm faced with a catch-22. How can I commit/rollback the 'long read' transaction without disconnecting the FIBDatasets which feed the grids. If I do commit the read transaction, all the grids lose their content. I am baffled as to why we have only now reached this conundrum after 8 years with firebird - maybe because we are approaching 24h active clients for the first time. Any thoughts? --- In firebird-support@yahoogroups.com, "Hans" <hhoogstraat@...> wrote: > > Isn't it possible to write one SQL procedure > for all the 'Rules'. Should be much faster > than shipping data from the database, > apply some 'Rule' and shipping it back > to the database. If anything fails in > the SQL procedure or a data error was > detected by some 'Rule' and exception > rolls everything back. > > -----Original Message----- > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] On Behalf Of fdt4y > Sent: Tuesday, July 10, 2012 7:49 AM > To: firebird-support@yahoogroups.com > Subject: [firebird-support] Re: Why not sweeping? > > Now you've made me think (and you math is correct BTW). This might be > unrelated and if so, just ignore, but let me explain a simplified version of > our architecture and ask for some advice regarding transaction handling. > > We have an application that almost continuously runs through a set of > "rules" and processes them. Lets say there are 6000 rules stored in a table. > In order to process each "rule" the application has to perform a set of > selects and updates from many other tables (mostly storing warehouse > inventory). Depending on the outcome of the process, the rule's status, > lastexecutetime, etc is updated. These rules are then continuously displayed > in a summarized view in a dashboard application. > > We currently process each rule in its own transaction, so to commit or > roll-back depending on retuned values from the other statements. We cannot > process all the rules in one transaction (unless if we use savepoints?). > Also the inventory changes constantly so getting a snapshot at starttime > will not make sense when getting to rule 5000+. > > Given the above makes sense (to anyone except me:-), I do not see another > way to handle this without increasing the TransactionIDs continuously? > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item on the main > (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links >
