首页 > 代码库 > SQL并行与否的性能差异

SQL并行与否的性能差异

2024-07-22 18:42:04   221人阅读

比较两种代码，核心代码相同，其中一个使用变量保存查询出的结果，另一个直接输出。使用同一变量时，强迫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 毫秒

?

?

?

  1. SELECT @bin = @bin + CASE @withDeviceID
  2. ????????????????????????????????????????WHEN 0 THEN 0x
  3. ????????????????????????????????????????ELSE CAST (deviceid AS BINARY(16))
  4. ??????????????????????????????????????END
  5. ????????????????????????+ dbo.updatesum(dbo.long2Bin_4byte(CardSerialNo)
  6. ????????????????????????????????????????+ dbo.long2Bin_4byte(Tac)
  7. ????????????????????????????????????????+ dbo.long2Bin(CardSeq)
  8. ????????????????????????????????????????+ dbo.int2Bin(HardwareNo)
  9. ????????????????????????????????????????+ CAST (CardType AS BINARY(1))
  10. ????????????????????????????????????????+ CAST (TranscationType AS BINARY(1))
  11. ????????????????????????????????????????+ CAST (Station AS BINARY(1))
  12. ????????????????????????????????????????+ dbo.int2Bin_3byte(PreBalance)
  13. ????????????????????????????????????????+ dbo.int2Bin_3byte(Amount)
  14. ????????????????????????????????????????+ dbo.time2utcbin(TranscationTime)
  15. ????????????????????????????????????????+ CAST (DeviceType AS BINARY(1))
  16. ????????????????????????????????????????+ dbo.int2Bin(TotalIntegral)
  17. ????????????????????????????????????????+ dbo.short2bin(Integral)
  18. ????????????????????????????????????????+ dbo.int2Bin(AccountCount)
  19. ????????????????????????????????????????+ CAST (Account AS BINARY(1))
  20. ????????????????????????????????????????+ dbo.int2Bin(Balance)
  21. ????????????????????????????????????????+ dbo.int2bin_3byte(Price)
  22. ????????????????????????????????????????+ CAST (Period AS BINARY(1))
  23. ????????????????????????????????????????+ dbo.int2Bin(Device)
  24. ????????????????????????????????????????+ CAST (InOutFlag AS BINARY(1))
  25. ????????????????????????????????????????+ CAST (UpDownFlag AS BINARY(1))
  26. ????????????????????????????????????????+ dbo.short2bin(Operator)
  27. ????????????????????????????????????????+ CAST (CardStandard AS BINARY(1))
  28. ????????????????????????????????????????+ CAST (TranscationMode AS BINARY(1))
  29. ????????????????????????????????????????+ CAST (0 AS BINARY(1))
  30. ????????????????????????????????????????+ CAST(0 AS BINARY(7)) + 0x55, 0, 62,
  31. ????????????????????????????????????????63)
  32. ????????????????FROM @record

?

?

  1. SELECT CASE 1
  2. ??????????WHEN 0 THEN 0x
  3. ??????????ELSE CAST (deviceid AS BINARY(16))
  4. ????????END + dbo.updatesum(dbo.long2Bin_4byte(CardSerialNo)
  5. ????????????????????????????+ dbo.long2Bin_4byte(Tac) + dbo.long2Bin(CardSeq)
  6. ????????????????????????????+ dbo.int2Bin(HardwareNo)
  7. ????????????????????????????+ CAST (CardType AS BINARY(1))
  8. ????????????????????????????+ CAST (TranscationType AS BINARY(1))
  9. ????????????????????????????+ CAST (Station AS BINARY(1))
  10. ????????????????????????????+ dbo.int2Bin_3byte(PreBalance)
  11. ????????????????????????????+ dbo.int2Bin_3byte(Amount)
  12. ????????????????????????????+ dbo.time2utcbin(TranscationTime)
  13. ????????????????????????????+ CAST (DeviceType AS BINARY(1))
  14. ????????????????????????????+ dbo.int2Bin(TotalIntegral)
  15. ????????????????????????????+ dbo.short2bin(Integral)
  16. ????????????????????????????+ dbo.int2Bin(AccountCount)
  17. ????????????????????????????+ CAST (Account AS BINARY(1))
  18. ????????????????????????????+ dbo.int2Bin(Balance) + dbo.int2bin_3byte(Price)
  19. ????????????????????????????+ CAST (Period AS BINARY(1)) + dbo.int2Bin(Device)
  20. ????????????????????????????+ CAST (InOutFlag AS BINARY(1))
  21. ????????????????????????????+ CAST (UpDownFlag AS BINARY(1))
  22. ????????????????????????????+ dbo.short2bin(Operator)
  23. ????????????????????????????+ CAST (CardStandard AS BINARY(1))
  24. ????????????????????????????+ CAST (TranscationMode AS BINARY(1))
  25. ????????????????????????????+ CAST (0 AS BINARY(1)) + CAST(0 AS BINARY(7))
  26. ????????????????????????????+ 0x55, 0, 62, 63)
  27. FROM @record

?

SQL并行与否的性能差异


联系
我们