SQLServer 不执行的条件分支却消耗时间！求解…

测试脚本：

--  创建测试库
--  drop database TestDB
use master
go
create database TestDB
go


--  创建证书
--  drop certificate  Mycertificate;
use TestDB
go
create certificate Mycertificate  
encryption by password = N'Hello@Mycertificate'
with subject = N'EnryptData certificate',start_date = N'20150101',expiry_date = N'20160101';  
go  


--  创建以证书加密的对称密钥
--  drop symmetric key MySymmetric;
use TestDB
go
create symmetric key MySymmetric
with   
    algorithm=aes_128   
    encryption by certificate Mycertificate
go  


--  测试加密和解密
--  close symmetric key MySymmetric; 
use TestDB
go
open symmetric key MySymmetric
decryption by certificate Mycertificate with password = N'Hello@Mycertificate';
go
select encryptbykey(key_guid('MySymmetric'),cast('123456' as varchar(20)))
go
select convert(varchar(20),decryptbykeyautocert(cert_id('Mycertificate'),N'Hello@Mycertificate',0x001E60848B02184E9106B2BDF6F612470100000023BE0228F35192CC39EE810A0B6D31B4EC12F68EAFC2DA8FB4F6C688F869D7EF))
go


--  创建分表
--  drop table objects_Part1,objects_Part2
use TestDB
go
select object_id,name,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date
into objects_Part1
from sys.objects
go
select object_id,is_ms_shipped,is_published,is_schema_published
into objects_Part2
from sys.objects where object_id<>object_id('objects_Part1')
go
create clustered index ix_objects_Part1 on [dbo].[objects_Part1]([object_id])
go
create clustered index ix_objects_Part2 on [dbo].[objects_Part2]([object_id])
go


--  创建联合视图
--  drop view objectsAll
use TestDB
go
create view [dbo].[VobjectsAll]  
as 
select t1.object_id,t1.name,t1.principal_id,t1.schema_id,t1.parent_object_id,t1.type,t1.type_desc,t1.create_date,t1.modify_date/*,t2.object_id*/,t2.is_ms_shipped,t2.is_published,t2.is_schema_published
from [dbo].[objects_Part1] t1 left join [dbo].[objects_Part2] t2 on t1.object_id=t2.object_id
go


--  创建更新触发器
--  drop trigger [dbo].[tgr_objectsall_update] 
use TestDB
go
create trigger [dbo].[tgr_objectsall_update]  
on [dbo].[VobjectsAll]   
instead of update  
as
begin
 declare @COLUMNS_UPDATED varbinary(100)
 declare @IsUpdateTab01 int = 0
 declare @IsUpdateTab02 int = 0
 declare @T1 int
 declare @T2 int
 set @T1 = 65281  
 set @T2 = 254	
 /*表1: 22222111 00000001*/
 /*表2: 00000000 22222110*/
 set @COLUMNS_UPDATED = COLUMNS_UPDATED()
 set @IsUpdateTab01 = SUBSTRING(@COLUMNS_UPDATED,1,2) & @T1
 set @IsUpdateTab02 = SUBSTRING(@COLUMNS_UPDATED,2,1) & @T2

 if ( @IsUpdateTab01 > 0 AND @IsUpdateTab02 = 0 )
 begin
    print 'update [objects_Part1]'
    update t1 set 
     t1.name = t2.name,t1.principal_id = t2.principal_id,t1.schema_id = t2.schema_id,t1.parent_object_id = t2.parent_object_id,t1.type = t2.type,t1.type_desc = t2.type_desc,t1.create_date = t2.create_date,t1.modify_date = t2.modify_date
    from [dbo].[objects_Part1] t1,inserted t2 where t1.object_id = t2.object_id
 end
 else if ( @IsUpdateTab01 = 0 AND @IsUpdateTab02 > 0)
 begin
    print 'update [objects_Part2]'
    open symmetric key MySymmetric
    decryption by certificate Mycertificate with password = N'Hello@Mycertificate';
    
    update t1 set 
     t1.is_ms_shipped = t2.is_ms_shipped,t1.is_published = t2.is_published,t1.is_schema_published = t2.is_schema_published
    from [dbo].[objects_Part2] t1,inserted t2 where t1.object_id = t2.object_id
 end
 else
 begin
    print 'update [objects_Part1] and [objects_Part2]'
    update t1 set 
     t1.name = t2.name,inserted t2 where t1.object_id = t2.object_id
    
    update t1 set 
     t1.is_ms_shipped = t2.is_ms_shipped,inserted t2 where t1.object_id = t2.object_id
 end
end
go



--  测试!
use TestDB
go
select * from [vobjectsall]
update [vobjectsall] set principal_id = 0 where object_id = 3

更新视图字段?principal_id ，将更新触发器中的第一个分支。执行一次更新耗时不明显，现在单线程更新30次。对比耗时如下图。

第二次更新时，去掉触发器中第二个条件分支的加密语句：

--  第二次更新去掉触发器中的打开密钥语句
open symmetric key MySymmetric
decryption by certificate Mycertificate with password = N'Hello@Mycertificate';

这就是奇怪的地方，更新的只是第一个分支中的表。第二个分支是没有执行的，但是第二个分支的 “打开密钥” 却影响到总体时间！

为什么？为什么？为什么？……

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!