performance with large datasets

[joncas]

The current master-2019-2 implementation does not seem to scale well to large datasets.
Running a test with 4000 customers, 7000 products and 1500 invoices, it takes 42 seconds to load “manage invoices”.
Investigating with the XDebug profiler showed that half the time was spent formatting all the currency values. Replacing that with displaying the raw amounts halved the time, but loading all invoices ahead of time rather than just the first 25 is still not workable.
I had tested the 2013 version for a friend, which loaded only the initially visible number of records and fetched the rest on demand, which works fine with any number of invoices.
For creating and editing the invoices, the problem is worse, because all the customers and all the products get loaded ahead of time, so opening a new invoice takes 46 seconds and runs 88000 sql calls.
In the 2013 version I had managed to avoid having all the products loaded by replacing the product SELECT with a text input where the user enters the product id.
When I tried that with the current version, loading all the customers still causes too much of an overhead, so something in the database access seems to have a much larger overhead than before, but I’m not sufficiently proficient to figure out where this happens.
In case anyone is interested, enclosed are 4 of the profiler summaries.
Both versions were run on a fast desktop pc with MAMP and PHP 7.
On a dedicated server running php natively, the timing was similar.

Attachments:

1. profiles.zip

[fearless359]

I’ve made changes to a couple of files that I would like you to test and report on their performance. They are in the attached zip file. Extract the manage.tpl file into the templates/default/invoices directory, and the Invoice.php file in the Inc/Claz directory. The changes in these files affect the display of invoices on the primary screen. They remove the formatting of the Date column and move the formatting of the Total and Owing columns to the display script. Please give them a try and let me know what if any performance changes you see.

• This reply was modified 3 years, 10 months ago by fearless359.

Attachments:

1. inv_test.zip

[joncas]

Hello, and excuse the late answer (I had forgotten to tick the “Notify” box for this post, so I didn’t realize that you had responded).
It seems a little faster, but I’ll have to try on the unix server, because in MAMP no invoices show in the list (opening the list pauses for several seconds, but none are displayed).
The data.json file contains all the invoices (2.6 MB), but they are not rendered on screen due to an error in the javascript (see enclosed image), which may be due to running on MAMP on an Apple computer.
I can create a new invoice, and all the customers and products show, but that part is still very slow which is inevitable if they all have to load into the selects.
I’ll try to test this on the unix server over the weekend.

Attachments:

1. 

   picture-1.jpg
   

[joncas]

p.s.: I solved the currency error (it was due to an invoice preference using the € symbol instead of EUR.
Opening the invoice list is indeed much faster than before: it now takes 8 seconds.
Creating a new invoice still takes nearly 40 seconds.
But even with the latest version of master_2019.2 in a clean install, the comma used as a thousands separator in the amount still gets passed on to the database insert query and causes the same error.

[RRowley]

FYI, there is a new local.currency_code field in the custom.config.php file. It is added automatically but is likely not set right for you. Check for it under the [production] header and for clarity, move it to the other local settings for locale and precision.

Once this is set up, let me know the following:
1) local value settings in the custom.config.php file.
2) What are the settings for currency sign, currency code and locale fields in the INV PREFS (SETTINGS) for Invoice type records.

[Author]

Posts