Performance of the SQL server

Vertec prefers to run on the open source Firebird database. I wrote a blog post some time ago titled Why Vertec relies on Firebird as its database server. Since this is about the performance of MS SQL servers, I quote from this blog post:

«Performance: for the vast majority of operations, firebird is faster than, for example, MS SQL server, which has to deal with much more overhead.»

There’s no denying that: For the vast majority of Vertec use cases, Firebird is the better choice. It offers better performance, less complexity and is easier to operate.

However, since there are also (larger) Vertec customers who rely on MS SQL servers for good reason, I would like to expand on the topic “performance of the SQL server” here.

First of all, an MS SQL server on “shared” cloud hardware delivers poor to very poor results.

This is mainly about the Query Analyzer. This software component first analyzes an incoming query formulated in SQL and develops the best possible plan for how to execute it efficiently. Experience shows that the Query Analyzer takes a lot of time to do this. Apparently, Microsoft introduced an “improved” Query Analyzer around 2014, but it also consumes more resources. The increased demand for resources, especially CPU power, causes the Query Analyzer to react very sensitively to missing CPU resources, for example in the case of a shared CPU (vCPU) in a virtual machine.

The execution times of Firebird and MS SQL servers are directly comparable, because they are the same Vertec database on the same server. We tested a complex SQL query with several nested subqueries, e.g. WHERE bold_id IN(SELECT project FROM ...), as they are common in Vertec in SQL folders or via Python with vtcapp.getwithsql().

The following measurements illustrate this. Firebird processes the query constantly in 16ms (0.016s), while the SQL server takes a full 9s for the same query! But what does the SQL server do during this time? It takes 8.6s for compiling alone. The execution takes less than 500ms.

In computer science, 8.6s are half an eternity. You can significantly reduce this time if you give the Query Analyzer, which can only use one thread, enough CPU resources, namely a core that is not otherwise busy and can fully concentrate on the very long computing time that the Query Analyzer requires.

To assess the impact more precisely, we ran the same query with the same database once on a “worse” (because shared) cloud infrastructure and once on a local, non-virtualized infrastructure (“bare metal”). The difference is striking: The Query Analyzer, which takes 8.6s in the cloud scenario, completes exactly the same work on the local infrastructure in 0.3s. This is still massively slower than Firebird, but still within acceptable limits!

On-premises customers who want to use Vertec with SQL servers should run it on local hardware that is as high-performance as possible. If this is not possible, care must be taken that cloud infrastructure hardware resources are not shared with other Vms if possible. If you want to run Vertec in the cloud with high-performance, the use of Firebird is highly recommended.