Search code examples
azureazure-sql-databasespatial-query

SQL Azure STDistance performance

I have a big performance problem with STDistance function on SQL Azure.

I'm testing the same query

SELECT Coordinate
FROM MyTable
WHERE Coordinate.STDistance(@Center) < 50000

on a SQL Azure database (Standard) and on my local machine database. Same database, same indexes (a spatial index on Coordinate), same data (400k rows) but I got two different execution time.

The query takes less than 1 second in my local workstation and more or less 9 seconds on SQL Azure.

Anybody else has the same problem?

Federico

Solution

  • You can try following things to reduce network latency:

    • Select the data center closest to majority of your users

    • Co-Locate your DB with your application if your application is in Windows Azure as well

    • Minimize network round trips in your app

    I would highly recommend you read this Azure SQL DB Perf guidance.

    In addition to that, please check the existing service tier of your database and see if the performance is capping out. In that case, you might want to upgrade the service tier of your DB. If you would like to monitor the performance and adjust the performance levels, please use this link.

    Thanks

    Silvia Doomra