I am working through trying to overcome speed issues with my application running on Windows Server 2016
The server is running 2 x Xenon E5-2650 V4 @ 202, RAM 256GB
The server is running raid 10 with SSD drives (we used to have mechanical drives up to recently)
The server has 2 partitions, C for OS and Z for files. The application is installed on Z drive.
Users log into the server with remote desktop regardless if they are working remotely or in the office.
There is a speed difference between remote and local working, but mostly general lag due to internet connections.
The application and database plus all stored files (like pdf's the app can preview) are stored in the applications folder on the Z partition.
The application launcher program has a pool of exe files that will pick the next available for each user as they log in, so each user is using a separate exe to run their copy of the application.
There can be approximately 10-15 users on the server at any one time, about 80-90% use the application.
My development computer is Intel core i9 10850 with 64GB RAM. SSD drives for OS and separate SSD drives for files and development.
The speed difference between my development computer and the server is like night and day
In my quest to resolve speed issues, I have discovered some things:
The speed of the application in development compared to compiled exe is much faster.
The speed of the compiled application on the development machine is still magnitudes faster than when running on the server (approx 5-20 times faster)
I have made considerable use of 'SET COVERAGE TO' to compare speeds and understand where the problem areas are.
I have spent considerable time optimizing queries, splitting database into smaller tables, adding indexes, archiving records into backup tables, re-working processes and refining code to achieve a better result.
Most of the speed issues seem to be down to database access.
For instance a simple query selecting all records from a table with a condition on an indexed integer field. The condition returns all the records in the table.
The table is 42mb on disk with approximately 33k rows.
On my development machine in development mode the query takes 0.1 seconds the first run and 0.07 seconds subsequent runs (the table gets opened on the first run, I assume)
On my development machine running compiled exe the initial query is 0.165 seconds and subsequent runs 0.12 seconds
On the server 0.285 first run and subsequent 0.15 seconds
These numbers sound fine, but for instance recently I added a feature which took 0.5 - 1.5 seconds on the development machine and took about 20 seconds on the server.
I had to rework the query and the processes and I got it down to around 1 second on the server.
I still have some processes which take too long on the server and more work required to get the application running smoother.
However, the amount of work involved to tailor this application to the slower server is excessive and I feel unnecessary past a certain point, I mean if I have done all the above and still having problems then, how far do I take this approach is the question.
I disabled the antivirus scanning on the server for my user's folder so my temp cursor files were not being scanned. I have disabled scanning of the application folder.
I have run the application in different location on the server, including on the C drive (Slightly faster, though not greatly different), I test ran the application with the database on another drive location being a NAS drive attached the server. This was noticeably faster, though still not anything like my development machine.
Moving the database to the NAS drive may be an option, but there are many dependencies such as node servers that enable communication with my mobile apps and the fox database.
A lot of reconfiguring would be required.
The server is managed by an external contractor who has supplied and configured our hardware, maintains users and looks after the security and health of the server. (For many years)
I have admin access to the server and can make changes, but I don't want to make changes that may inadvertently change a setting that has been setup specifically for a reason by the IT contractor.
I am also thinking about core speed. vfp is a single threaded application. My development machine cpu cores may be individually be faster than the server. Whereas the server is designed for many users a bit more of a workhorse compared to my development machine.
Any comments welcome
The server is running 2 x Xenon E5-2650 V4 @ 202, RAM 256GB
The server is running raid 10 with SSD drives (we used to have mechanical drives up to recently)
The server has 2 partitions, C for OS and Z for files. The application is installed on Z drive.
Users log into the server with remote desktop regardless if they are working remotely or in the office.
There is a speed difference between remote and local working, but mostly general lag due to internet connections.
The application and database plus all stored files (like pdf's the app can preview) are stored in the applications folder on the Z partition.
The application launcher program has a pool of exe files that will pick the next available for each user as they log in, so each user is using a separate exe to run their copy of the application.
There can be approximately 10-15 users on the server at any one time, about 80-90% use the application.
My development computer is Intel core i9 10850 with 64GB RAM. SSD drives for OS and separate SSD drives for files and development.
The speed difference between my development computer and the server is like night and day
In my quest to resolve speed issues, I have discovered some things:
The speed of the application in development compared to compiled exe is much faster.
The speed of the compiled application on the development machine is still magnitudes faster than when running on the server (approx 5-20 times faster)
I have made considerable use of 'SET COVERAGE TO' to compare speeds and understand where the problem areas are.
I have spent considerable time optimizing queries, splitting database into smaller tables, adding indexes, archiving records into backup tables, re-working processes and refining code to achieve a better result.
Most of the speed issues seem to be down to database access.
For instance a simple query selecting all records from a table with a condition on an indexed integer field. The condition returns all the records in the table.
The table is 42mb on disk with approximately 33k rows.
On my development machine in development mode the query takes 0.1 seconds the first run and 0.07 seconds subsequent runs (the table gets opened on the first run, I assume)
On my development machine running compiled exe the initial query is 0.165 seconds and subsequent runs 0.12 seconds
On the server 0.285 first run and subsequent 0.15 seconds
These numbers sound fine, but for instance recently I added a feature which took 0.5 - 1.5 seconds on the development machine and took about 20 seconds on the server.
I had to rework the query and the processes and I got it down to around 1 second on the server.
I still have some processes which take too long on the server and more work required to get the application running smoother.
However, the amount of work involved to tailor this application to the slower server is excessive and I feel unnecessary past a certain point, I mean if I have done all the above and still having problems then, how far do I take this approach is the question.
I disabled the antivirus scanning on the server for my user's folder so my temp cursor files were not being scanned. I have disabled scanning of the application folder.
I have run the application in different location on the server, including on the C drive (Slightly faster, though not greatly different), I test ran the application with the database on another drive location being a NAS drive attached the server. This was noticeably faster, though still not anything like my development machine.
Moving the database to the NAS drive may be an option, but there are many dependencies such as node servers that enable communication with my mobile apps and the fox database.
A lot of reconfiguring would be required.
The server is managed by an external contractor who has supplied and configured our hardware, maintains users and looks after the security and health of the server. (For many years)
I have admin access to the server and can make changes, but I don't want to make changes that may inadvertently change a setting that has been setup specifically for a reason by the IT contractor.
I am also thinking about core speed. vfp is a single threaded application. My development machine cpu cores may be individually be faster than the server. Whereas the server is designed for many users a bit more of a workhorse compared to my development machine.
Any comments welcome
