查看内容

SQL并行与否的性能差异

  • 2020-02-16 22:23
  • 数据库知识
  • Views

比较两种代码,核心代码相同,其中一个使用变量保存查询出的结果,另一个直接输出。使用同一变量时,强迫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打印全年日历 下一篇:没有了