I've just finished up moving our benchmarking rig to The Cloud last night and was waiting for the first batch of result times to come back before commenting. The new rackspace server comes with quad core Xeons, 4GiB ram and some SSD drive (a massive step up from the dual core 32bit Pentium something it used to be running on), it also has a newer version of MSSQL (v12... which is 2014?) with all the latest service packs. I haven't yet updated MySQL and Postgres to the newer versions yet, so they're still running Postgres 9.4 (one version older than latest) and MySQL 5.6.23 (very old now!). It'd also be interesting to try with the latest MSSQL (v14 2016?) for comparison.
There was, unsurprisingly, across the board improvement. All of the benchmarking used to take 6 hours to run on the old server, and only 2 hours on the new server. I removed the ram limits on the new server. The new benchmarking times (to the nearest second) were:
- Foxpro 613s
- MySQL 847s
- MSSQL 1,382s
- SQLite 945s
- Postgres 617s
We have had an MSSQL contractor working with us for the past few weeks, mostly to set up some 'real world' benchmarking (simulating load, rather than just blindly running each report once like the benchmarking I had set up) but he's also been looking into some of our bottlenecks that we're having with MSSQL. The main one is that the cursors we were using were being inefficient (something about running sp_cursor
on the server a lot), by switching to forward-only cursors we were now running the queries on the server rather than one cursor lookup per row - maybe that makes more sense to MSSQL people
So switching to forward-only cursors gives us about a 20% improvement on MSSQL from what we've seen so far, but it's at the penalty of rewriting all our queries to make sure that all TEXT columns are the last columns in the SELECT, a not-small undertaking - and also means we can't count up the number of results using ODBC functions, we have to do a separate SELECT COUNT(*)
query. So we are testing speeds with forward-only cursors too, but because there's too many errors the numbers aren't very reliable.