Dear All,
I am having the SQL execution performance issue. The followings are the SQL and
the result of SQL explain. But I have no ideas on how to tune the SQL to
improve.
Expect to receive your suggestions. Thanks.
Best Regards,
Thomas
=========SQL START================
SELECT
a.projectid,
a.ContractGUID,
c.CalID,
c.calperiod,
c.teamtype,
c.HKRatio,
d.MeasureMemberID
FROM
yj_contractinfo_doris a
JOIN yj_salestage_doris b ON b.CalID = '898' and a.ProjectID =
'26d614143fa73c16c4c8149455311e34'
-- AND a.projectid = b.projectid
AND b.contractguid = a.contractguid
JOIN yj_roompayment_doris c ON c.calid = '898'
-- AND a.projectid = c.projectid
AND c.contractguid = a.contractguid
AND c.ContractType = a.ContractType
-- AND b.CalID = c.CalID
AND c.CalPeriod = 'c709bdf3ff7da83e23e1a1fc1289b68e'
AND c.teamtype = 'ffab7099a45eb01583ea3b43faefad66'
AND c.CalID = '898'
JOIN yj_measure_limit d ON d.CalID = '898'
AND d.projectid = a.projectid
AND d.producttype = a.producttype
AND d.SaleStage = b.SaleStage
AND d.ContractType = c.ContractType
AND c.HKRatio >=d.min and
c.HKRatio<= d.max
===========SQL END=====================================
Explain SQL Result as following:
===========EXPLAIN START==================
PLAN FRAGMENT 0
OUTPUT EXPRS:`a`.`projectid` | `a`.`ContractGUID` | `c`.`CalID` |
`c`.`calperiod` | `c`.`teamtype` | `c`.`HKRatio` | `d`.`MeasureMemberID`
PARTITION: UNPARTITIONED
RESULT SINK
10:EXCHANGE
tuple ids: 1 2 3 0
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 10
UNPARTITIONED
6:HASH JOIN
| join op: INNER JOIN (BROADCAST)
| hash predicates:
| colocate: false, reason: left hash join node can not do colocate
| equal join conjunct: `b`.`contractguid` = `a`.`contractguid`
| equal join conjunct: `c`.`contractguid` = `a`.`contractguid`
| equal join conjunct: `c`.`ContractType` = `a`.`ContractType`
| equal join conjunct: `d`.`projectid` = `a`.`projectid`
| equal join conjunct: `d`.`producttype` = `a`.`producttype`
| tuple ids: 1 2 3 0
|
|----9:EXCHANGE
| tuple ids: 0
|
4:HASH JOIN
| join op: INNER JOIN (BROADCAST)
| hash predicates:
| colocate: false, reason: Node type not match
| equal join conjunct: `b`.`SaleStage` = `d`.`SaleStage`
| equal join conjunct: `c`.`ContractType` = `d`.`ContractType`
| other predicates: `c`.`HKRatio` >= `d`.`min`, `c`.`HKRatio` <= `d`.`max`
| tuple ids: 1 2 3
|
|----8:EXCHANGE
| tuple ids: 3
|
2:CROSS JOIN
| cross join:
| predicates is NULL. | tuple ids: 1 2
|
|----7:EXCHANGE
| tuple ids: 2
|
0:OlapScanNode
TABLE: yj_salestage_doris
PREAGGREGATION: OFF. Reason: No AggregateInfo
PREDICATES: `b`.`CalID` = 898.0
partitions=2/3
rollup: yj_salestage_doris
tabletRatio=24/24
tabletList=11523,11527,11531,11535,11539,11543,11547,11551,11555,11559 ...
cardinality=1199149
avgRowSize=21.937193
numNodes=3
tuple ids: 1
PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 09
UNPARTITIONED
5:OlapScanNode
TABLE: yj_contractinfo_doris
PREAGGREGATION: OFF. Reason: null
PREDICATES: `a`.`ProjectID` = '26d614143fa73c16c4c8149455311e34'
partitions=1/1
rollup: yj_contractinfo_doris
tabletRatio=1/12
tabletList=17970
cardinality=6092
avgRowSize=72.79629
numNodes=3
tuple ids: 0
PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 08
UNPARTITIONED
3:OlapScanNode
TABLE: yj_measure_limit
PREAGGREGATION: OFF. Reason: null
PREDICATES: `d`.`CalID` = 898.0
partitions=1/3
rollup: yj_measure_limit
tabletRatio=12/12
tabletList=20658,20662,20666,20670,20674,20678,20682,20686,20690,20694 ...
cardinality=90938
avgRowSize=5.368526
numNodes=3
tuple ids: 3
PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 07
UNPARTITIONED
1:OlapScanNode
TABLE: yj_roompayment_doris
PREAGGREGATION: OFF. Reason: null
PREDICATES: `c`.`calid` = 898.0, `c`.`CalPeriod` =
'c709bdf3ff7da83e23e1a1fc1289b68e', `c`.`teamtype` =
'ffab7099a45eb01583ea3b43faefad66', `c`.`CalID` = 898.0
partitions=2/3
rollup: yj_roompayment_doris
tabletRatio=24/24
tabletList=13675,13679,13683,13687,13691,13695,13699,13703,13707,13711 ...
cardinality=503798
avgRowSize=52.880646
numNodes=3
tuple ids: 2
===========EXPLAIN END============================
