sql - 重写查询和调优
问题描述
我有以下这些查询。您能否查看并让我知道逻辑是否需要进行任何修改以提高性能。还请根据逻辑对索引提出建议(如果有任何缺失,将交叉验证并添加)
查询 (1)
SELECT DT.classid,
SUM(IT.expectedamount) AS expAmt,
SUM(IT.debitamount) AS debAmt
FROM deptrans DT
inner join itemtotal IT
ON DT.id = IT.deposittransid
left outer join depreceipt DR
ON DR.deposittransid = DT.id
WHERE DT.bankaccountid IN ( ? )
AND (( ( DT.statecode IN ( ?, ?, ?, ?,
?, ?, ?, ? )
OR ( DT.statecode IN ( ? )
AND IT.statecode = ? ) )
AND DR.requesttime >= ?
AND DR.requesttime <= ? ))
GROUP BY DT.classid
ORDER BY DT.classid
查询 (2)
SELECT Count(DISTINCT DT.id)
FROM deptrans DT
inner join itemtotal IT
ON DT.id = IT.deposittransid
left outer join depreceipt DR
ON DR.deposittransid = DT.id
WHERE DT.bankaccountid IN ( ? )
AND (( ( DT.statecode IN ( ?, ?, ?, ?,
?, ?, ?, ? )
OR ( DT.statecode IN ( ? )
AND IT.statecode = ? ) )
AND DR.requesttime >= ?
AND DR.requesttime <= ? ))
来自上述查询的 3 个表(ITEMTOTAL IT、DEPTRANS DT 和 DEPRECEIPT DR ...resp)的索引,
IX1_ITEMTOTAL ID
IX2_ITEMTOTAL DEPOSITTRANSID
IX3_ITEMTOTAL USERID
IX4_ITEMTOTAL USERID, STATECODE
IX1_DEPTRANS ID
IX2_DEPTRANS CUSTOMERID
IX3_DEPTRANS LOCATIONID
IX4_DEPTRANS BANKACCOUNTID
IX5_DEPTRANS STATECODE
IX6_DEPTRANS TIMEZONEID
IX7_DEPTRANS LASTMODIFYDATE, RESOURCENAME
IX8_DEPTRANS AUTOCLOSESTATE
I035451031300_DEPTRANS_CDT_DNO CREATEDATE, DEPOSITNUMBER
I035938031400_DEPTRANS_RCPTRNO RECEIPTREFNUMBER
IX1_DEPRECEIPT ID
IX2_DEPRECEIPT DEPOSITTRANSID
IX3_DEPRECEIPT USERID, REQUESTTIME, DEPOSITTRANSID
I035451031300_DEPRECP_PDATE POSTINGTIME
解决方案
推荐阅读
- html - 在边框中间剪开
- linux - 在 Linux VM 上安装 pycryptodome
- python - 设置 QTreeWidget 等于列宽
- docker - Docker 检查不适用于 Jenkins 管道中的某些图像
- php - 使用未定义的常量页面 - 假定为“页面”(这将在 PHP 的未来版本中引发错误)
- reactjs - “ReactInstance”类型上不存在属性“selectionStart”。“组件”类型上不存在属性“selectionStart”
'.ts - r - 如何在 R 函数之间传递参数?
- r - 错误:变量名限制为 10000 字节
- javascript - 样式化组件仅将一些值传递给自定义组件
- c# - 如果从示例表中读取测试数据,源标签的意义何在?我收到错误“场景大纲...没有定义示例”