SQL Azure STDistance performance

Question

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

Answer 1

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

Answer 2

Query performance depends on various factors, one among them is your performance tier. Verify if you are hitting your resource limits (sys.resource_stats dmv from the master database)

Besides that there are a few other factors you can consider verifying: index fragmentation on azure, network latency, locking etc.

Application level caching helps avoid hitting the database if the query is repeating.

Answer 3

You may also have to investigate on which Service-Tier and Performance level is required based on the Benchmarks here, AzureSQL-ServierTier_PerformanceLevel