Sunday, April 2, 2017

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

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.

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...