How to minimize latency between Azure App Service and Azure SQL Database?

Question

We have an Azure App Service application using Java Tomcat 8.5 running in West US Region. The Service Plan for this Service is Standard, Big.

This App Service application is connected to a SQL Database Service that belongs to an Elastic Group also in West US which Service Plan is Standard 1200 eDTU.

The problem we are facing is a very poor performance between the interaction of both servers.

We have been testing by calling a RESTful WebService on the App Service that should insert 50 records on 2 tables on the database.Total 100 records inserted. For doing this task it takes 3000 ms.

We have been measure this by saving timestamps between starting inserting process, and ending insert. So no time from local client to app service communication is included.

If we run the same call to Restful WebService on local machine with SQL Database running on the same local machine as the Tomcat Server, the task was completed in 400 ms.

So I'm intend to think that the problem should be the latency of the connection between this two servers on the azure cloud or maybe I was missing something here.

So that's why I'm asking, I'm pretty new to azure technology and maybe there is other options we can take to provide a better communication between the servers or there is a way I don't know to debug this problem.

As a side question if this 2 server where inside of my own datacenter I will make sure it will communicate directly on its own network, not sure if that king of service is available in azure at the moment.

This are the definitions of the 2 tables the service is inserting records on:

CREATE TABLE [dbo].[Vehiculo](
   [Id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
   [CodigoCliente] [int] NOT NULL,
   [NumeroVehiculo] [int] NOT NULL,
   [Denominacion] [varchar](99) NOT NULL,
   [NumeroTarjeta] [int] NULL,
   [Placa] [varchar](10) NULL,
   [CuentaPresupuestal] [varchar](99) NULL,
   [NumeroPatrimonial] [varchar](99) NULL,
   [Grupo] [varchar](99) NULL,
   [NumeroEconomico] [varchar](99) NULL,
   [ClienteId] [decimal](18, 0) NULL,
   [UltimaActualizacion] [datetime] NULL,
   [Tenant] [varchar](50) NULL,
   [IdOrigenDeDatos] [decimal](18, 0) NOT NULL,
   [FechaAlta] [datetime] NOT NULL,
   [FechaUltimaModificacion] [datetime] NOT NULL,
   [PrimerActualizacion] [datetime] NOT NULL,
 CONSTRAINT [PK_Vehiculo] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Cliente](
    [Id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
    [Codigo] [int] NOT NULL,
    [Denominacion] [varchar](255) NOT NULL,
    [CondicionVenta] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [TipoDeValor] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [PlazoDePago] [int] NOT NULL DEFAULT ((0)),
    [UltimaActualizacion] [datetime] NULL,
    [Tipo] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [Grupo] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [Zona] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [EjecutivoDeCuenta] [varchar](50) NOT NULL DEFAULT ('NO INFORMADO'),
    [Clasificador] [varchar](20) NOT NULL DEFAULT ('CLIENT'),
    [Indicador001] [varchar](50) NULL,
    [FechaIndicador001] [datetime] NULL,
    [Indicador002] [varchar](50) NULL,
    [FechaIndicador002] [datetime] NULL,
    [Geolocalizacion] [varchar](50) NULL,
    [Latitud] [decimal](8, 6) NULL,
    [Longitud] [decimal](9, 6) NULL,
    [Colonia] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [Delegacion] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [Ciudad] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [Estado] [varchar](50) NOT NULL DEFAULT ('N/A'),
    [Correo] [varchar](200) NULL,
    [Tenant] [varchar](50) NULL,
    [IdOrigenDeDatos] [decimal](18, 0) NOT NULL,
    [FechaAlta] [datetime] NOT NULL,
    [FechaUltimaModificacion] [datetime] NOT NULL,
    [PrimerActualizacion] [datetime] NOT NULL,
 CONSTRAINT [PK_Cliente] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

As you can see first table Vehiculo has 17 Columns, and Cliente has 29 Columns.

what is the size of the data you are trying to insert, roughly? Speed on a local machine is always going to be faster than two machines over a network, hopefully your not expecting to see the same performance as everything local? — Sam Cogan, Sep 21 '18 at 15:08
Thanks for your comment @SamCogan The size of the body in JSON format of the RESTful request is about 27Kb. As I mentioned above this results in 100 inserted records, 50 records on each of the 2 tables. And no I'm not expecting the same performance but I'm trying to figure it out if there is something I can do to improve it. — Daniel Ardison, Sep 21 '18 at 15:15
The thing that is going to impact the speed is the size of the records you are inserting, if your inserting 100 rows with 1 column its very different to 100 rows with 1000 columns — Sam Cogan, Sep 21 '18 at 15:18
To clarify I updated the post with the definition of the tables the service is working on — Daniel Ardison, Sep 21 '18 at 15:39

score 1 · Answer 1 · answered Sep 21 '18 at 21:49

1

There's not a lot you can do to control the proximity of your VM to your SQL DB beyond ensuring they are in the same region and same availability zone if you use them. A few things to consider:

You can enable accelerated networking on your VM to increase the network throughput
If you're using any sort of VPN or Express Route to connect back to on-prem resources, make sure the traffic from IaaS VM to PaaS SQL is not being routed back on-prem and out the internet
Look at using some of the diagnostic tools in Azure to measure performance, things like Azure Monitor, Network Watcher and the SQL monitoring tools to see if you can spot any issues

answered Sep 21 '18 at 21:49

Sam Cogan

38,158
6
77
113

Thanks for your answer @SamCogan. In reference to it I would like to clarify a couple of things: 1) When you say VM what do you mean? I don't have e Virtual Machine I have a Azure App Service (AAS), are they synonyms? 2) I don't know where can I enable accelerated networking in the AAS could give me a tip? – Daniel Ardison Sep 21 '18 at 23:59
Also we are not using any VPN. Only Azure App Service that provides REST API to clients and SQL DB Service that connects to it. – Daniel Ardison Sep 22 '18 at 00:06

How to minimize latency between Azure App Service and Azure SQL Database?

1 Answers1