带外部连接的sql长查询，

sr4lhrrt  于 2021-07-24  发布在  Java
关注(0)|答案(0)|浏览(253)

我有一个很长的问题，基本上由以下两部分组成，

  1. SELECT
  2. cph.clientPollId,
  3. cph.clientPollSeqNo,
  4. cph.clientId,
  5. cph.pollDate,
  6. d.deviceId,
  7. allPollData_A.HEALTHY,
  8. batteryPollData_A.BATTERY_CHARGE_CYCLES,
  9. batteryPollData_A.BATTERY_CURRENT,
  10. batteryPollData_A.BATTERY_CURRENT_MAX,
  11. batteryPollData_A.BATTERY_EFFECTIVE_SOC,
  12. batteryPollData_A.BATTERY_EFFECTIVE_SOC_EXPLANATION,
  13. batteryPollData_A.BATTERY_ENERGY,
  14. batteryPollData_A.BATTERY_POWER,
  15. batteryPollData_A.BATTERY_POWER_MAX,
  16. batteryPollData_A.BATTERY_REAL_SOC,
  17. batteryPollData_A.BATTERY_STORED_ENERGY,
  18. batteryPollData_A.BATTERY_TEMP,
  19. batteryPollData_A.BATTERY_VOLTAGE,
  20. batteryPollData_A.BATTERY_VOLTAGE_MAX,
  21. batteryPollData_A.BATTERY_VOLTAGE_MIN,
  22. batteryPollData_A.BMS_VERSON,
  23. batteryPollData_A.CELL_TEMPERATURE_MAX,
  24. batteryPollData_A.CELL_TEMPERATURE_MIN,
  25. batteryPollData_A.CELL_VOLTAGE_MAX,
  26. batteryPollData_A.CELL_VOLTAGE_MIN,
  27. .
  28. .
  29. .
  30. FROM clientPollHeader_A cph
  31. JOIN device d on cph.clientId = d.clientId
  32. JOIN client c on d.clientId = c.clientId
  33. JOIN latestClientPoll lcp on cph.clientPollSeqNo = lcp.clientPollSeqNo
  34. LEFT OUTER JOIN allPollData_A on cph.clientPollSeqNo = allPollData_A.clientPollSeqNo
  35. and d.deviceId = allPollData_A.deviceId
  36. LEFT OUTER JOIN batteryPollData_A on cph.clientPollSeqNo = batteryPollData_A.clientPollSeqNo
  37. and d.deviceId = batteryPollData_A.deviceId
  38. LEFT OUTER JOIN deltaPollData_A on cph.clientPollSeqNo = deltaPollData_A.clientPollSeqNo
  39. and d.deviceId = deltaPollData_A.deviceId
  40. LEFT OUTER JOIN dessPollData_A on cph.clientPollSeqNo = dessPollData_A.clientPollSeqNo
  41. and d.deviceId = dessPollData_A.deviceId
  42. LEFT OUTER JOIN programPollData_A on cph.clientPollSeqNo = programPollData_A.clientPollSeqNo
  43. and d.deviceId = programPollData_A.deviceId
  44. LEFT OUTER JOIN emersonthermostat1PollData_A on cph.clientPollSeqNo = emersonthermostat1PollData_A.clientPollSeqNo
  45. and d.deviceId = emersonthermostat1PollData_A.deviceId
  46. LEFT OUTER JOIN emersonthermostat2PollData_A on cph.clientPollSeqNo = emersonthermostat2PollData_A.clientPollSeqNo
  47. and d.deviceId = emersonthermostat2PollData_A.deviceId
  48. LEFT OUTER JOIN emersonthermostat3PollData_A on cph.clientPollSeqNo = emersonthermostat3PollData_A.clientPollSeqNo
  49. and d.deviceId = emersonthermostat3PollData_A.deviceId
  50. LEFT OUTER JOIN emersonthermostat4PollData_A on cph.clientPollSeqNo = emersonthermostat4PollData_A.clientPollSeqNo
  51. and d.deviceId = emersonthermostat4PollData_A.deviceId
  52. LEFT OUTER JOIN thermostatPollData_A on cph.clientPollSeqNo = thermostatPollData_A.clientPollSeqNo
  53. and d.deviceId = thermostatPollData_A.deviceId
  54. LEFT OUTER JOIN inverterPollData_A on cph.clientPollSeqNo = inverterPollData_A.clientPollSeqNo
  55. and d.deviceId = inverterPollData_A.deviceId
  56. LEFT OUTER JOIN ioboardPollData_A on cph.clientPollSeqNo = ioboardPollData_A.clientPollSeqNo
  57. and d.deviceId = ioboardPollData_A.deviceId
  58. LEFT OUTER JOIN kokambatteryPollData_A on cph.clientPollSeqNo = kokambatteryPollData_A.clientPollSeqNo
  59. and d.deviceId = kokambatteryPollData_A.deviceId
  60. LEFT OUTER JOIN maggiePollData_A on cph.clientPollSeqNo = maggiePollData_A.clientPollSeqNo
  61. and d.deviceId = maggiePollData_A.deviceId
  62. LEFT OUTER JOIN mercedesbatteryPollData_A on cph.clientPollSeqNo = mercedesbatteryPollData_A.clientPollSeqNo
  63. and d.deviceId = mercedesbatteryPollData_A.deviceId
  64. LEFT OUTER JOIN omronPollData_A on cph.clientPollSeqNo = omronPollData_A.clientPollSeqNo
  65. and d.deviceId = omronPollData_A.deviceId
  66. LEFT OUTER JOIN daikinacPollData_A on cph.clientPollSeqNo = daikinacPollData_A.clientPollSeqNo
  67. and d.deviceId = daikinacPollData_A.deviceId
  68. LEFT OUTER JOIN outbackradianPollData_A on cph.clientPollSeqNo = outbackradianPollData_A.clientPollSeqNo
  69. and d.deviceId = outbackradianPollData_A.deviceId
  70. LEFT OUTER JOIN pvgenPollData_A on cph.clientPollSeqNo = pvgenPollData_A.clientPollSeqNo
  71. and d.deviceId = pvgenPollData_A.deviceId
  72. LEFT OUTER JOIN safeplugPollData_A on cph.clientPollSeqNo = safeplugPollData_A.clientPollSeqNo
  73. and d.deviceId = safeplugPollData_A.deviceId
  74. LEFT OUTER JOIN schneiderinverterPollData_A on cph.clientPollSeqNo = schneiderinverterPollData_A.clientPollSeqNo
  75. and d.deviceId = schneiderinverterPollData_A.deviceId
  76. LEFT OUTER JOIN solarchargecontrollerPollData_A on cph.clientPollSeqNo = solarchargecontrollerPollData_A.clientPollSeqNo
  77. and d.deviceId = solarchargecontrollerPollData_A.deviceId
  78. LEFT OUTER JOIN owonPollData_A on cph.clientPollSeqNo = owonPollData_A.clientPollSeqNo
  79. and d.deviceId = owonPollData_A.deviceId
  80. LEFT OUTER JOIN ecobeePollData_A on cph.clientPollSeqNo = ecobeePollData_A.clientPollSeqNo
  81. and d.deviceId = ecobeePollData_A.deviceId
  82. LEFT OUTER JOIN lgPollData_A on cph.clientPollSeqNo = lgPollData_A.clientPollSeqNo
  83. and d.deviceId = lgPollData_A.deviceId
  84. LEFT OUTER JOIN eatonemcb1PollData_A on cph.clientPollSeqNo = eatonemcb1PollData_A.clientPollSeqNo
  85. and d.deviceId = eatonemcb1PollData_A.deviceId
  86. LEFT OUTER JOIN eyedroPollData_A on cph.clientPollSeqNo = eyedroPollData_A.clientPollSeqNo
  87. and d.deviceId = eyedroPollData_A.deviceId
  88. LEFT OUTER JOIN iflowPollData_A on cph.clientPollSeqNo = iflowPollData_A.clientPollSeqNo
  89. and d.deviceId = iflowPollData_A.deviceId
  90. WHERE
  91. (
  92.    lcp.clientId IN
  93.    (
  94.       '01886fc9-3442-473a-85fa-3325f2e6a66d',
  95.       '94711a81-76eb-4865-9fc9-95c622071c24'
  96.    )
  97. )
  98. union
  99. SELECT
  100. cph.clientPollId,
  101. cph.clientPollSeqNo,
  102. cph.clientId,
  103. cph.pollDate,
  104. d.deviceId,
  105. allPollData_B.HEALTHY,
  106. batteryPollData_B.BATTERY_CHARGE_CYCLES,
  107. batteryPollData_B.BATTERY_CURRENT,
  108. batteryPollData_B.BATTERY_CURRENT_MAX,
  109. batteryPollData_B.BATTERY_EFFECTIVE_SOC,
  110. batteryPollData_B.BATTERY_EFFECTIVE_SOC_EXPLANATION,
  111. batteryPollData_B.BATTERY_ENERGY,
  112. batteryPollData_B.BATTERY_POWER,
  113. batteryPollData_B.BATTERY_POWER_MAX,
  114. batteryPollData_B.BATTERY_REAL_SOC,
  115. batteryPollData_B.BATTERY_STORED_ENERGY,
  116. batteryPollData_B.BATTERY_TEMP,
  117. batteryPollData_B.BATTERY_VOLTAGE,
  118. batteryPollData_B.BATTERY_VOLTAGE_MAX,
  119. batteryPollData_B.BATTERY_VOLTAGE_MIN,
  120. batteryPollData_B.BMS_VERSON,
  121. batteryPollData_B.CELL_TEMPERATURE_MAX,
  122. batteryPollData_B.CELL_TEMPERATURE_MIN,
  123. batteryPollData_B.CELL_VOLTAGE_MAX,
  124. batteryPollData_B.CELL_VOLTAGE_MIN,
  125. .
  126. .
  127. .
  128. FROM clientPollHeader_B cph
  129. JOIN device d on cph.clientId = d.clientId
  130. JOIN client c on d.clientId = c.clientId
  131. JOIN latestClientPoll lcp on cph.clientPollSeqNo = lcp.clientPollSeqNo
  132. LEFT OUTER JOIN allPollData_B on cph.clientPollSeqNo = allPollData_B.clientPollSeqNo
  133. and d.deviceId = allPollData_B.deviceId
  134. LEFT OUTER JOIN batteryPollData_B on cph.clientPollSeqNo = batteryPollData_B.clientPollSeqNo
  135. and d.deviceId = batteryPollData_B.deviceId
  136. LEFT OUTER JOIN deltaPollData_B on cph.clientPollSeqNo = deltaPollData_B.clientPollSeqNo
  137. and d.deviceId = deltaPollData_B.deviceId
  138. LEFT OUTER JOIN dessPollData_B on cph.clientPollSeqNo = dessPollData_B.clientPollSeqNo
  139. and d.deviceId = dessPollData_B.deviceId
  140. LEFT OUTER JOIN programPollData_B on cph.clientPollSeqNo = programPollData_B.clientPollSeqNo
  141. and d.deviceId = programPollData_B.deviceId
  142. LEFT OUTER JOIN emersonthermostat1PollData_B on cph.clientPollSeqNo = emersonthermostat1PollData_B.clientPollSeqNo
  143. and d.deviceId = emersonthermostat1PollData_B.deviceId
  144. LEFT OUTER JOIN emersonthermostat2PollData_B on cph.clientPollSeqNo = emersonthermostat2PollData_B.clientPollSeqNo
  145. and d.deviceId = emersonthermostat2PollData_B.deviceId
  146. LEFT OUTER JOIN emersonthermostat3PollData_B on cph.clientPollSeqNo = emersonthermostat3PollData_B.clientPollSeqNo
  147. and d.deviceId = emersonthermostat3PollData_B.deviceId
  148. LEFT OUTER JOIN emersonthermostat4PollData_B on cph.clientPollSeqNo = emersonthermostat4PollData_B.clientPollSeqNo
  149. and d.deviceId = emersonthermostat4PollData_B.deviceId
  150. LEFT OUTER JOIN thermostatPollData_B on cph.clientPollSeqNo = thermostatPollData_B.clientPollSeqNo
  151. and d.deviceId = thermostatPollData_B.deviceId
  152. LEFT OUTER JOIN inverterPollData_B on cph.clientPollSeqNo = inverterPollData_B.clientPollSeqNo
  153. and d.deviceId = inverterPollData_B.deviceId
  154. LEFT OUTER JOIN ioboardPollData_B on cph.clientPollSeqNo = ioboardPollData_B.clientPollSeqNo
  155. and d.deviceId = ioboardPollData_B.deviceId
  156. LEFT OUTER JOIN kokambatteryPollData_B on cph.clientPollSeqNo = kokambatteryPollData_B.clientPollSeqNo
  157. and d.deviceId = kokambatteryPollData_B.deviceId
  158. LEFT OUTER JOIN maggiePollData_B on cph.clientPollSeqNo = maggiePollData_B.clientPollSeqNo
  159. and d.deviceId = maggiePollData_B.deviceId
  160. LEFT OUTER JOIN mercedesbatteryPollData_B on cph.clientPollSeqNo = mercedesbatteryPollData_B.clientPollSeqNo
  161. and d.deviceId = mercedesbatteryPollData_B.deviceId
  162. LEFT OUTER JOIN omronPollData_B on cph.clientPollSeqNo = omronPollData_B.clientPollSeqNo
  163. and d.deviceId = omronPollData_B.deviceId
  164. LEFT OUTER JOIN daikinacPollData_B on cph.clientPollSeqNo = daikinacPollData_B.clientPollSeqNo
  165. and d.deviceId = daikinacPollData_B.deviceId
  166. LEFT OUTER JOIN outbackradianPollData_B on cph.clientPollSeqNo = outbackradianPollData_B.clientPollSeqNo
  167. and d.deviceId = outbackradianPollData_B.deviceId
  168. LEFT OUTER JOIN pvgenPollData_B on cph.clientPollSeqNo = pvgenPollData_B.clientPollSeqNo
  169. and d.deviceId = pvgenPollData_B.deviceId
  170. LEFT OUTER JOIN safeplugPollData_B on cph.clientPollSeqNo = safeplugPollData_B.clientPollSeqNo
  171. and d.deviceId = safeplugPollData_B.deviceId
  172. LEFT OUTER JOIN schneiderinverterPollData_B on cph.clientPollSeqNo = schneiderinverterPollData_B.clientPollSeqNo
  173. and d.deviceId = schneiderinverterPollData_B.deviceId
  174. LEFT OUTER JOIN solarchargecontrollerPollData_B on cph.clientPollSeqNo = solarchargecontrollerPollData_B.clientPollSeqNo
  175. and d.deviceId = solarchargecontrollerPollData_B.deviceId
  176. LEFT OUTER JOIN owonPollData_B on cph.clientPollSeqNo = owonPollData_B.clientPollSeqNo
  177. and d.deviceId = owonPollData_B.deviceId
  178. LEFT OUTER JOIN ecobeePollData_B on cph.clientPollSeqNo = ecobeePollData_B.clientPollSeqNo
  179. and d.deviceId = ecobeePollData_B.deviceId
  180. LEFT OUTER JOIN lgPollData_B on cph.clientPollSeqNo = lgPollData_B.clientPollSeqNo
  181. and d.deviceId = lgPollData_B.deviceId
  182. LEFT OUTER JOIN eatonemcb1PollData_B on cph.clientPollSeqNo = eatonemcb1PollData_B.clientPollSeqNo
  183. and d.deviceId = eatonemcb1PollData_B.deviceId
  184. LEFT OUTER JOIN eyedroPollData_B on cph.clientPollSeqNo = eyedroPollData_B.clientPollSeqNo
  185. and d.deviceId = eyedroPollData_B.deviceId
  186. LEFT OUTER JOIN iflowPollData_B on cph.clientPollSeqNo = iflowPollData_B.clientPollSeqNo
  187. and d.deviceId = iflowPollData_B.deviceId
  188. WHERE
  189. (
  190.    lcp.clientId IN
  191.    (
  192.       '01886fc9-3442-473a-85fa-3325f2e6a66d',
  193.       '94711a81-76eb-4865-9fc9-95c622071c24'
  194.    )
  195. )

问题是\u a表有值，但\u b表都是空的（在这种情况下，它们可能会得到值）。因此，在联合之前的第一部分，即为_a运行不到1秒，而为_b表（空表）运行的第二部分需要31秒。所以我的问题是空表上的外部连接是否会造成瓶颈？谢谢你的帮助。

sqllong-integer

暂无答案！

目前还没有任何答案，快来回答吧！

我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com