GUID和自增ID的比较_id/guidid
------------------------------------------------------------------------------------------------
--清空缓存
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESESSIONCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
SET NOCOUNT on
CREATE TABLE #result
(
状态 varchar(20),
[test_guid(毫秒)/id] INT,
[test_guid(毫秒)/guidid] INT
)
DECLARE @start INT
DECLARE @end INT
SET @start=1
SET @end=10
WHILE (@start<=@end)
BEGIN --循环开始
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
declare @d3 datetime
set @d3 = getdate()
SELECT 费用_id, 水表编码, 水表口径, 水表用途, 收费方式, 水表状态, 本期抄表日, 上期读数, 本期读数, 实抄水量
from test_id_guid
WHERE id =800000
declare @result3 int
select @result3 = datediff(ms, @d3, getdate())
declare @d4 datetime
set @d4 = getdate()
SELECT 费用_id, 水表编码, 水表口径, 水表用途, 收费方式, 水表状态, 本期抄表日, 上期读数, 本期读数, 实抄水量
from test_id_guid
WHERE guidid ='2D269A7B-1D19-48B8-82AC-76D455BF83E2'
declare @result4 int
select @result4 = datediff(ms, @d4, getdate())
INSERT INTO #result
SELECT '正常',
@result3 'test_id_guid(毫秒)/id',
@result4 'test_id_guid(毫秒)/guidid'
SET @start=@start+1
END --循环结束
SELECT * FROM #result
UNION ALL
SELECT '最小值',
min([test_guid(毫秒)/id]) ,
min([test_guid(毫秒)/guidid])
FROM #result
UNION ALL
SELECT '平均值',
AVG([test_guid(毫秒)/id]) ,
AVG([test_guid(毫秒)/guidid])
FROM #result
UNION ALL
SELECT '最大值',
max([test_guid(毫秒)/id]) ,
max([test_guid(毫秒)/guidid])
FROM #result
--DROP TABLE #result
------------------------------------------------------------------------------------------------
1.没有索引
2.索引
CREATE INDEX test1 ON test_id_guid(id)
CREATE INDEX test2 ON test_id_guid(guidid)
CREATE CLUSTERED INDEX test1 ON test_id_guid(id)
CREATE INDEX test2 ON test_id_guid(guidid)
CREATE INDEX test3 ON test_id_guid(id,guidid)
CREATE INDEX test3 ON test_id_guid(guidid,id)
CREATE CLUSTERED INDEX test1 ON test_id_guid(id,guidid)
CREATE CLUSTERED INDEX test1 ON test_id_guid(guidid,id)
