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

概述测试脚本： -- 创建测试库-- drop database TestDBuse mastergocreate database TestDBgo-- 创建证书-- drop certificate Mycertificate;use TestDBgocreate certificate Mycertificate encryption by password

测试脚本：

--  创建测试库--  drop database TestDBuse mastergocreate database TestDBgo--  创建证书--  drop certificate  Mycertificate;use TestDBgocreate 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 TestDBgocreate symmetric key MySymmetricwith       algorithm=aes_128       encryption by certificate Mycertificatego  --  测试加密和解密--  close symmetric key MySymmetric; use TestDBgoopen symmetric key MySymmetricdecryption by certificate Mycertificate with password = N'Hello@Mycertificate';goselect encryptbykey(key_guID('MySymmetric'),cast('123456' as varchar(20)))goselect convert(varchar(20),decryptbykeyautocert(cert_ID('Mycertificate'),N'Hello@Mycertificate',0x001E60848B02184E9106B2BDF6F612470100000023BE0228F35192CC39EE810A0B6D31B4EC12F68EAFC2DA8FB4F6C688F869D7EF))go--  创建分表--  drop table objects_Part1,objects_Part2use TestDBgoselect object_ID,name,principal_ID,schema_ID,parent_object_ID,type,type_desc,create_date,modify_dateinto objects_Part1from sys.objectsgoselect object_ID,is_ms_shipped,is_published,is_schema_publishedinto objects_Part2from sys.objects where object_ID<>object_ID('objects_Part1')gocreate clustered index ix_objects_Part1 on [dbo].[objects_Part1]([object_ID])gocreate clustered index ix_objects_Part2 on [dbo].[objects_Part2]([object_ID])go--  创建联合视图--  drop vIEw objectsAlluse TestDBgocreate 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_publishedfrom [dbo].[objects_Part1] t1 left join [dbo].[objects_Part2] t2 on t1.object_ID=t2.object_IDgo--  创建更新触发器--  drop trigger [dbo].[tgr_objectsall_update] use TestDBgocreate trigger [dbo].[tgr_objectsall_update]  on [dbo].[VobjectsAll]   instead of update  asbegin 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: 11111111 00000001*/ /*表2: 00000000 11111110*/ 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 endendgo--  测试!use TestDBgoselect * from [vobjectsall]update [vobjectsall] set principal_ID = 0 where object_ID = 3

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

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

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

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

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

总结

以上是内存溢出为你收集整理的SQLServer 不执行的条件分支却消耗时间！求解…全部内容，希望文章能够帮你解决SQLServer 不执行的条件分支却消耗时间！求解…所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。