第一步,创建表
- CREATE TABLE Customers (
- CustomerID INT,
- CustomerNumber CHAR(4),
- CustomerName VARCHAR(50),
- CustomerCity VARCHAR(20) )
- GO
第二步,编写语句
插入语句
/*使用CTE递归循环插入 运用CTE递归插入,速度较快*/
TRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @d Datetime
SET @d=getdate();
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,'0000','Customer 0',cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,'0000','Customer 0',
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 10000/*CTE批量递归500万数据量 用时一般大概3分钟 因个人电脑而已5000000*/
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)
select [500万数据量插入完毕,共花费时间(毫秒)]=datediff(ms,@d,getdate())
SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO
查询语句row_number
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY customerid desc) AS 'RowNumber', * FROM Customers
) AS UserInfo
WHERE UserInfo.customerid BETWEEN 5001 AND 5020
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
查询语句fetchnext
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
select * from Customers order by customerid desc offset 5000 rows fetch next 20 rows only
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
第三步,查询对比
1万条数据量查询对比
逻辑读取次数和执行时间fetchnext 获得一分
另外分别测试了读取开头100条,和末尾20条的情况。末尾20条查询亮着性能详尽,fetchnext还是相对快一点。
50万条数据量查询对比
逻辑读取次数和执行时间fetchnext 获得一分
另外分别测试了读取开头100条,和末尾20条的情况。末尾20条查询亮着性能详尽,fetchnext还是相对快一点。
500万条数据量查询对比
逻辑读取次数和执行时间fetchnext 获得一分,fetchnext 逻辑读取和执行时间性能是rownumber的一倍
1亿条数据量查询对比(final battle)
再多不测了,哇咔咔。
下面是废话,哈哈哈,人生么,务实也要务虚。
500万数据插入用了3分钟。
5000万数据,推测30分钟。
1亿额,难道用1个小时么刚刚过去了三分钟,还在查询,那就让子弹再飞一会。
刚刚去看了看风景,思考了下人生。回来一看,额,还有个sql在执行,还没完,数据量确实有点大了。我还是暂停了语句吧
打算顺便测试下 count(1) 和(*)到底有啥区别,目前来看,500万以下的数据量查询效果一样,没啥区别
5千万条数据量查询对比(final battle)
吃完午饭眯会,继续码字,1亿条太多了,电脑卡死
逻辑读取次数和执行时间fetchnext 获得一分,此时已经是10倍的效率差距了
同时验证了下count(*)和count(1)的区别
对比结果
fetch性能高
简单数据下:count(1)和count(*)性能么有什么大的差别。推测,复杂数据表1比*性能高