Replies to that thread solved the issue but got me to thinking. In a previous life I had to query Sage Line 50 with the ODBC driver and for most data this worked fine. However as soon as you attempted to read nominal transactions it became so slow as to be unusable taking several minutes to do even a simple query.
Is this an inherent problem with the way accounts systems store nominal transactions or is this just a coincidence?
Serious QuickFile Question
Could a transaction ID be added to this export to make it easier to combine data from multiple exports?
I suppose it depends on the architecture of the system. The nominal ledger table is usually pretty huge as it stores every double entry movement between nominal codes, most other aspects of accounting (e.g. invoices, expenses, journals etc) are an abstraction from this table.
Our nominal ledger data table alone contains over 60 million records, so if you’re running a query that pulls back a large chunk of data, things can slow down. That said if the data tables are optimised with all the required indexes you can still return 100 records in a matter of milliseconds.
We are often asked if we can allow more than 50 records at a time to be exported. Due to the limitations explained above, you can’t really let hundreds of concurrent users run large queries on the database. In most cases we decouple that process to the backup system. A backup will contain a snapshot of all account level data across multiple tables but is requested manually and goes into a queue. We then have a background worker thread that prepares these backups in a synchronous fashion.
I don’t see why not. I’ve created a post in our planner so this can be looked at. Will update you in due course.
