i need retrieve range of record should skip , take. however, got result on both running on local sql server , sql azure time hugh difference. both database have same indexes.
for example, have table 7 million records , have query this: select top(100) a.time, a.siteid (select a.time, a.siteid, row_number() on (order a.time desc) [row_number] [table] a.siteid = 1111) row_number > 632900
in sql azure : give result in 30 seconds 1 mins. in sql server on premises : give result in instance time.
what can improve execution time on sql azure?
regards grace
depending on plan query requires reading @ least 632900 records. if there no suitable index might require reading , sorting entire table.
sql azure extremely memory limited. pushes work loads out of in-memory state requiring disk io. io 100x slower memory, using severely throttled io on azure.
optimize query require less buffer pool memory. probably, should create appropriate index. consider using more efficient paging strategy. example instead of seeking row number seek last a.time value processed. way required buffer pool memory tiny because table access starts @ right position.
Comments
Post a Comment