数据分页性能比较 SQL SERVER,商业智能,BI,DBA - 微软BI开拓者

微软BI开拓者数据库专区 SQL Server开发数据分页性能比较

1 / 1 页

跳转页

查看:803

[技术文档] 数据分页性能比较

个人空间
组别:版主
性别:
来自:北京
积分:169
帖子:288
注册: 2007-04-10

2009-07-02 11:59 | 只看楼主

树型| 收藏| 小中大 1

数据分页性能比较

--数据分页性能比较方法4 IO最小
DECLARE @startRow INT ; SET @startrow = 21
SET STATISTICS IO ON
SET STATISTICS TIME ON
--1
SELECT COUNT(*) AS TotRows
FROM [INFORMATION_SCHEMA].columns
;WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 19
ORDER BY seq
--2
CREATE TABLE #pgeResults(
id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
table_name VARCHAR(255),
column_name VARCHAR(255)
)
INSERT INTO #pgeResults(Table_name, column_name)
SELECT table_name, column_name
FROM [INFORMATION_SCHEMA].columns
ORDER BY [table_name], [column_name]
SELECT @@ROWCOUNT AS TotRows
SELECT Table_Name, Column_Name
FROM #pgeResults
WHERE id between @startrow and @startrow + 19
ORDER BY id
DROP TABLE #pgeResults
--3
;WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
COUNT(*) OVER() AS totrows
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 19
ORDER BY seq
--4 IO最少
;WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 19
ORDER BY seq

SQL交流QQ群:29778717
由于以上群已满，请加新群100485723
(人不多，希望支持)

fuxiaoyang13 个人空间组别:中级会员性别: 来自: 积分:262 帖子:60 注册: 2008-07-12	2009-07-11 15:30 \| 树型\| 收藏\| 小中大 2 回复：数据分页性能比较高手！！！！

<<上一主题|下一主题>>

1 / 1 页

跳转页

微软BI开拓者数据库专区SQL Server开发 数据分页性能比较

[技术文档] 数据分页性能比较

数据分页性能比较

回复：数据分页性能比较

微软BI开拓者数据库专区 SQL Server开发数据分页性能比较