GUID和自增ID的比较_delete
1.范围删除
------------------------------------------------------------------------------------------------
--清空缓存
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 @费用ID int
SET @费用ID =4406668
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=10
WHILE (@start<=@end)
BEGIN --循环开始
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
declare @d datetime
set @d = getdate()
delete
FROM test
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result int
select @result = datediff(ms, @d, getdate())
declare @d1 datetime
set @d1 = getdate()
delete
FROM test_ID
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result1 int
select @result1 = datediff(ms, @d1, getdate())
declare @d2 datetime
set @d2 = getdate()
delete
FROM test_guid
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result2 int
select @result2 = datediff(ms, @d2, getdate())
declare @d3 datetime
set @d3 = getdate()
delete
FROM test_id_guid
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result3 int
select @result3 = datediff(ms, @d3, getdate())
declare @d4 datetime
set @d4 = getdate()
delete
FROM test_id_guid1
WHERE 费用_ID between @费用ID and @费用ID+100
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 @费用ID=@费用ID+100
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
------------------------------------------------------------------------------------------------
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(毫秒)]
------------------------------------------------------------------------------------------------
--清空缓存
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 @费用ID int
SET @费用ID =4407669
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()
delete
FROM test
WHERE 费用_ID= @费用ID
declare @result int
select @result = datediff(ms, @d, getdate())
declare @d1 datetime
set @d1 = getdate()
delete
FROM test_ID
WHERE 费用_ID= @费用ID
declare @result1 int
select @result1 = datediff(ms, @d1, getdate())
declare @d2 datetime
set @d2 = getdate()
delete
FROM test_guid
WHERE 费用_ID= @费用ID
declare @result2 int
select @result2 = datediff(ms, @d2, getdate())
declare @d3 datetime
set @d3 = getdate()
delete
FROM test_id_guid
WHERE 费用_ID= @费用ID
declare @result3 int
select @result3 = datediff(ms, @d3, getdate())
declare @d4 datetime
set @d4 = getdate()
delete
FROM test_id_guid1
WHERE 费用_ID= @费用ID
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 @费用ID=@费用ID+1
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
------------------------------------------------------------------------------------------------
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(毫秒)]
--创建索引
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)
------------------------------------------------------------------------------------------------
--清空缓存
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 @费用ID int
SET @费用ID =4406668
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=10
WHILE (@start<=@end)
BEGIN --循环开始
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
declare @d datetime
set @d = getdate()
delete
FROM test
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result int
select @result = datediff(ms, @d, getdate())
declare @d1 datetime
set @d1 = getdate()
delete
FROM test_ID
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result1 int
select @result1 = datediff(ms, @d1, getdate())
declare @d2 datetime
set @d2 = getdate()
delete
FROM test_guid
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result2 int
select @result2 = datediff(ms, @d2, getdate())
declare @d3 datetime
set @d3 = getdate()
delete
FROM test_id_guid
WHERE 费用_ID between @费用ID and @费用ID+100
declare @result3 int
select @result3 = datediff(ms, @d3, getdate())
declare @d4 datetime
set @d4 = getdate()
delete
FROM test_id_guid1
WHERE 费用_ID between @费用ID and @费用ID+100
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 @费用ID=@费用ID+100
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
------------------------------------------------------------------------------------------------
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(毫秒)]
