CREATE PROCEDURE SP_Pagination
@TableNames VARCHAR(200), --表名，可以是多個表，但不能用別名
@PrimaryKey VARCHAR(100), --主鍵，可以為空，但@Order為空時該值不能為空
@Fields VARCHAR(200) = '*', --要取出的欄位，可以是多個表的欄位，可以為空，為空表示select *
@PageSize int = 30, --每頁記錄數
@CurrentPage int = 1, --當前頁，0表示第1頁
@IsCount int = 0,
@Filter VARCHAR(2000) = '', --條件，可以為空，不用填 where
@Group VARCHAR(200) = '', --分組依據，可以為空，不用填 group by
@Order VARCHAR(200) = '' --排序，可以為空，為空默認按主鍵升序排列，不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
DECLARE @sql nvarchar(4000)
DECLARE @iPageCount int
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'Where 1=1'
ELSE
SET @Filter = 'Where ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
IF @IsCount != 0
BEGIN
SET @Sql = 'SELECT @iPageCount=COUNT(1) FROM ' + @TableNames + ' ' + @Filter + ''
EXEC sp_executesql @Sql,N'@iPageCount int OUTPUT',@iPageCount OUTPUT--計算總記錄數
END
IF @Order <> ''
BEGIN
DECLARE @pos1 INT, @pos2 INT
SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
IF CHARINDEX(' DESC', @Order) > 0
IF CHARINDEX(' ASC', @Order) > 0
BEGIN
IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>='
SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos1 = CHARINDEX(',', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = '>='
END
DECLARE @type varchar(50)
DECLARE @prec int
Select @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
PRINT @TopRows
PRINT @Operator
EXEC('
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
Select @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
Select ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order + '
')
If @@Error = 0
BEGIN
RETURN @iPageCount ---返回記錄總數
END
END
GO
2. 高分求SQL分頁的存儲過程
ASP.Net中的DataGrid有內置分頁功能,
但是它的默認的分頁方式效率是很低的,特別是在數據量很大的時候,用它內置的分頁功能幾乎是不可能的事,因為它會把所有的數據從資料庫讀出來再進行分頁,
這種只選取了一小部分而丟掉大部分的方法是不可去取的.
在最進的一個項目中因為一個管理頁面要管理的數據量非常大,所以必須分頁顯示,並且不能用DataGrid的內置分頁功能,於是自己實現分頁.
下面介紹一下我在項目中用到的分頁方法.
當然顯示控制項還是用DataGrid的,
因為數據綁定很方便^_^.
要保證不傳輸冗餘的數據,那麼必須在資料庫中數據讀取時實現分頁,
資料庫的分頁操作可以放在存儲過程中.
看了CSDN的一篇Blog中講了一個百萬級數據分頁的存儲過程的實現(
http://blog.csdn.net/wellknow/posts/55167.aspx
，他的這個方法可以根據不同情況進行適當的優化),
根據他的方法,這里實現一個簡單的SQL語句來實現這里分頁需要的存儲過程。
create
procere
ListProct
(
@PageIndex
int,
--
分頁後需要頁的序號
@PageSize
int,
--
一頁的大小
@ConditionSQL
–
查詢條件的SQL語句
)
AS
…
具體代碼就不寫了(可以參考上面的鏈接).
具體的SQL語句如下:
Select
TOP
100
*
FROM
(select
*
from
proct
where
proctid<200000)
T
Where
T.proctid
NOT
IN
(Select
TOP
900
proctid
FROM
(select
proctid
from
proct
where
proctid<200000)
T1
orDER
BY
T1.proctid
asc)
orDER
BY
proctid
asc
這條語句的
從總的商品(30萬)中取出proctid<200000(共20萬),再按每頁100的大小分頁,然後取出第10頁.
Public
DataTable
ListProct(int
pageIndex,
int
pageSize)
{
//ADO.net從資料庫中取出數據的代碼就略過^_^.
}
用上面的存儲過程讀出的數據在DataGrid裡面分頁,
必須把DataGrid的AllowPaging和AllowCustomPaging設置為true
protected
System.Web.UI.WebControls.DataGrid
ProctGrid;
ProctGrid.AllowPaging
=
true;
ProctGrid.AllowCustomPaging
=
true;
然後在設置要顯示的一頁的大小
ProctGrid.PageSize
=
100;
//
在顯示的時候依據實際的數據顯示。
設置一頁大小後,如果要讓DataGrid實際分出頁數來,還必須設置
ProctGrid.VirtualItemCount
=
GetProctCount()
;
//
GetProctCount()
的功能是獲取滿足條件的產品數目,
這里的條件就是proctid<200000.
設置這項屬性後,那麼這個DataGrid的頁數就是
VirtualItemCount/PageSize,
也就是PageCount的值.
不能對PageCount直接賦值,因為他是只讀的屬性.
3. mysql 百萬級數據，怎麼分頁
一般剛開始學SQL的時候，會這樣寫 復制代碼代碼如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在數據達到百萬級的時候，這樣寫會慢死 復制代碼代碼如下: SELECT * FROM table ORDER BY id LIMIT 1000000,
4. oracle資料庫，搜索百萬級別數據分頁優化問題
oracle count 百萬級 分頁查詢記錄總數、總條數優化
Oracle count 百萬級 查詢記錄總數、總條數優化
最近做一個項目時，做分頁時，發現分頁查詢速度很慢，分頁我做的是兩次查詢，一次是查詢總數，一次是查詢分頁結果
[java] view plain
/** 查詢總記錄數 **/
SELECT
COUNT(id)
FROM
USER
order by
id
/** 查詢結果集 **/
select
*
from
( select
row_.*,
rownum rownum_
from
( select
id ,
user_number,
user_name,
user_password,
sex,
Registered_time,
last_login_time,
post
from
USER u
order by
u.id) row_
where
rownum <= ?
)
where
rownum_ > ?
user表中的記錄是128萬多條，這個是沒有查詢條件時的查詢，也就是用戶剛剛進入模塊時的查詢，發現查詢時間是2566ms~2152ms之間，單獨執行每條語句，發現第一條的執行時間在2000ms以上，在PL/SQL中執行的結果也證實了我的判斷。所以要對select count語句進行優化。
在網上找了很多優化方案，大多不盡人意，（分表的方式聽上去不錯，不過由於單表是歷史原因，這里就不作考慮）。最後找到一個比較令人滿意的答。就是在語句中加入 /*+ROWID(USER)*/或者/*+ INDEX(USER ID) */ 來提高查詢效果。
聽說這個就是強制使用索引統計結果？如果有哪位大蝦能把原理詳細告訴我，請來多多指點！
[java] view plain
SELECT /*+ROWID(USER)*/ count(*) FROM USER t
或者
SELECT /*+ INDEX(USER ID) */ count(*) FROM USER t
使用後，單條統計總數的查詢在800ms左右，分頁查詢結果基本在900ms~950ms之間，基本在一秒之內，達到了當初設計需求。
當然，這個是沒有加查詢條件的，當把查詢條件加入後，不管前面加不加強制索引，結果時間都在2000ms之間，所以，如果要進行有條件的查詢，就要在where條件中進行優化。特別注意條件欄位查詢前後順序。
5. MYSQL百萬條數據下標簽、文章的分頁查詢排序的高效方法有哪些
文章：content表儲存：content_id[自增id]，content[內容]，dateline[發布時間戳]，ispublic[是否發布]
關系：tag_content表儲存：tag_id[標簽id]，content_id[文章id]
網站前段，訪問標簽頁面，需要查詢出這個標簽下的所有文章，需要篩選是否發布，需要按照時間排序。
通過一般方法left join + 聯合索引 速度仍然十分不理想。
請教在百萬級文章，萬級標簽，千萬級關系下。如果高效的實現某一標簽下文章的分頁排序查詢。（分頁可只顯示前100頁，不用全部可訪問）
以前數據量沒這么大，聯合索引+left join 沒問題。在大數據量的時候，速度瓶頸在於按照時間戳、瀏覽量等排序。
6. sql2008資料庫，九百萬條數據，如何快速查詢
根據你說的需求：
"可程序中需要查詢每行的所有數據"、「查詢全部九百萬條數據」
-------------------------------
這樣的需求跟索引沒有關系了（因為已經肯定是走全表掃描的了），要提高效率的辦法就是：1、提高硬碟的io速度；2、增加內存以使sql server有更多的緩存。
另外，你程序不要一次性取那麼多數據返回，這樣會拖死的，建議你考慮變換下處理邏輯（如：分批取回--可以根據id列值進行分批；將數據直接在服務端存成文本再傳回本地處理）。
"而是需要根據這些數據逐一進行其他功能的操作，這款程序是不聯網的"
--------------------------------
就算這樣的話,也不能一次性把9百萬數據一次性取回,如果你非要取出來再操作的話，那你得考慮分批去取。或者你把處理邏輯寫在存儲過程，然後由SQL SERVER本身去完成邏輯處理。總之，不管怎樣，你也是得優化你現在的處理邏輯（現有處理邏輯我覺得不合理，效率很低下）。
「真正部署到電力網的伺服器上速度會不會提高呢」
------------------------
伺服器當然比你本地要快得多啦，硬體配置根本就不同一個級別,但不管怎樣，還是建議你參考上面的建議優化你的處理邏輯才行，否則，你系統的效率將很低。
7. SQL語句自帶的分頁，就是最後between 1 and 20這樣的。現在要導出到excel，但因為記錄比較多，百萬級的
select Field1,Field2 from table with(nolock)再加個索引就會快一些，
不想再查詢的話放到內存裡面，第二次再從內存裡面拿數據
8. sql server百萬級的數據，如何提高查詢效率
沒有太好的辦法，哪怕千萬級的，查詢也還是那麼查，
唯一能做的，就是盡量增加限制條件，篩選數據，節約時間和資源。
9. 百萬級數據分頁 C# SQL 2K5
百萬級數據檢索存儲過程需要很精潔 盡量不要在select部分、where部分做判斷； 考慮僅一次的order by。
在存儲過程里做分頁是個不錯的選擇。排序好的數據可以用between and達到分頁效果。