Our development staff have been doing some database access performance profiling of AyaNova with large database operations on different database platforms.
The idea is to determine the relative speed differences between FireBird and Microsoft SQL server.
Different hardware will of course show different performance figures. AyaNova is highly hardware scaleable in performance. This means that it’s speed of database access is directly linked to the speed of the hardware on the database server and the connection between the server and the end user.
Fetching and updating individual editable records such as a Client or a unit are not particularly database speed sensitive and were not timed. We focused on the slowest possible operation which is retrieving a very large list of work orders into a main work order grid in the program (Service navigation pane - Service Workorders grid).
Test were done without applying any filters to the grid to ensure the slowest possible performance by retrieving all data.
Tests were run several times in each scenario to ensure the times are typical.
Testing was performed with AyaNova 3.x and a test database composed of real world (not simulated) data consisting of 9900 work orders retrieved into the main work order list grid with no filters set.
The test was over a network from a single workstation to an older database server that was running both Firebird and Microsoft SQL Express 2005. Each of which contained the exact same database. For comparison purposes an embedded FireBird configuration (with the same database) is also shown which was run at the workstation.
Hardware:
=-=-=-=-=
Database server
Windows 2000
Athlon XP 2000+ 1.67 GHz
512mb RAM
100BaseTX network adapter
Workstation
Windows XP Professional SP2
Intel P4 3.06ghz
1GB RAM
100BaseTX network adapter.
Results
=-=-=-=-=-=
Populating a Workorder list grid with 9,900 work orders
MS SQL Express 2005 - 2.2 seconds avg.
Network Firebird server - 7.5 seconds avg.
Standalone default embedded firebird on workstation - 46.4 seconds avg.
During testing we noticed that the FireBird test caused the CPU to peak out at 100% utilization. The SQL Express server peaked at 43% cpu utilization. It appears that SQL Express is more efficient in utilizing processor resources under the test scenario.
Also note that the embedded firebird was run on what is a pretty fast computer, much faster than the test server, even so it took over 6 times longer to retrieve the same data, so it’s clear that there are major performance benefits to upsizing from an embedded Firebird configuration to a server configuration if performance is at all an issue.
The real benefit of the Firebird server is that it’s multi-platform and offers the tiny embedded mode which is handy for trialing our software in a small download without having to set up a full server as well as for small offices with a single user running AyaNova.
Clearly if you don’t need to run a Linux database server and need to share a database between mutiple users Microsoft SQL Express or any of the paid for versions are the way to go.