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:
- Checked to see if there any spikes in the dashboard chart for the database. There are no significant ones - DTU's are maximum 25%
- I added
Query Performance Insight / Recommendations / Automate
to automatically tune the database. This did speed it up somewhat but by no means enough. - 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
- 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