带外部连接的sql长查询，

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

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

SELECT
cph.clientPollId,
cph.clientPollSeqNo,
cph.clientId,
cph.pollDate,
d.deviceId,
allPollData_A.HEALTHY,
batteryPollData_A.BATTERY_CHARGE_CYCLES,
batteryPollData_A.BATTERY_CURRENT,
batteryPollData_A.BATTERY_CURRENT_MAX,
batteryPollData_A.BATTERY_EFFECTIVE_SOC,
batteryPollData_A.BATTERY_EFFECTIVE_SOC_EXPLANATION,
batteryPollData_A.BATTERY_ENERGY,
batteryPollData_A.BATTERY_POWER,
batteryPollData_A.BATTERY_POWER_MAX,
batteryPollData_A.BATTERY_REAL_SOC,
batteryPollData_A.BATTERY_STORED_ENERGY,
batteryPollData_A.BATTERY_TEMP,
batteryPollData_A.BATTERY_VOLTAGE,
batteryPollData_A.BATTERY_VOLTAGE_MAX,
batteryPollData_A.BATTERY_VOLTAGE_MIN,
batteryPollData_A.BMS_VERSON,
batteryPollData_A.CELL_TEMPERATURE_MAX,
batteryPollData_A.CELL_TEMPERATURE_MIN,
batteryPollData_A.CELL_VOLTAGE_MAX,
batteryPollData_A.CELL_VOLTAGE_MIN,
.
.
.
FROM clientPollHeader_A cph
JOIN device d on cph.clientId = d.clientId
JOIN client c on d.clientId = c.clientId
JOIN latestClientPoll lcp on cph.clientPollSeqNo = lcp.clientPollSeqNo
LEFT OUTER JOIN allPollData_A on cph.clientPollSeqNo = allPollData_A.clientPollSeqNo
and d.deviceId = allPollData_A.deviceId
LEFT OUTER JOIN batteryPollData_A on cph.clientPollSeqNo = batteryPollData_A.clientPollSeqNo
and d.deviceId = batteryPollData_A.deviceId
LEFT OUTER JOIN deltaPollData_A on cph.clientPollSeqNo = deltaPollData_A.clientPollSeqNo
and d.deviceId = deltaPollData_A.deviceId
LEFT OUTER JOIN dessPollData_A on cph.clientPollSeqNo = dessPollData_A.clientPollSeqNo
and d.deviceId = dessPollData_A.deviceId
LEFT OUTER JOIN programPollData_A on cph.clientPollSeqNo = programPollData_A.clientPollSeqNo
and d.deviceId = programPollData_A.deviceId
LEFT OUTER JOIN emersonthermostat1PollData_A on cph.clientPollSeqNo = emersonthermostat1PollData_A.clientPollSeqNo
and d.deviceId = emersonthermostat1PollData_A.deviceId
LEFT OUTER JOIN emersonthermostat2PollData_A on cph.clientPollSeqNo = emersonthermostat2PollData_A.clientPollSeqNo
and d.deviceId = emersonthermostat2PollData_A.deviceId
LEFT OUTER JOIN emersonthermostat3PollData_A on cph.clientPollSeqNo = emersonthermostat3PollData_A.clientPollSeqNo
and d.deviceId = emersonthermostat3PollData_A.deviceId
LEFT OUTER JOIN emersonthermostat4PollData_A on cph.clientPollSeqNo = emersonthermostat4PollData_A.clientPollSeqNo
and d.deviceId = emersonthermostat4PollData_A.deviceId
LEFT OUTER JOIN thermostatPollData_A on cph.clientPollSeqNo = thermostatPollData_A.clientPollSeqNo
and d.deviceId = thermostatPollData_A.deviceId
LEFT OUTER JOIN inverterPollData_A on cph.clientPollSeqNo = inverterPollData_A.clientPollSeqNo
and d.deviceId = inverterPollData_A.deviceId
LEFT OUTER JOIN ioboardPollData_A on cph.clientPollSeqNo = ioboardPollData_A.clientPollSeqNo
and d.deviceId = ioboardPollData_A.deviceId
LEFT OUTER JOIN kokambatteryPollData_A on cph.clientPollSeqNo = kokambatteryPollData_A.clientPollSeqNo
and d.deviceId = kokambatteryPollData_A.deviceId
LEFT OUTER JOIN maggiePollData_A on cph.clientPollSeqNo = maggiePollData_A.clientPollSeqNo
and d.deviceId = maggiePollData_A.deviceId
LEFT OUTER JOIN mercedesbatteryPollData_A on cph.clientPollSeqNo = mercedesbatteryPollData_A.clientPollSeqNo
and d.deviceId = mercedesbatteryPollData_A.deviceId
LEFT OUTER JOIN omronPollData_A on cph.clientPollSeqNo = omronPollData_A.clientPollSeqNo
and d.deviceId = omronPollData_A.deviceId
LEFT OUTER JOIN daikinacPollData_A on cph.clientPollSeqNo = daikinacPollData_A.clientPollSeqNo
and d.deviceId = daikinacPollData_A.deviceId
LEFT OUTER JOIN outbackradianPollData_A on cph.clientPollSeqNo = outbackradianPollData_A.clientPollSeqNo
and d.deviceId = outbackradianPollData_A.deviceId
LEFT OUTER JOIN pvgenPollData_A on cph.clientPollSeqNo = pvgenPollData_A.clientPollSeqNo
and d.deviceId = pvgenPollData_A.deviceId
LEFT OUTER JOIN safeplugPollData_A on cph.clientPollSeqNo = safeplugPollData_A.clientPollSeqNo
and d.deviceId = safeplugPollData_A.deviceId
LEFT OUTER JOIN schneiderinverterPollData_A on cph.clientPollSeqNo = schneiderinverterPollData_A.clientPollSeqNo
and d.deviceId = schneiderinverterPollData_A.deviceId
LEFT OUTER JOIN solarchargecontrollerPollData_A on cph.clientPollSeqNo = solarchargecontrollerPollData_A.clientPollSeqNo
and d.deviceId = solarchargecontrollerPollData_A.deviceId
LEFT OUTER JOIN owonPollData_A on cph.clientPollSeqNo = owonPollData_A.clientPollSeqNo
and d.deviceId = owonPollData_A.deviceId
LEFT OUTER JOIN ecobeePollData_A on cph.clientPollSeqNo = ecobeePollData_A.clientPollSeqNo
and d.deviceId = ecobeePollData_A.deviceId
LEFT OUTER JOIN lgPollData_A on cph.clientPollSeqNo = lgPollData_A.clientPollSeqNo
and d.deviceId = lgPollData_A.deviceId
LEFT OUTER JOIN eatonemcb1PollData_A on cph.clientPollSeqNo = eatonemcb1PollData_A.clientPollSeqNo
and d.deviceId = eatonemcb1PollData_A.deviceId
LEFT OUTER JOIN eyedroPollData_A on cph.clientPollSeqNo = eyedroPollData_A.clientPollSeqNo
and d.deviceId = eyedroPollData_A.deviceId
LEFT OUTER JOIN iflowPollData_A on cph.clientPollSeqNo = iflowPollData_A.clientPollSeqNo
and d.deviceId = iflowPollData_A.deviceId
WHERE
(
   lcp.clientId IN
   (
      '01886fc9-3442-473a-85fa-3325f2e6a66d',
      '94711a81-76eb-4865-9fc9-95c622071c24'
   )
)
union
SELECT
cph.clientPollId,
cph.clientPollSeqNo,
cph.clientId,
cph.pollDate,
d.deviceId,
allPollData_B.HEALTHY,
batteryPollData_B.BATTERY_CHARGE_CYCLES,
batteryPollData_B.BATTERY_CURRENT,
batteryPollData_B.BATTERY_CURRENT_MAX,
batteryPollData_B.BATTERY_EFFECTIVE_SOC,
batteryPollData_B.BATTERY_EFFECTIVE_SOC_EXPLANATION,
batteryPollData_B.BATTERY_ENERGY,
batteryPollData_B.BATTERY_POWER,
batteryPollData_B.BATTERY_POWER_MAX,
batteryPollData_B.BATTERY_REAL_SOC,
batteryPollData_B.BATTERY_STORED_ENERGY,
batteryPollData_B.BATTERY_TEMP,
batteryPollData_B.BATTERY_VOLTAGE,
batteryPollData_B.BATTERY_VOLTAGE_MAX,
batteryPollData_B.BATTERY_VOLTAGE_MIN,
batteryPollData_B.BMS_VERSON,
batteryPollData_B.CELL_TEMPERATURE_MAX,
batteryPollData_B.CELL_TEMPERATURE_MIN,
batteryPollData_B.CELL_VOLTAGE_MAX,
batteryPollData_B.CELL_VOLTAGE_MIN,
.
.
.
FROM clientPollHeader_B cph
JOIN device d on cph.clientId = d.clientId
JOIN client c on d.clientId = c.clientId
JOIN latestClientPoll lcp on cph.clientPollSeqNo = lcp.clientPollSeqNo
LEFT OUTER JOIN allPollData_B on cph.clientPollSeqNo = allPollData_B.clientPollSeqNo
and d.deviceId = allPollData_B.deviceId
LEFT OUTER JOIN batteryPollData_B on cph.clientPollSeqNo = batteryPollData_B.clientPollSeqNo
and d.deviceId = batteryPollData_B.deviceId
LEFT OUTER JOIN deltaPollData_B on cph.clientPollSeqNo = deltaPollData_B.clientPollSeqNo
and d.deviceId = deltaPollData_B.deviceId
LEFT OUTER JOIN dessPollData_B on cph.clientPollSeqNo = dessPollData_B.clientPollSeqNo
and d.deviceId = dessPollData_B.deviceId
LEFT OUTER JOIN programPollData_B on cph.clientPollSeqNo = programPollData_B.clientPollSeqNo
and d.deviceId = programPollData_B.deviceId
LEFT OUTER JOIN emersonthermostat1PollData_B on cph.clientPollSeqNo = emersonthermostat1PollData_B.clientPollSeqNo
and d.deviceId = emersonthermostat1PollData_B.deviceId
LEFT OUTER JOIN emersonthermostat2PollData_B on cph.clientPollSeqNo = emersonthermostat2PollData_B.clientPollSeqNo
and d.deviceId = emersonthermostat2PollData_B.deviceId
LEFT OUTER JOIN emersonthermostat3PollData_B on cph.clientPollSeqNo = emersonthermostat3PollData_B.clientPollSeqNo
and d.deviceId = emersonthermostat3PollData_B.deviceId
LEFT OUTER JOIN emersonthermostat4PollData_B on cph.clientPollSeqNo = emersonthermostat4PollData_B.clientPollSeqNo
and d.deviceId = emersonthermostat4PollData_B.deviceId
LEFT OUTER JOIN thermostatPollData_B on cph.clientPollSeqNo = thermostatPollData_B.clientPollSeqNo
and d.deviceId = thermostatPollData_B.deviceId
LEFT OUTER JOIN inverterPollData_B on cph.clientPollSeqNo = inverterPollData_B.clientPollSeqNo
and d.deviceId = inverterPollData_B.deviceId
LEFT OUTER JOIN ioboardPollData_B on cph.clientPollSeqNo = ioboardPollData_B.clientPollSeqNo
and d.deviceId = ioboardPollData_B.deviceId
LEFT OUTER JOIN kokambatteryPollData_B on cph.clientPollSeqNo = kokambatteryPollData_B.clientPollSeqNo
and d.deviceId = kokambatteryPollData_B.deviceId
LEFT OUTER JOIN maggiePollData_B on cph.clientPollSeqNo = maggiePollData_B.clientPollSeqNo
and d.deviceId = maggiePollData_B.deviceId
LEFT OUTER JOIN mercedesbatteryPollData_B on cph.clientPollSeqNo = mercedesbatteryPollData_B.clientPollSeqNo
and d.deviceId = mercedesbatteryPollData_B.deviceId
LEFT OUTER JOIN omronPollData_B on cph.clientPollSeqNo = omronPollData_B.clientPollSeqNo
and d.deviceId = omronPollData_B.deviceId
LEFT OUTER JOIN daikinacPollData_B on cph.clientPollSeqNo = daikinacPollData_B.clientPollSeqNo
and d.deviceId = daikinacPollData_B.deviceId
LEFT OUTER JOIN outbackradianPollData_B on cph.clientPollSeqNo = outbackradianPollData_B.clientPollSeqNo
and d.deviceId = outbackradianPollData_B.deviceId
LEFT OUTER JOIN pvgenPollData_B on cph.clientPollSeqNo = pvgenPollData_B.clientPollSeqNo
and d.deviceId = pvgenPollData_B.deviceId
LEFT OUTER JOIN safeplugPollData_B on cph.clientPollSeqNo = safeplugPollData_B.clientPollSeqNo
and d.deviceId = safeplugPollData_B.deviceId
LEFT OUTER JOIN schneiderinverterPollData_B on cph.clientPollSeqNo = schneiderinverterPollData_B.clientPollSeqNo
and d.deviceId = schneiderinverterPollData_B.deviceId
LEFT OUTER JOIN solarchargecontrollerPollData_B on cph.clientPollSeqNo = solarchargecontrollerPollData_B.clientPollSeqNo
and d.deviceId = solarchargecontrollerPollData_B.deviceId
LEFT OUTER JOIN owonPollData_B on cph.clientPollSeqNo = owonPollData_B.clientPollSeqNo
and d.deviceId = owonPollData_B.deviceId
LEFT OUTER JOIN ecobeePollData_B on cph.clientPollSeqNo = ecobeePollData_B.clientPollSeqNo
and d.deviceId = ecobeePollData_B.deviceId
LEFT OUTER JOIN lgPollData_B on cph.clientPollSeqNo = lgPollData_B.clientPollSeqNo
and d.deviceId = lgPollData_B.deviceId
LEFT OUTER JOIN eatonemcb1PollData_B on cph.clientPollSeqNo = eatonemcb1PollData_B.clientPollSeqNo
and d.deviceId = eatonemcb1PollData_B.deviceId
LEFT OUTER JOIN eyedroPollData_B on cph.clientPollSeqNo = eyedroPollData_B.clientPollSeqNo
and d.deviceId = eyedroPollData_B.deviceId
LEFT OUTER JOIN iflowPollData_B on cph.clientPollSeqNo = iflowPollData_B.clientPollSeqNo
and d.deviceId = iflowPollData_B.deviceId
WHERE
(
   lcp.clientId IN
   (
      '01886fc9-3442-473a-85fa-3325f2e6a66d',
      '94711a81-76eb-4865-9fc9-95c622071c24'
   )
)

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

sqllong-integer

暂无答案！

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

我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com