Large Transaction Mode

[SBarnes]

Hi Guys,

Does the Large Transaction Mode that existed in 6.5.13 exit in version 7 or is there a plugin that does it?

Is there also a way of limiting the result returned in a standard search screen as this is causing a speed issue for databases with either lots of debtors or lots of inventory items or how could you do this with a plugin and introduce paging?

[Mike.Sheen]

Does the Large Transaction Mode that existed in 6.5.13 exit in version 7 or is there a plugin that does it?

No - we never implemented that because version 7 is a lot more performant in displaying transactions, and also because the 2 forms which had this Large Transaction Mode - creditor maintenance and debtor maintenance on the transaction tab - have alternate forms to manage transaction allocation which cope with large transaction sets by not trying to display all of them but instead have the user select an invoice or credit and then select the corresponding transaction to allocate to.

I'd like to learn more about the issue you're having - we might be able to do something via a plugin, or improve things in the standard software.

Is there also a way of limiting the result returned in a standard search screen as this is causing a speed issue for databases with either lots of debtors or lots of inventory items or how could you do this with a plugin and introduce paging?

Search forms (that is the Jiwa search dialog) use server side cursors, so we only return the rows which are able to be displayed within the confines of the current form size - you can't get much more efficient than that. If you are having performance issues with the search dialogs then I have a lot of confidence that some server side tuning of indexes / statistics can be done to improve things for you.

A couple of things to try (If you've not done so already) is:

• Rebuild indexes as described in Slow performance when retrieving data
• Match database SQL compatibility

There is also the rebuilding of database table statistics, which I personally have seen a few times to improve performance a great deal - but we don't have an article for that it seems. I'll circle back when I've created the article and post a link to it here.

EDIT: I've updated the article Slow performance when retrieving data to include a section on how to rebuild table statistics.

Mike

[SBarnes]

Hi Mike,

I have done all three steps as I only discovered the issue yesterday, including restarting SQL server.

Unfortunately it has had no effect the real issue I believe is when the debtor transaction tab is selected and in the example that is slow, it's taking literally minutes to load, there are 2000 invoices and 4400 payments and admittedly whilst this is a fair volume, its not a huge number of records for SQL server.

Whilst other tabs use stored procedures to load their data this one doesn't so I can't try reviewing the procedure to see if there is anything that can be done.

[Mike.Sheen]

it's taking literally minutes to load, there are 2000 invoices and 4400 payments and admittedly whilst this is a fair volume, its not a huge number of records for SQL server.

So it's not a SQL Server performance issue, but a Jiwa application performance issue. I bet if you ran a profile trace you'd see the queries reading the transactions and allocations taking a few seconds, and the rest of the time is Jiwa digesting and displaying the data.

[SBarnes]

Hi Mike,

So I suppose that's going to bring it back to supporting some sort of paging(large transaction mode) type scenario like 6.5.13 supported, how hard would it be to do in a plugin?

I haven't looked but is there a way to get at the SQL that the debtor object uses to read the debtor transactions, would a read start event and client cancel exception then allow a plugin to then take over managing filling the grids?

[SBarnes]

This problem also seems to exist for unprocessed orders in the debtor screen as well.