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)