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.
我需要检索一系列我应该跳过并记录的记录。但是,我在本地SQL Server和SQL Azure上都运行了结果,但时间差异很大。两个数据库都具有相同的索引。
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
例如,我有一个包含700万条记录的表,我有这样的查询:SELECT TOP(100)a.Time,a.SiteID FROM(SELECT a.Time,a.SiteID,row_number()OVER(ORDER BY a。时间DESC)AS [row_number] FROM [表] 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.
在SQL Azure中:它在30秒到1分钟内得到结果。在SQL Server内部:它几乎实例时给出结果。
What can I do to improve the execution time on SQL Azure?
我该怎么做才能缩短SQL Azure的执行时间?
Regards Grace
2 个解决方案
#1
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.
根据计划,此查询需要至少读取632900条记录。如果没有合适的索引,则可能需要读取和排序整个表。
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.
SQL Azure极其受内存限制。这通常会将工作负载从内存状态推送到需要磁盘IO的状态。 IO比内存慢100倍,尤其是在Azure上使用严格限制的IO。
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.
优化查询以减少缓冲池内存。您可能应该创建一个合适的索引。还要考虑使用更有效的分页策略。例如,不是按行号搜索,而是可以通过最后一个a.Time值进行搜索。这样,所需的缓冲池内存很小,因为表访问从恰当的位置开始。
#2
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.
您可以尝试使用OFFSET FETCH重新编写查询。确保您有一个与ORDER BY中的列匹配的索引。然后,SQL Server将使用优化的TOP运算符来执行分页。有关OFFSET FETCH的更多注意事项,请查看此文章。
#1
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.
根据计划,此查询需要至少读取632900条记录。如果没有合适的索引,则可能需要读取和排序整个表。
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.
SQL Azure极其受内存限制。这通常会将工作负载从内存状态推送到需要磁盘IO的状态。 IO比内存慢100倍,尤其是在Azure上使用严格限制的IO。
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.
优化查询以减少缓冲池内存。您可能应该创建一个合适的索引。还要考虑使用更有效的分页策略。例如,不是按行号搜索,而是可以通过最后一个a.Time值进行搜索。这样,所需的缓冲池内存很小,因为表访问从恰当的位置开始。
#2
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.
您可以尝试使用OFFSET FETCH重新编写查询。确保您有一个与ORDER BY中的列匹配的索引。然后,SQL Server将使用优化的TOP运算符来执行分页。有关OFFSET FETCH的更多注意事项,请查看此文章。