LoveYou3000 opened a new issue, #40332:
URL: https://github.com/apache/shardingsphere/issues/40332
## Feature Request
**For English only**, other languages will not be accepted.
### Is your feature request related to a problem?
Yes, it is a performance problem in the SQL binding engine, most visible on
wide tables where queries list many projection items.
In `ProjectionsSegmentBinder#bind`
(`infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/projection/ProjectionsSegmentBinder.java`
on master, introduced by #34151 and available since 5.5.2), the loop eagerly
builds `currentTableBinderContexts` on every iteration:
```java
for (ProjectionSegment each : segment.getProjections()) {
Multimap<CaseInsensitiveString, TableSegmentBinderContext>
currentTableBinderContexts = createCurrentTableBinderContexts(binderContext,
result.getProjections());
result.getProjections().add(bind(binderContext, boundTableSegment,
currentTableBinderContexts, tableBinderContexts, outerTableBinderContexts,
each));
}
```
However, `currentTableBinderContexts` is only consumed by the retry path of
the private `bind(...)` overload, which is taken only when the first binding
attempt throws `ColumnNotFoundException` (the "current select projection
reference" feature of #34151, e.g. `SELECT a + 1 AS b, b + 1 AS c FROM t`):
```java
try {
return bind(projectionSegment, binderContext, boundTableSegment,
tableBinderContexts, outerTableBinderContexts);
} catch (final ColumnNotFoundException ignored) {
...
return bind(projectionSegment, binderContext, boundTableSegment,
currentTableBinderContexts, outerTableBinderContexts);
}
```
In the common case where every projection binds successfully on the first
attempt, each eagerly built multimap is discarded without ever being read.
`createCurrentTableBinderContexts` is not cheap: it calls
`SubqueryTableBindUtils.createSubqueryProjections(result.getProjections(),
...)`, which iterates over all projections bound so far and allocates a new
`ColumnProjectionSegment` / `ColumnSegment` / `ColumnSegmentBoundInfo` /
`TableSegmentBoundInfo` for each one (recursively expanding the actual
projections of shorthand segments).
As a result, for a statement with `n` projection items the loop performs `1
+ 2 + ... + (n-1) = n(n-1)/2` projection conversions and object allocations
that are all thrown away, every time a statement context is created. For
example, a `SELECT` listing all 200 columns of a wide table wastes about 19,900
conversions per bind. ORM-generated queries, `INSERT ... SELECT` and explicit
column lists on wide tables are typical victims.
The same eager pattern was introduced once more for the `DISTINCT ON` items
loop in #39944.
### Describe the feature you would like.
Build `currentTableBinderContexts` lazily, i.e. only when the
`ColumnNotFoundException` retry path is actually taken.
`result.getProjections()` is only mutated after the inner `bind(...)`
returns, so computing the contexts inside the `catch` block observes exactly
the same list contents as the current eager computation. The change is
therefore behavior-preserving:
```java
for (ProjectionSegment each : segment.getProjections()) {
result.getProjections().add(bind(binderContext, boundTableSegment,
result.getProjections(), tableBinderContexts, outerTableBinderContexts, each));
}
private static ProjectionSegment bind(final SQLStatementBinderContext
binderContext, final TableSegment boundTableSegment,
final Collection<ProjectionSegment>
boundProjections,
final Multimap<CaseInsensitiveString,
TableSegmentBinderContext> tableBinderContexts,
final Multimap<CaseInsensitiveString,
TableSegmentBinderContext> outerTableBinderContexts,
final ProjectionSegment
projectionSegment) {
try {
return bind(projectionSegment, binderContext, boundTableSegment,
tableBinderContexts, outerTableBinderContexts);
} catch (final ColumnNotFoundException ignored) {
if (isWholeRowProjection(projectionSegment, binderContext,
boundTableSegment, tableBinderContexts)) {
return createWholeRowProjection((ColumnProjectionSegment)
projectionSegment);
}
return bind(projectionSegment, binderContext, boundTableSegment,
createCurrentTableBinderContexts(binderContext,
boundProjections), outerTableBinderContexts);
}
}
```
With this change the happy path (no `ColumnNotFoundException`) performs zero
extra work per projection, while statements that reference earlier projection
aliases in the same `SELECT` keep working unchanged (covered by the existing
tests of #34151). The same lazy treatment can be applied to the `DISTINCT ON`
loop added by #39944.
If the proposal looks good, I would like to submit a PR for it.
Environment: observed in 5.5.2, and the code is still present on master as
of today.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
