Azure elastic query performance

3
votes

We have two applications that use separate databases on SQL Server 2012, however we have several stored procedures that get data from the other using INNER JOINs (7 joins in total). We are looking to see if it is possible to move to Azure and have set up a test using our existing databases, with external tables to get the data from the other database.

The problem is that the performance of these queries goes from 1-15 seconds on our server, to 4+ minutes on Azure. We have tried moving the tables to the same database and it did fix the speed problem, although it isn't ideal to move all the tables over to the same DB.

For the purpose of our test, we are using Azure Standard elastic pool with 50 DTUs.

1
azureazure-sql-database
what does query performance pane show in portal..Also i think there is a managed instance coming up in next month end(same like on prem sql),but with all features of sqlserver..So i think you may want to test that as well – TheGameiswar
dcac.co/syndication/… – TheGameiswar

1 Answers

3
votes

Cross database queries show good performance when the remote tables are not big. When remote tables are big, this article shows you how to perform joins remotely using table variables and improve performance.

This other article shows you also how to push parameterized operations to remote databases and improve performance.

Hope this helps.