首页 > 代码库 > SQL并行与否的性能差异
SQL并行与否的性能差异
比较两种代码,核心代码相同,其中一个使用变量保存查询出的结果,另一个直接输出。使用同一变量时,强迫SQL放弃了并行,使用了循环。
?
测试结果
表‘#1699586C‘。扫描计数1,逻辑读取186 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
?
SQL Server 执行时间:
CPU 时间= 0 毫秒,占用时间= 335 毫秒。
?
SQL Server 执行时间:
CPU 时间= 33954 毫秒,占用时间= 35315 毫秒。
?
表‘#1699586C‘。扫描计数1,逻辑读取186 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
?
SQL Server 执行时间:
CPU 时间= 93 毫秒,占用时间= 167 毫秒
?
?
?
- SELECT @bin = @bin + CASE @withDeviceID
- ????????????????????????????????????????WHEN 0 THEN 0x
- ????????????????????????????????????????ELSE CAST (deviceid AS BINARY(16))
- ??????????????????????????????????????END
- ????????????????????????+ dbo.updatesum(dbo.long2Bin_4byte(CardSerialNo)
- ????????????????????????????????????????+ dbo.long2Bin_4byte(Tac)
- ????????????????????????????????????????+ dbo.long2Bin(CardSeq)
- ????????????????????????????????????????+ dbo.int2Bin(HardwareNo)
- ????????????????????????????????????????+ CAST (CardType AS BINARY(1))
- ????????????????????????????????????????+ CAST (TranscationType AS BINARY(1))
- ????????????????????????????????????????+ CAST (Station AS BINARY(1))
- ????????????????????????????????????????+ dbo.int2Bin_3byte(PreBalance)
- ????????????????????????????????????????+ dbo.int2Bin_3byte(Amount)
- ????????????????????????????????????????+ dbo.time2utcbin(TranscationTime)
- ????????????????????????????????????????+ CAST (DeviceType AS BINARY(1))
- ????????????????????????????????????????+ dbo.int2Bin(TotalIntegral)
- ????????????????????????????????????????+ dbo.short2bin(Integral)
- ????????????????????????????????????????+ dbo.int2Bin(AccountCount)
- ????????????????????????????????????????+ CAST (Account AS BINARY(1))
- ????????????????????????????????????????+ dbo.int2Bin(Balance)
- ????????????????????????????????????????+ dbo.int2bin_3byte(Price)
- ????????????????????????????????????????+ CAST (Period AS BINARY(1))
- ????????????????????????????????????????+ dbo.int2Bin(Device)
- ????????????????????????????????????????+ CAST (InOutFlag AS BINARY(1))
- ????????????????????????????????????????+ CAST (UpDownFlag AS BINARY(1))
- ????????????????????????????????????????+ dbo.short2bin(Operator)
- ????????????????????????????????????????+ CAST (CardStandard AS BINARY(1))
- ????????????????????????????????????????+ CAST (TranscationMode AS BINARY(1))
- ????????????????????????????????????????+ CAST (0 AS BINARY(1))
- ????????????????????????????????????????+ CAST(0 AS BINARY(7)) + 0x55, 0, 62,
- ????????????????????????????????????????63)
- ????????????????FROM @record
?
?
- SELECT CASE 1
- ??????????WHEN 0 THEN 0x
- ??????????ELSE CAST (deviceid AS BINARY(16))
- ????????END + dbo.updatesum(dbo.long2Bin_4byte(CardSerialNo)
- ????????????????????????????+ dbo.long2Bin_4byte(Tac) + dbo.long2Bin(CardSeq)
- ????????????????????????????+ dbo.int2Bin(HardwareNo)
- ????????????????????????????+ CAST (CardType AS BINARY(1))
- ????????????????????????????+ CAST (TranscationType AS BINARY(1))
- ????????????????????????????+ CAST (Station AS BINARY(1))
- ????????????????????????????+ dbo.int2Bin_3byte(PreBalance)
- ????????????????????????????+ dbo.int2Bin_3byte(Amount)
- ????????????????????????????+ dbo.time2utcbin(TranscationTime)
- ????????????????????????????+ CAST (DeviceType AS BINARY(1))
- ????????????????????????????+ dbo.int2Bin(TotalIntegral)
- ????????????????????????????+ dbo.short2bin(Integral)
- ????????????????????????????+ dbo.int2Bin(AccountCount)
- ????????????????????????????+ CAST (Account AS BINARY(1))
- ????????????????????????????+ dbo.int2Bin(Balance) + dbo.int2bin_3byte(Price)
- ????????????????????????????+ CAST (Period AS BINARY(1)) + dbo.int2Bin(Device)
- ????????????????????????????+ CAST (InOutFlag AS BINARY(1))
- ????????????????????????????+ CAST (UpDownFlag AS BINARY(1))
- ????????????????????????????+ dbo.short2bin(Operator)
- ????????????????????????????+ CAST (CardStandard AS BINARY(1))
- ????????????????????????????+ CAST (TranscationMode AS BINARY(1))
- ????????????????????????????+ CAST (0 AS BINARY(1)) + CAST(0 AS BINARY(7))
- ????????????????????????????+ 0x55, 0, 62, 63)
- FROM @record
?
SQL并行与否的性能差异
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。