有5张表
test是原始表,表结构如下
SELECT TOP 10 * FROM test
其他四张表都是复制test表
现做如下字段添加修改
--test_id添加自增ID
ALTER TABLE dbo.test_id ADD
ID int NOT NULL IDENTITY (1, 1)
SELECT TOP 10 * FROM test_id
--test_guid添加guidid,默认值newid
ALTER TABLE dbo.test_guid ADD
guidid [uniqueidentifier] NOT NULL DEFAULT (newid())
SELECT TOP 10 * FROM test_guid
--test_id_guid添加自增ID
ALTER TABLE dbo.test_id_guid ADD
ID int NOT NULL IDENTITY (1, 1)
--test_id_guid添加guidid,默认值newid
ALTER TABLE dbo.test_id_guid ADD
guidid [uniqueidentifier] NOT NULL DEFAULT (newid())
SELECT TOP 10 * FROM test_id_guid
--test_id_guid1添加自增ID
ALTER TABLE dbo.test_id_guid1 ADD
ID int NOT NULL IDENTITY (1, 1)
--test_id_guid1添加guidid,默认值newid
ALTER TABLE dbo.test_id_guid1 ADD
guidid [uniqueidentifier] NOT NULL DEFAULT (newid())
--test_id_guid1添加guidid1,默认值newid
ALTER TABLE dbo.test_id_guid1 ADD
guidid1 [uniqueidentifier] NOT NULL DEFAULT (newid())
SELECT TOP 10 * FROM test_id_guid1
------------------------------------------------------------------------------------------------
以上表都没有索引
------------------------------------------------------------------------------------------------
I.
a.top 查询
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
--清空缓存
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESESSIONCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
SET NOCOUNT on
DECLARE @top INT
SET @top=1
CREATE TABLE #result
(
状态 varchar(20),
[test(毫秒)] INT,
[test_id(毫秒)] INT,
[test_guid(毫秒)] INT,
[test_id_guid(毫秒)] INT,
[test_id_guid1(毫秒)] INT
)
DECLARE @start INT
DECLARE @end INT
SET @start=1
SET @end=20
WHILE (@start<=@end)
BEGIN --循环开始
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
declare @d datetime
set @d = getdate()
SELECT TOP (@top)[费用_ID],
[水表编码],
[水表口径],
[水表用途],
[收费方式],
[水表状态],
[本期抄表日],
[上期读数],
[本期读数],
[实抄水量]
FROM test
declare @result int
select @result = datediff(ms, @d, getdate())
declare @d1 datetime
set @d1 = getdate()
SELECT TOP (@top)[费用_ID],
[水表编码],
[水表口径],
[水表用途],
[收费方式],
[水表状态],
[本期抄表日],
[上期读数],
[本期读数],
[实抄水量]
FROM test_id
declare @result1 int
select @result1 = datediff(ms, @d1, getdate())
declare @d2 datetime
set @d2 = getdate()
SELECT TOP (@top)[费用_ID],
[水表编码],
[水表口径],
[水表用途],
[收费方式],
[水表状态],
[本期抄表日],
[上期读数],
[本期读数],
[实抄水量]
FROM test_guid
declare @result2 int
select @result2 = datediff(ms, @d2, getdate())
declare @d3 datetime
set @d3 = getdate()
SELECT TOP (@top)[费用_ID],
[水表编码],
[水表口径],
[水表用途],
[收费方式],
[水表状态],
[本期抄表日],
[上期读数],
[本期读数],
[实抄水量]
FROM test_id_guid
declare @result3 int
select @result3 = datediff(ms, @d3, getdate())
declare @d4 datetime
set @d4 = getdate()
SELECT TOP (@top)[费用_ID],
[水表编码],
[水表口径],
[水表用途],
[收费方式],
[水表状态],
[本期抄表日],
[上期读数],
[本期读数],
[实抄水量]
FROM test_id_guid1
declare @result4 int
select @result4 = datediff(ms, @d4, getdate())
INSERT INTO #result
SELECT '正常',
@result AS 'test(毫秒)',
@result1 'test_id(毫秒)',
@result2 'test_guid(毫秒)',
@result3 'test_id_guid(毫秒)',
@result4 'test_id_guid1(毫秒)'
SET @start=@start+1
END --循环结束
SELECT * FROM #result
UNION ALL
SELECT '最小值',
min([test(毫秒)]),
min([test_id(毫秒)]),
min([test_guid(毫秒)]),
min([test_id_guid(毫秒)]),
min([test_id_guid1(毫秒)])
FROM #result
UNION ALL
SELECT '平均值',
AVG([test(毫秒)]),
AVG([test_id(毫秒)]),
AVG([test_guid(毫秒)]),
AVG([test_id_guid(毫秒)]),
AVG([test_id_guid1(毫秒)])
FROM #result
UNION ALL
SELECT '最大值',
max([test(毫秒)]),
max([test_id(毫秒)]),
max([test_guid(毫秒)]),
max([test_id_guid(毫秒)]),
max([test_id_guid1(毫秒)])
FROM #result
--DROP TABLE #result
------------------------------------------------------------------------------------------------
1.@TOP=1,@end=20
2.@TOP=1,@end=100
SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_guid(毫秒)]
SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_id_guid(毫秒)]
SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_id_guid1(毫秒)]
SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_guid(毫秒)]
3.@TOP=10000,@end=50
--添加索引CREATE INDEX test1 ON test_id(id)CREATE INDEX test2 ON test_guid(guidid)CREATE INDEX test3 ON test_id_guid(id)CREATE INDEX test4 ON test_id_guid(guidid)CREATE INDEX test5 ON test_id_guid1(id)CREATE INDEX test6 ON test_id_guid1(guidid)CREATE INDEX test7 ON test_id_guid1(guidid1)
1.@top=1
2.@top=2000
3.@top=50000
