多年以前提高asp.net分頁查詢效率的一個執行個體

 

2004年資料庫查詢最佳化執行個體

情況：sqlserver 2000，資源表，記錄近30萬條。資源有一個整數的id欄位，自動增量，但是資源可以被刪除。所以，id並不連續。

用.net SqlDataAdapter進行分頁查詢，由於Fill（）方法的機制問題，使得在DataGrid裡面進行翻頁的時候延時非常大。資料庫

伺服器和web伺服器同在區域網路，延時大約有3-4秒。經過分析，對此進行最佳化。

首先得知資料庫的select count(id) from resource where id>n這樣的查詢速度非常快。所以利用這一點進行最佳化，以達到準確翻頁

 

假設，記錄的id是連續的，那麼如果每頁10條，那麼我們要得到第12頁，就需要第十一頁最後一個記錄的id。11 x 10 = 110，這個記錄id就是110。所以第12頁這麼得到，select top 10 * from resource where id>110，這條語句的查詢效率是非常高的。

但是如果id不連續，怎麼辦呢？

這裡進行估算。因為cpu進行數值運算的耗時比起資料庫查詢可以忽略不計。假設，id是連續的，那麼先統計一個數字。如果id連續，那麼第m頁需要的前一個記錄id可以這麼得到：(m – 1) * 10。所以先統計：

Select count(id) from resource where id<=(m-1)*10

假設統計得到的值為p，p<=(m-1)*10。如果p<(m-1)*10，那麼我麼可以這樣查詢：

Select top (((m-1)*10 – p) + 10) * from resource where id>(m-1)*10

查詢完畢，取最後的10條記錄就可以了。

這種方法對於實際的應用已經足夠了。因為，一個幾十萬條記錄的表一般是不經常刪除操作的。如果有極端情況，那麼可以連續估算2-3次，就可以比較準確了。

也就是(m-1)*10 – p很大的情況下，為了避免查詢出太多的記錄，進行二次估算。既然p太小了，再次假設id為(m-1)*10後的記錄號是連續的。我們需要的id是(m-1)*10 + ((m-1)*10 – p) = 2*(m-1)*10 –p。

查詢：

Select count(id) from resource where id<=2*(m-1)*10 –p

得到q，那麼我們真正需要的查詢是：

Select top (2*(m-1)*10 –p – q+ 10) * from resource where id>2*(m-1)*10 –p

取查詢結果的最後10條記錄。

這種最佳化可以用預存程序來實現，獲得更好的效果。

經過這樣的最佳化以後，查詢速度從原來的超過3秒多變成了瞬間，不足1秒，當時尚未使用預存程序。

這是在asp.net 1.1的時候，利用DataGrid綁定資料的時候解決sqlserver 2000查詢效率的一個執行個體。在此提供出來只是提供一個思路，方案也許有用，也許已經過時了。