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
>