Terrible performances using a remote Azure SQL Database

Question

We're currently evaluating Azure's SQL Database service (vs local installation) because we would like to have the "Always Encrypted" feature integrated in our (java-based) application.

Problem is, performances are terrible.

I'm connecting from a physical server located in the same area (Paris) to the database created in the "France Central" region.

I wrote a very simple java application which does 5000 inserts (prepared statements) in a very simple table, it's using the Microsoft jdbc driver and the Azure's provided connection string.

It takes nearly a minute to complete, whatever the type of SQL Database I use (last test with S6 Standard). The java application ran on a production server (8 cores). I would expect some latency from using a remote database, but not to this level. (the same test is running in 5 seconds with SQL Server on the local server).

Is it what's to be expected ? I though our case was the case addressed by the SQL Database service (remote SQL server). I also created a VM in the same region, connecting through the public IP of the database, and it was nearly as bad. I tried to create SQL Databases in other european countries, and it was just worse.

Answer 1

Seems reasonable. Especially if the inserts are being run in serial. Breaking it down:

Say there's 30ms RTT between the app and db server. Since SQLServer is an ACID compliant database, and the database driver honors this, the theoretical maximum amount of TPS is 33 per second as the driver will block until it receives an ACK from the database. (1000ms / 30ms = ~33 TPS)

You are achieving around 83 TPS, so I'm guessing the latency between the app server and the db is around 12ms.

On the local server, you probably have sub millisecond timings.

Remote databases are just a bad idea. But if you have to do it, try to batch operations into a single transaction as you can pack more data into a RTT hop. With that said, it means a failure is much more expensive (retrying 1 insert vs retrying all 100). Take it all into consideration, but anything you can do to reduce latency (or increase parallelism) will make a substantial difference.