For your information, our Manager is getting slow loading. Example, the Summary 36234ms (as displayed at the bottom right corner), Sales Invoices 16991ms and search within the Sales Invoices 22763ms. Overall performance is very slow. We have over 5100 invoices and on top of them, thousands of transactions, and hundreds of inventories. Is there a way to improve the loading performance?
Are you using Cloud or Server version of Manager?
What OS is hosting Manager and has the performance progressively dropped off over time with the increase in database size? What size is your data file at present? How many users are working simultaneously in Manager?
Is the Manager Server in-house or are your accessing it over the Internet?
Is performance poor if only one person is accessing the Manager server? We just need to isolate / eliminate factors that may contribute to the performance issue one at a time.
Something you could do to quickly eliminate whether the issue is local or not is to backup your data using the “Backup” button, then import it into the Cloud Manager system and confirm if the performance problem persists.
If it does it could be a database size issue. Another consideration could be the attachments Manager references.
If Cloud performance is good with your data then you will need to look at your infrastructure. However first see if you can provide some detail on your current arrangement and the information requested because it may simply mean your server needs a tweak.
I have done some testing about the slow loading as recommended.
a) Backup file size is 6.48MB
b) Import over to the Desktop Version using Windows 10 (personal PC Intel i3), the speed for the Summary after multiple tests, the results are between 23274 - 32731 (fastest - slowest). Sales Invoices (>5000 data) between 15851 - 24256. P&L Statement 16265 - 18424. Customers (>300 data), 15820 - 16062.
Interestingly, upon clicking the Cash Accounts and the sub-accounts within it, the speed is between 173 - 201. We have thousands of transactions. This is fast.
c) Import over to the cloud version (using my home internet 5Mbps), the speed for the Summary is between 8507 - 9806, Sales Invoices 8736 - 8836, P & L Statement 12515 - 12791. Customers 8561 - 8923.
Likewise, the loading of the Cash Accounts is amazingly fast, below 200ms
To answer some of your questions posted:
The Server Manager is hosted in the Intel i5, 8GB RAM, Windows Server 2012. The PC is dedicated to use the Manager. There are few installed software like the Office, but it is rarely used. The available space in the Harddisk is over 1.5TB. Our ISP speed is 100Mbps (usually we get around 60Mbps in most test speed we had done so far). Internal network setup is using both fiber and CAT6. We only access the Manager locally. We have 3 users. It is very rare to have 3 users work simultaneously in the Manager. The most is 2 users.
I hope same of these tests help.
If the cloud version seems faster than the locally hosted version, then try to monitor the resource usage in Resource Monitor on your Windows Server machine.
Generating reports is probably doing a lot of random read I/O; and if that’s the case, having a faster storage medium (SSD, flash cache, etc) would help speed up the things!
You backup file is not large at all. Can you use a different Browser to access Manager please also confirm the Server’s DNS and local network response. Those Manager response speeds you have are really poor. I do not think the issue is altogether Manager given that performance has improved when running locally and on Cloud but still not great. There is a possibility that the number of small transactions Manager is having to process is having an impact on Managers ability to display the result. This will need to be addressed by the developer as it could be a simple SQLite tweak or it could be a SQLite limitation.
Was this drop in performance progressive or after some update to Manager / Windows?
For the record what Manager server version are you using?
I think your Server Spec is OK but know Windows IIS web service systems go to sleep if not active for a while and then when you make a connection to the web server the service takes time to wake up and get going. This symptom will only occur if IIS is idle but thereafter it will be fine. At any rate check if your server has not got something like that going on. Follow what is suggested by - [quote=“micky, post:6, topic:8316”]
monitor the resource usage in Resource Monitor on your Windows Server machine
As suggested, I tested opening Manager using the Firefox directly from the host PC, the result for the summary is around 19000ms. Sales Invoices around 20000ms.
I don’t think there is any issue with the DNS or the network response. Yesterday, I used the backup file and import over into my personal PC at home, the loading was very slow as well. It is almost similar with the Manager speed at the office which has the faster network speed. It could be with the data itself (I’m guessing).
I am not entirely sure when the drop of performance occurred, but my staff had been mentioning the loading was getting slower before the school holidays, but bearable. During the holidays, since the office is off, I did some update the Manager to 16.12.4x and subsequently 51 (I updated to the newest 54 yesterday). Upon my staff returned from their break, as they enter new data, the loading is getting slower as they entered each transaction.
I have no knowledge of the IIS. Our setup is very basic. The host PC is original intended to be used as storage for the school. The setup is basically the AD, and a domain. However, we did not pursue further as the storage. It has been used primarily for the Manager.
This is during the the loading of the summary.
This is after summary has completed loading.
Both are done on the host PC.
Btw, there is no one in the school office today. So the network may be used over at the church office, but they are not related to our subnet.
I’m no techno, but what if you created a new Business with a basic setup and tested processing some transactions. If they processed “normally” then perhaps its the data file causing the response times.
If that’s the case, you could create a new business starting Jan1 and probably within 3 or 4 months the old business would be hardly referred to.
I tested the old businesses files, with half of the invoices data of our current business file, the loading is less than 200ms in all major tabs.
This was going to be the next step in my view [quote=“Brucanna, post:10, topic:8316”]
what if you created a new Business with a basic setup
[/quote] I think it is now narrowed down to your production data, you have pretty much proved it is not your network and hardware arrangement. This may be of interest to the developer and SQLite. Let us know what transpires.
As suggested, tested a new business and with a simple setup, the opening of each tab is less than 20ms.
Although SQLite 3.0 is capable of holding 140 terabytes of data but there are certain things which can bog it down. For instance, queries won’t get slow as long as they fit into the database’s page-cache, which is 2 MB by default. I have also seen that doing insertions with a pre-built index can be slow.
But that doesn’t mean there aren’t any workarounds e.g. drop and re-create indexes after major insertions.
I can see why the developers chose SQLite, considering they were going with Manager’s simple and modular design. IIRC, with the broad .NET coverage for db drivers, I don’t think it will be that hard to change dialect of backend from SQLite to say, one of the enterprise database RDBMS e.g. Postgres, MSSQL, etc., should the need arise
Last but not least, we should keep in mind that SQLite powers major applications e.g. Chrome, Firefox, Android, Dropbox, etc. Our everyday digital life would be incomplete without it.
Has any further work been done to confirm that this is an issue or not? Is this a performance limitation in Manager or SQLite when a certain volume of transactions are to be processed? I am concerned as our company data is growing and I would not like it to grind to a halt in a multiuser environment. Any thoughts?
The slow loading still persist. I am not a technical person in regards to the SQLite. I have tested the backup file in the Xeon Server, the loading remain the same despite the speed has much improved but not much. Currently I can’t stop the ongoing data entry. I have planned for starting a new business next school year to speed up the loading, but again it would take lots of works especially with our customers data of over 300 and invoice entries of each of the customers, chart of accounts… Wish there is a way where we can delete all the transactions and past invoices without touching the customers, employee and chart of account data. A great wish is that the developers would look into it to help improve the loading speed without the need of starting a new business.
Moderator - Can this be escalated please?
I will invite @lubos to join this topic. But I will not escalate the behavior as a bug, because no malfunction of the software has been reported.
@success127, would you be able to send me your accounting file to
email@example.com ? I will be able to trace this down what causes the slowness. For the record, there are businesses using Manager with 10x more data than you have so whatever is the issue, it’s definitely solvable.
Thanks Lubos. I have sent the file over to you.