高效的SQLSERVER分頁查詢

時間：2018-12-06 10:38:01

關(guān)鍵字： sqlserver 分頁查詢

手機看文章

掃描二維碼
隨時隨地手機看文章

[導(dǎo)讀]Sqlserver數(shù)據(jù)庫分頁查詢一直是Sqlserver的短板，閑來無事，想出幾種方法，假設(shè)有表ARTICLE,字段ID、YEAR...(其他省略)，數(shù)據(jù)53210條(客戶真實數(shù)據(jù)，量不大)，分頁查詢

Sqlserver數(shù)據(jù)庫分頁查詢一直是Sqlserver的短板，閑來無事，想出幾種方法，假設(shè)有表ARTICLE,字段ID、YEAR...(其他省略)，數(shù)據(jù)53210條(客戶真實數(shù)據(jù)，量不大)，分頁查詢每頁30條，查詢第1500頁（即第45001-45030條數(shù)據(jù)），字段ID聚集索引，YEAR無索引，Sqlserver版本：2008R2

第一種方案、最簡單、普通的方法：

代碼如下:

SELECT?TOP?30?*?FROM?ARTICLE?WHERE?ID?NOT?IN?(SELECT?TOP?45000?ID?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?ORDER?BY?YEAR?DESC,ID?DESC

第二種方案：

代碼如下:

SELECT?*?FROM?(SELECT?TOP?30?*?FROM?(SELECT?TOP?45030?*?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?f?ORDER?BY?f.YEAR?ASC,?f.ID?DESC)?s?ORDER?BY?s.YEAR?DESC,s.ID?DESC

平均查詢100次所需時間：138S

第三種方案：

代碼如下:

SELECT?*?FROM?ARTICLE?w1,???
(??
????SELECT?TOP?30?ID?FROM???
????(??
????????SELECT?TOP?50030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??
????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??
)?w2?WHERE?w1.ID?=?w2.ID?ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC

平均查詢100次所需時間：21S

第四種方案：

代碼如下:

SELECT?*?FROM?ARTICLE?w1???
????WHERE?ID?in???
????????(??
????????????SELECT?top?30?ID?FROM???
????????????(??
????????????????SELECT?top?45030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??
????????????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??
????????)???
????ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC

平均查詢100次所需時間：20S

第五種方案：

代碼如下:

SELECT?w2.n,?w1.*?FROM?ARTICLE?w1,?(SELECT?TOP?50030?row_number()?OVER?(ORDER?BY?YEAR?DESC,?ID?DESC)?n,?ID?FROM?ARTICLE?)?w2?WHERE?w1.ID?=?w2.ID?AND?w2.n?>?50000?ORDER?BY?w2.n?ASC

平均查詢100次所需時間：15S

查詢第1000-1030條記錄

第一種方案：

代碼如下:

SELECT?TOP?30?*?FROM?ARTICLE?WHERE?ID?NOT?IN(SELECT?TOP?1000?ID?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?ORDER?BY?YEAR?DESC,ID?DESC

平均查詢100次所需時間：80s

第二種方案：

代碼如下:

SELECT?*?FROM??(?　　SELECT?TOP?30?*?FROM?(SELECT?TOP?1030?*?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC)?f?ORDER?BY?f.YEAR?ASC,?f.ID?DESC)?s?ORDER?BY?s.YEAR?DESC,s.ID?DESC

平均查詢100次所需時間：30S

第三種方案：

代碼如下:

SELECT?*?FROM?ARTICLE?w1,???
(??
????SELECT?TOP?30?ID?FROM???
????(??
????????SELECT?TOP?1030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??
????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??
)?w2?WHERE?w1.ID?=?w2.ID?ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC

平均查詢100次所需時間：12S

第四種方案：

代碼如下:

SELECT?*?FROM?ARTICLE?w1???
????WHERE?ID?in???
????????(??
????????????SELECT?top?30?ID?FROM???
????????????(??
????????????????SELECT?top?1030?ID,?YEAR?FROM?ARTICLE?ORDER?BY?YEAR?DESC,?ID?DESC??
????????????)?w?ORDER?BY?w.YEAR?ASC,?w.ID?ASC??
????????)???
????ORDER?BY?w1.YEAR?DESC,?w1.ID?DESC

平均查詢100次所需時間：13S

第五種方案：

代碼如下:

SELECT?w2.n,?w1.*?FROM?ARTICLE?w1,(?　　SELECT?TOP?1030?row_number()?OVER?(ORDER?BY?YEAR?DESC,?ID?DESC)?n,?ID?FROM?ARTICLE)?w2?WHERE?w1.ID?=?w2.ID?AND?w2.n?>?1000?ORDER?BY?w2.n?ASC

平均查詢100次所需時間：14S

???? 由此可見在查詢頁數(shù)靠前時，效率3>4>5>2>1，頁碼靠后時5>4>3>1>2，再根據(jù)用戶習(xí)慣，一般用戶的檢索只看最前面幾頁，因此選擇3 4 5方案均可，若綜合考慮方案5是最好的選擇，但是要注意SQL2000不支持row_number()函數(shù)，由于時間和條件的限制沒有做更深入、范圍更廣的測試，有興趣的可以仔細研究下。

以下是根據(jù)第四種方案編寫的一個分頁存儲過程：

代碼如下:

[sql]?view plaincopyif?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[sys_Page_v2]')?and?OBJECTPROPERTY(id,?N'IsProcedure')?=?1)?? drop?procedure?[dbo].[sys_Page_v2]?? GO?? CREATE?PROCEDURE?[dbo].[sys_Page_v2]?? @PCount?int?output,????--總頁數(shù)輸出?? @RCount?int?output,????--總記錄數(shù)輸出?? @sys_Table?nvarchar(100),????--查詢表名?? @sys_Key?varchar(50),????????--主鍵?? @sys_Fields?nvarchar(500),????--查詢字段?? @sys_Where?nvarchar(3000),????--查詢條件?? @sys_Order?nvarchar(100),????--排序字段?? @sys_Begin?int,????????--開始位置?? @sys_PageIndex?int,????????--當(dāng)前頁數(shù)?? @sys_PageSize?int????????--頁大小?? AS?? SET?NOCOUNT?ON?? SET?ANSI_WARNINGS?ON?? IF?@sys_PageSize?<?0?OR?@sys_PageIndex?<?0?? BEGIN?????????? RETURN?? END?? DECLARE?@new_where1?NVARCHAR(3000)?? DECLARE?@new_order1?NVARCHAR(100)?? DECLARE?@new_order2?NVARCHAR(100)?? DECLARE?@Sql?NVARCHAR(4000)?? DECLARE?@SqlCount?NVARCHAR(4000)?? DECLARE?@Top?int?? if(@sys_Begin?<=0)?? ????set?@sys_Begin=0?? else?? ????set?@sys_Begin=@sys_Begin-1?? IF?ISNULL(@sys_Where,'')?=?''?? ????SET?@new_where1?=?'?'?? ELSE?? ????SET?@new_where1?=?'?WHERE?'?+?@sys_Where?? IF?ISNULL(@sys_Order,'')?<>?''??? BEGIN?? ????SET?@new_order1?=?'?ORDER?BY?'?+?Replace(@sys_Order,'desc','')?? ????SET?@new_order1?=?Replace(@new_order1,'asc','desc')?? ????SET?@new_order2?=?'?ORDER?BY?'?+?@sys_Order?? END?? ELSE?? BEGIN?? ????SET?@new_order1?=?'?ORDER?BY?ID?DESC'?? ????SET?@new_order2?=?'?ORDER?BY?ID?ASC'?? END?? SET?@SqlCount?=?'SELECT?@RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'?? ????????????+?CAST(@sys_PageSize?AS?NVARCHAR)+')?FROM?'?+?@sys_Table?+?@new_where1?? EXEC?SP_EXECUTESQL?@SqlCount,N'@RCount?INT?OUTPUT,@PCount?INT?OUTPUT',?? ???????????????@RCount?OUTPUT,@PCount?OUTPUT?? IF?@sys_PageIndex?>?CEILING((@RCount+0.0)/@sys_PageSize)????--如果輸入的當(dāng)前頁數(shù)大于實際總頁數(shù),則把實際總頁數(shù)賦值給當(dāng)前頁數(shù)?? BEGIN?? ????SET?@sys_PageIndex?=??CEILING((@RCount+0.0)/@sys_PageSize)?? END?? set?@sql?=?'select?'+?@sys_fields?+'?from?'?+?@sys_Table?+?'?w1?'?? ????+?'?where?'+?@sys_Key?+'?in?('?? ????????+'select?top?'+?ltrim(str(@sys_PageSize))?+'?'?+?@sys_Key?+?'?from?'?? ????????+'('?? ????????????+'select?top?'?+?ltrim(STR(@sys_PageSize?*?@sys_PageIndex?+?@sys_Begin))?+?'?'?+?@sys_Key?+?'?FROM?'?? ????????+?@sys_Table?+?@new_where1?+?@new_order2??? ????????+')?w?'?+?@new_order1?? ????+')?'?+?@new_order2?? print(@sql)?? Exec(@sql)?? GO