SQL Azure on cloud run query slower than SQL Server on premises, what can i do to improve?

2
votes

I need to retrieve a range of record which I should skip and take. However, I got result on both running on local SQL Server and SQL Azure but the time is hugh difference. Both database have the same indexes.

For example, I have a table with 7 million records and I have query like this: SELECT TOP(100) a.Time, a.SiteID FROM (SELECT a.Time, a.SiteID, row_number() OVER (ORDER BY a.Time DESC) AS [row_number] FROM [Table] AS a WHERE a.SiteID = 1111) AS a WHERE row_number > 632900

In SQL Azure : It give result in 30 seconds to 1 mins. In SQL Server on premises : It give result in nearly instance time.

What can I do to improve the execution time on SQL Azure?

Regards Grace

2
azure-sql-database
what edition of sql azure you are using? Please take a look at the performance tiers @ msdn.microsoft.com/en-us/library/azure/dn741336.aspx Is the query plan looking the same for both Azure and on premise? - Sirisha Chamarthi
In SQL Azure, I'm using "Standard" and the Performance Level is S0 (10DTUS). I check the execution plan, both Cloud and On-premises have same 99% on the Key Lookup (Clustered) of [Table].[PK_Table_1], this is the primary key of [Table] column "ID" which is a unique auto-increment column. - Grace Chan
That explains the slowness as the resources you getting in your on prem must be much higher than what you get in Azure for Standard S0 tier. A few things you can try are compressing the table to reduce the number of pages to scan (this can increase CPU slightly). Cache the results in your application instead of querying every time. Also you may want to scale up to compare the performance gains .. if your database is not already in V12 you may want to upgrade to get better performance as it uses SSD - Sirisha Chamarthi

2 Answers

1
votes

Depending on the plan this query requires reading at least 632900 records. If there is no suitable index it might require reading and sorting the entire table.

SQL Azure is extremely memory limited. This often pushes work loads out of an in-memory state into requiring disk IO. IO is easily 100x slower than memory, especially using the severely throttled IO on Azure.

Optimize the query to require less buffer pool memory. Probably, you should create an appropriate index. Also consider using a more efficient paging strategy. For example instead of seeking by row number you could seek by the last a.Time value processed. That way the required buffer pool memory is tiny because the table access starts at just the right position.

0
votes

You can try to re-write your query using OFFSET FETCH. Make sure you have an index in place that matches the columns in the ORDER BY. SQL Server will then use an optimized TOP operator to do the pagination. Check this post for some more considerations on OFFSET FETCH.