1

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.

8

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.

3
  • Great answer, Learn something new everyday. 33 TPS seems so low though – pm1391 Apr 11 '18 at 2:47
  • 1
    It's one of my favorite interview questions ;) - finding good resources that discuss this is hard, but this article from the Galera team does a great job highlighting the phenomena in action: galeracluster.com/2015/07/… – Brennen Smith Apr 11 '18 at 9:11
  • Thank you ! On a hunch, I've done a mtr to the database sever. I got a lot of drops (nearly 50%), even with a 1s delay. Don't know if this test is meaningful though. – huelbois Apr 11 '18 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.