[
https://issues.apache.org/jira/browse/CALCITE-4994?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17482830#comment-17482830
]
Jay Narale edited comment on CALCITE-4994 at 1/27/22, 1:23 AM:
---------------------------------------------------------------
[~julianhyde]
I quickly ran the benchmark with the lamda ( i commented out code around the
correlation part for now).
Its as expected for the larger columns its faster than previous but slower than
the map
SqlToRelBenchmark With Lambda
{noformat}
Benchmark (columnLength)
(length) Mode Cnt Score Error Units
SqlToRelBenchmark.parse 10
10000 avgt 10 864.354 ± 54.142 ms/op
SqlToRelBenchmark.parse:·gc.alloc.rate 10
10000 avgt 10 452.993 ± 18.077 MB/sec
SqlToRelBenchmark.parse:·gc.alloc.rate.norm 10
10000 avgt 10 659921742.400 ± 25767.271 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space 10
10000 avgt 10 436.142 ± 15.755 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space.norm 10
10000 avgt 10 635437056.000 ± 9228675.624 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space 10
10000 avgt 10 0.484 ± 0.675 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space.norm 10
10000 avgt 10 709928.800 ± 990794.310 B/op
SqlToRelBenchmark.parse:·gc.count 10
10000 avgt 10 10.000 counts
SqlToRelBenchmark.parse:·gc.time 10
10000 avgt 10 123.000 ms
SqlToRelBenchmark.parse 100
10000 avgt 10 1618.893 ± 36.368 ms/op
SqlToRelBenchmark.parse:·gc.alloc.rate 100
10000 avgt 10 293.675 ± 5.130 MB/sec
SqlToRelBenchmark.parse:·gc.alloc.rate.norm 100
10000 avgt 10 660880576.800 ± 15650.604 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space 100
10000 avgt 10 283.600 ± 4.794 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space.norm 100
10000 avgt 10 638215782.400 ± 3662040.546 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space 100
10000 avgt 10 0.435 ± 0.365 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space.norm 100
10000 avgt 10 977904.000 ± 822120.092 B/op
SqlToRelBenchmark.parse:·gc.count 100
10000 avgt 10 10.000 counts
SqlToRelBenchmark.parse:·gc.time 100
10000 avgt 10 106.000 ms
SqlToRelBenchmark.parse 1000
10000 avgt 10 9605.022 ± 263.164 ms/op
SqlToRelBenchmark.parse:·gc.alloc.rate 1000
10000 avgt 10 62.488 ± 1.636 MB/sec
SqlToRelBenchmark.parse:·gc.alloc.rate.norm 1000
10000 avgt 10 663950768.000 ± 15667.655 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space 1000
10000 avgt 10 59.648 ± 21.827 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space.norm 1000
10000 avgt 10 634703052.800 ± 235508467.343 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space 1000
10000 avgt 10 1.103 ± 0.995 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space.norm 1000
10000 avgt 10 11767007.200 ± 10718693.190 B/op
SqlToRelBenchmark.parse:·gc.count 1000
10000 avgt 10 24.000 counts
SqlToRelBenchmark.parse:·gc.time 1000
10000 avgt 10 261.000 {noformat}
SqlNodeToRelBenchmark With Lambda
{noformat}
Benchmark
(columnLength) (length) Mode Cnt Score Error Units
SqlNodeToRelBenchmark.convertToRel
10 10000 avgt 10 300.395 ± 30.364 ms/op
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate
10 10000 avgt 10 457.408 ± 13.567 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate.norm
10 10000 avgt 10 682826479.200 ± 15686.208 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space
10 10000 avgt 10 462.075 ± 200.634 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space.norm
10 10000 avgt 10 692007731.200 ± 320073587.742 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space
10 10000 avgt 10 1.969 ± 4.904 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space.norm
10 10000 avgt 10 2944764.800 ± 7408016.060 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.count
10 10000 avgt 10 11.000 counts
SqlNodeToRelBenchmark.convertToRel:·gc.time
10 10000 avgt 10 180.000 ms
SqlNodeToRelBenchmark.convertToRel
100 10000 avgt 10 612.111 ± 74.204 ms/op
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate
100 10000 avgt 10 260.242 ± 27.603 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate.norm
100 10000 avgt 10 665062728.800 ± 15703.902 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space
100 10000 avgt 10 270.294 ± 88.036 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space.norm
100 10000 avgt 10 699107555.200 ± 303664136.290 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space
100 10000 avgt 10 0.530 ± 0.812 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space.norm
100 10000 avgt 10 1384058.400 ± 2230790.576 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.count
100 10000 avgt 10 11.000 counts
SqlNodeToRelBenchmark.convertToRel:·gc.time
100 10000 avgt 10 172.000 ms
SqlNodeToRelBenchmark.convertToRel
1000 10000 avgt 10 3304.496 ± 206.641 ms/op
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate
1000 10000 avgt 10 59.267 ± 1.488 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate.norm
1000 10000 avgt 10 668750760.000 ± 15692.951 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space
1000 10000 avgt 10 59.205 ± 6.559 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space.norm
1000 10000 avgt 10 668361812.000 ± 78633524.696 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space
1000 10000 avgt 10 1.682 ± 0.648 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space.norm
1000 10000 avgt 10 18977920.000 ± 7239963.196 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.count
1000 10000 avgt 10 61.000 counts
SqlNodeToRelBenchmark.convertToRel:·gc.time
1000 10000 avgt 10 599.000 {noformat}
Summary
{noformat}
Column length 10 100 1,000
==================== ========= ========= ==========
Before change
Score Sql To Rel 856.562 1,722.243 11,692.547
Score SqlNode to Rel 303.435 649.239 4,987.583
After Memoization at Blackboard Change
Score Sql To Rel 1,097.489 1,574.153 8,805.355
Score SqlNode to Rel 288.835 509.465 2,701.474
After Lambda Change
Score Sql To Rel 864.354 1618.893 9605.022
Score SqlNode to Rel 300.395 612.111 3304.496
{noformat}
Any thoughts?
was (Author: jaynarale):
[~julianhyde]
I quickly ran the benchmark with the lamda ( i commented out code around the
correlation part for now).
Its as expected for the larger columns its faster than previous but slower than
the map
SqlToRelBenchmark With Lambda
{noformat}
Benchmark (columnLength)
(length) Mode Cnt Score Error Units
SqlToRelBenchmark.parse 10
10000 avgt 10 864.354 ± 54.142 ms/op
SqlToRelBenchmark.parse:·gc.alloc.rate 10
10000 avgt 10 452.993 ± 18.077 MB/sec
SqlToRelBenchmark.parse:·gc.alloc.rate.norm 10
10000 avgt 10 659921742.400 ± 25767.271 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space 10
10000 avgt 10 436.142 ± 15.755 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space.norm 10
10000 avgt 10 635437056.000 ± 9228675.624 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space 10
10000 avgt 10 0.484 ± 0.675 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space.norm 10
10000 avgt 10 709928.800 ± 990794.310 B/op
SqlToRelBenchmark.parse:·gc.count 10
10000 avgt 10 10.000 counts
SqlToRelBenchmark.parse:·gc.time 10
10000 avgt 10 123.000 ms
SqlToRelBenchmark.parse 100
10000 avgt 10 1618.893 ± 36.368 ms/op
SqlToRelBenchmark.parse:·gc.alloc.rate 100
10000 avgt 10 293.675 ± 5.130 MB/sec
SqlToRelBenchmark.parse:·gc.alloc.rate.norm 100
10000 avgt 10 660880576.800 ± 15650.604 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space 100
10000 avgt 10 283.600 ± 4.794 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space.norm 100
10000 avgt 10 638215782.400 ± 3662040.546 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space 100
10000 avgt 10 0.435 ± 0.365 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space.norm 100
10000 avgt 10 977904.000 ± 822120.092 B/op
SqlToRelBenchmark.parse:·gc.count 100
10000 avgt 10 10.000 counts
SqlToRelBenchmark.parse:·gc.time 100
10000 avgt 10 106.000 ms
SqlToRelBenchmark.parse 1000
10000 avgt 10 9605.022 ± 263.164 ms/op
SqlToRelBenchmark.parse:·gc.alloc.rate 1000
10000 avgt 10 62.488 ± 1.636 MB/sec
SqlToRelBenchmark.parse:·gc.alloc.rate.norm 1000
10000 avgt 10 663950768.000 ± 15667.655 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space 1000
10000 avgt 10 59.648 ± 21.827 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Eden_Space.norm 1000
10000 avgt 10 634703052.800 ± 235508467.343 B/op
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space 1000
10000 avgt 10 1.103 ± 0.995 MB/sec
SqlToRelBenchmark.parse:·gc.churn.PS_Survivor_Space.norm 1000
10000 avgt 10 11767007.200 ± 10718693.190 B/op
SqlToRelBenchmark.parse:·gc.count 1000
10000 avgt 10 24.000 counts
SqlToRelBenchmark.parse:·gc.time 1000
10000 avgt 10 261.000 {noformat}
SqlNodeToRelBenchmark With Lambda
{noformat}
Benchmark
(columnLength) (length) Mode Cnt Score Error Units
SqlNodeToRelBenchmark.convertToRel
10 10000 avgt 10 300.395 ± 30.364 ms/op
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate
10 10000 avgt 10 457.408 ± 13.567 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate.norm
10 10000 avgt 10 682826479.200 ± 15686.208 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space
10 10000 avgt 10 462.075 ± 200.634 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space.norm
10 10000 avgt 10 692007731.200 ± 320073587.742 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space
10 10000 avgt 10 1.969 ± 4.904 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space.norm
10 10000 avgt 10 2944764.800 ± 7408016.060 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.count
10 10000 avgt 10 11.000 counts
SqlNodeToRelBenchmark.convertToRel:·gc.time
10 10000 avgt 10 180.000 ms
SqlNodeToRelBenchmark.convertToRel
100 10000 avgt 10 612.111 ± 74.204 ms/op
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate
100 10000 avgt 10 260.242 ± 27.603 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate.norm
100 10000 avgt 10 665062728.800 ± 15703.902 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space
100 10000 avgt 10 270.294 ± 88.036 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space.norm
100 10000 avgt 10 699107555.200 ± 303664136.290 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space
100 10000 avgt 10 0.530 ± 0.812 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space.norm
100 10000 avgt 10 1384058.400 ± 2230790.576 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.count
100 10000 avgt 10 11.000 counts
SqlNodeToRelBenchmark.convertToRel:·gc.time
100 10000 avgt 10 172.000 ms
SqlNodeToRelBenchmark.convertToRel
1000 10000 avgt 10 3304.496 ± 206.641 ms/op
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate
1000 10000 avgt 10 59.267 ± 1.488 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.alloc.rate.norm
1000 10000 avgt 10 668750760.000 ± 15692.951 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space
1000 10000 avgt 10 59.205 ± 6.559 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Eden_Space.norm
1000 10000 avgt 10 668361812.000 ± 78633524.696 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space
1000 10000 avgt 10 1.682 ± 0.648 MB/sec
SqlNodeToRelBenchmark.convertToRel:·gc.churn.PS_Survivor_Space.norm
1000 10000 avgt 10 18977920.000 ± 7239963.196 B/op
SqlNodeToRelBenchmark.convertToRel:·gc.count
1000 10000 avgt 10 61.000 counts
SqlNodeToRelBenchmark.convertToRel:·gc.time
1000 10000 avgt 10 599.000 {noformat}
{noformat}
Column length 10 100 1,000
==================== ========= ========= ==========
Before change
Score Sql To Rel 856.562 1,722.243 11,692.547
Score SqlNode to Rel 303.435 649.239 4,987.583
After Memoization at Blackboard Change
Score Sql To Rel 1,097.489 1,574.153 8,805.355
Score SqlNode to Rel 288.835 509.465 2,701.474
After Lambda Change
Score Sql To Rel 864.354 1618.893 9605.022
Score SqlNode to Rel 300.395 612.111 3304.496
{noformat}
Any thoughts?
> SqlToRelConverter creates FieldMap for every Identifier Instead of Memoizing
> it
> -------------------------------------------------------------------------------
>
> Key: CALCITE-4994
> URL: https://issues.apache.org/jira/browse/CALCITE-4994
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Jay Narale
> Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> When converting from Sql To Rel, In SqlToRelConverter for every single
> instance of an identifier we create a new map in
> *_org.apache.calcite.sql2rel.SqlToRelConverter.Blackboard#lookupExp_*
>
> {code:java}
> final Map<String, Integer> fieldOffsets = new HashMap<>();
> for (RelDataTypeField f : resolve.rowType().getFieldList()) {
> if (!fieldOffsets.containsKey(f.getName())) {
> fieldOffsets.put(f.getName(), f.getIndex());
> }
> }
> final Map<String, Integer> map = ImmutableMap.copyOf(fieldOffsets);{code}
>
> So for a Sql Query
> {code:java}
> SELECT name, nation FROM customer{code}
> We would do the above operation twice.
> Memoization of this information will improve performance.
> In my database, I had observed that for a large table involving 1200 columns
> and a huge select having multiple expressions and operators, this part was a
> bottleneck.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
