GUID和自增ID的比较_delete-白红宇

GUID和自增ID的比较_delete

阅读量：4549 次

发布时间：2019-06-08

本文共 7939 字，大约阅读时间需要 26 分钟。

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(毫秒)]

2.最后一条数据删除

------------------------------------------------------------------------------------------------

--清空缓存

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(毫秒)]

转载于:https://www.cnblogs.com/qanholas/archive/2011/09/07/2170034.html

你可能感兴趣的文章

sklearn的train_test_split()各函数参数含义解释（非常全）

机器学习线性回归算法的评价指标（简单线性回归问题）

查看>>

教你如何剖析源码（转）

查看>>

proxy和proxy-no的策略取值区别

查看>>

Silverlight代码编写对控件的PlaneProjection.RotationY属性控制动画

Laravel 数据库连接，数据库名，配置文件修改

查看>>

屌丝接盘侠们,孩子可能不是你们亲生的!

查看>>

BZOJ 1854 【SCOI2010】游戏

查看>>

JavaScript - 匿名函数和闭包

查看>>

负载均衡下的资源文件配置/多站点下的资源文件夹共享（Windows IIS）

查看>>