0

Currently my web application runs on two Cloud-based Windows 2012 R2 Servers - one running Tomcat and one running SQL Server Express. The Windows Servers are 4GB Intel XEON(R) CPU E5-2660 v2 @2.20 Gghz

I am testing my application on PaaS (Platform as a Service) in Azure. I created a Linux Web App with Tomcat 9.0 to hold the application (P1V2) and an Azure SQL Server to hold the database (S2)

One test I did was to compare the time it takes to produce an Excel report (using Apache POI) on both systems.

On the Cloud system (running SQL Server Express) it took about 10 seconds. On Azure it took about 35 seconds.

Obviously I would like the Azure system to be at least as fast as the one based on SQL Server Express, especially as the cloud system runs SQL Server Express which is capped at 1GB and 1 core.

I have tried the following:

  1. Checked to see if there any spikes in the dashboard chart for the database. There are no significant ones - DTU's are maximum 25%
  2. I added Query Performance Insight / Recommendations / Automate to automatically tune the database. This did speed it up somewhat but by no means enough.
  3. I read Help, My Azure Site Performance Sucks! and Why is running a query on SQL Azure so much slower? and Azure SQL query slow
  4. I checked that the database and application were in the same location. (West Europe)

I imagine that the problem is the database.

As an example, I found a query (using the Query Performance Insight / Long Running Queries) that runs in 2 seconds on Azure and in 0 seconds on SQL Server Express. Note that I am NOT asking how to optimize this query. Rather I am imagining that the fact that this query takes longer on Azure - with the same database schema, the same data and the same indexes - might be a clue as to how to speed up my application as a whole.

 SELECT cp.*
    ,(
        SELECT min(market_date)
        FROM mydb.rates ms
        WHERE ms.curr1 = cp.curr1 
            AND ms.curr2= cp.curr2
        ) MIN_MARKETDATE
    FROM pairs cp
            order by curr1, curr2
gordon613
  • 2,384
  • 8
  • 45
  • 72

1 Answers1

1

The easiest way to do an apples-to-apples comparison is to use the vCore model for your Azure SQL. You say you are using an S2 database which is 50 DTU or half of a core. You would need to scale up to at least an S3 to be the equivalent of a 1 core VM.

That will ensure you are testing with the same general setup and should help you to match performance.

Ken W MSFT
  • 2,047
  • 1
  • 9
  • 16
  • Thanks @Ken W MSFT. I wasn't sure if you meant I should actually try a vCore pricing, or a vCore *equivalent* (i.e. S3). Anyway I tried the former - I scaled my database from Standard S2: 50 DTUs, 250 GB storage to General Purpose: Serverless, Gen5, 1 vCore, 16 GB storage for database. Everything does seem to run about twice as quick, but still not as quick as the old system. The SQL query I reproduced in my question runs in 1 second now. – gordon613 Sep 24 '19 at 14:44