CODEWITHSUNDEEP
azure-sql-database
Grace Chan
Grace Chan

Reputation: 21

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

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

Upvotes: 2

Views: 2025

Answers (2)

Jan Engelsberg
Jan Engelsberg

Reputation: 1087

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.

Upvotes: 0

usr
usr

Reputation: 171178

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.

Upvotes: 1

Related Questions