Azure - Cross Database Queries

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  March 23, 2016 at 4:40 am

  #309340

  Hi everyone

  We have moved one of our systems to Azure. The problem is that there is a large product database, about 30 million products, that is independant of the system.

  In our on-site enviroment, we use a table valued function to select from the other database and join the product details with the transactional information. Now on Azure, we have implemented the EXTERNAL TABLE options, but the response time is horrid. I have now made scalar functions to retrieve each field for each product, the optimizer picks this up and it works quicker, but it is still way too slow.

  Here are some basic examples

  Slowest:

  SELECT O.Id, O.UtcDateCreated, PD.Name, PD.Supplier

  FROM [Order] O

  OUTER APPLY dbo.ufnProductDetails(O.ProductId) PD

  Slower

  SELECT O.Id, O.UtcDateCreated, dbo.ufnProductName(O.ProductId) Name, dbo.ufnProductSupplier(O.ProductId) Supplier

  FROM [Order] O

  Slow

  SELECT O.Id, O.UtcDateCreated, PD.Name, PD.Supplier

  FROM [Order] O

  LEFT JOIN ProductDetail PD

  ON O.ProductId = PD.Id

  Has anyone worked with something similair? What is the best way to handle these kind of cross-db queries in Azure?

  Both databases are on the P1 plans.

  Thanks.

• Torsten Grabs

  Valued Member

  Points: 61

  More actions

  March 23, 2016 at 10:37 am

  #1867165

  Currently, the minimum roundtrip time to access a remote database through an external table or external source in Azure SQL DB is around 100msec. Looking at your queries I worry that they pull many rows from the remote table and only the join then reduces the number of rows. You can verify that by checking if there is a predicate that is pushed to the remote table when looking into the remote query operator in your query plan.

  I am not sure if you need all products from the remote table. If there is a way to reduce this to a list of product IDs consider putting them into an IN list in the WHERE clause. That will allow the predicate to be pushed to the remote side and will reduce the number of rows before they are sent over the wire.

  Hope this helps.

  Thanks,

  Torsten

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  March 24, 2016 at 12:08 am

  #1867324

  Thanks Torsten

  I have checked the query plans and as you say, the join seems to bring back all the products first and them filter them.

  That is why I did the user functions as the query have parameters, but I wasn't aware of the 100ms roundtrip minimum.

  We have decided to move the product details over to the main database. It's just too much of a performance sacrifice for now.

  Thank you for your input.

  Kind Regards