[
https://issues.apache.org/jira/browse/CALCITE-7918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18138668#comment-18138668
]
Darpan Lunagariya (e6data) edited comment on CALCITE-7918 at 11/5/26, 11:40 AM:
--------------------------------------------------------------------------------
Validation time micro-benchmark:
{noformat}
Benchmark (caseSensitive) (columnLength) Mode Cnt
Score Error Units
WideRowValidationBenchmark.validate true 10 avgt 10
0.023 ± 0.001 ms/op
WideRowValidationBenchmark.validate true 100 avgt 10
0.256 ± 0.005 ms/op
WideRowValidationBenchmark.validate true 1000 avgt 10
8.486 ± 0.249 ms/op
WideRowValidationBenchmark.validate true 4096 avgt 10
130.773 ± 4.849 ms/op
WideRowValidationBenchmark.validate true 10000 avgt 10
735.808 ± 10.048 ms/op
WideRowValidationBenchmark.validate false 10 avgt 10
0.029 ± 0.001 ms/op
WideRowValidationBenchmark.validate false 100 avgt 10
0.704 ± 0.049 ms/op
WideRowValidationBenchmark.validate false 1000 avgt 10
47.814 ± 3.774 ms/op
WideRowValidationBenchmark.validate false 4096 avgt 10
767.352 ± 26.229 ms/op
WideRowValidationBenchmark.validate false 10000 avgt 10
5138.961 ± 130.623 ms/op
{noformat}
After change
{noformat}
Benchmark (caseSensitive) (columnLength) Mode Cnt
Score Error Units
WideRowValidationBenchmark.validate true 10 avgt 10
0.023 ± 0.001 ms/op
WideRowValidationBenchmark.validate true 100 avgt 10
0.183 ± 0.005 ms/op
WideRowValidationBenchmark.validate true 1000 avgt 10
3.255 ± 0.199 ms/op
WideRowValidationBenchmark.validate true 4096 avgt 10
40.016 ± 1.316 ms/op
WideRowValidationBenchmark.validate true 10000 avgt 10
191.094 ± 29.125 ms/op
WideRowValidationBenchmark.validate false 10 avgt 10
0.031 ± 0.001 ms/op
WideRowValidationBenchmark.validate false 100 avgt 10
0.283 ± 0.010 ms/op
WideRowValidationBenchmark.validate false 1000 avgt 10
7.490 ± 0.261 ms/op
WideRowValidationBenchmark.validate false 4096 avgt 10
99.548 ± 2.673 ms/op
WideRowValidationBenchmark.validate false 10000 avgt 10
564.114 ± 41.202 ms/op
{noformat}
I used following SQL for this, just changed the number of fields.
{code:sql}
SELECT T.c0, T.c1, T.c2
FROM (VALUES (0, 1, 2)) AS T(C0, C1, C2)
{code}
was (Author: darpan):
Validation time micro-benchmark:
||Columns / references||Case sensitive||Baseline ms/op||PR ms/op||Baseline /
PR||
| 10 | true | 0.02315 ± 0.000554 | 0.02274 ± 0.00109 | 1.02x |
| 100 | true | 0.2565 ± 0.00467 | 0.1832 ± 0.00472 | 1.40x |
| 1,000 | true | 8.486 ± 0.249 | 3.255 ± 0.199 | 2.61x |
| 4,096 | true | 130.8 ± 4.85 | 40.02 ± 1.32 | 3.27x |
| 10,000 | true | 735.8 ± 10 | 191.1 ± 29.1 | 3.85x |
| 10 | false | 0.02907 ± 0.000447 | 0.03094 ± 0.000684 | 0.94x |
| 100 | false | 0.7042 ± 0.0488 | 0.2826 ± 0.0103 | 2.49x |
| 1,000 | false | 47.81 ± 3.77 | 7.49 ± 0.261 | 6.38x |
| 4,096 | false | 767.4 ± 26.2 | 99.55 ± 2.67 | 7.71x |
| 10,000 | false | 5139 ± 131 | 564.1 ± 41.2 | 9.11x |
I used following SQL for this, just changed the number of fields.
{code:sql}
SELECT T.c0, T.c1, T.c2
FROM (VALUES (0, 1, 2)) AS T(C0, C1, C2)
{code}
> SQL validation takes too long for wider row types
> -------------------------------------------------
>
> Key: CALCITE-7918
> URL: https://issues.apache.org/jira/browse/CALCITE-7918
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.42.0
> Reporter: Darpan Lunagariya (e6data)
> Assignee: Darpan Lunagariya (e6data)
> Priority: Minor
> Labels: pull-request-available
>
> h2. There are 2 problems, which is contributing to this huge latency:
> * *Ambiguity checking:* {{DelegatingScope.hasAmbiguousField}} scans every
> field to count matching names, even when ordinary case-sensitive lookup
> already uses an index.
> * *Case-insensitive lookup:* {{RelDataTypeImpl.getField}} only uses the
> existing field-name map when {{caseSensitive=true}}. Case-insensitive
> requests scan the field list instead.
> h2. Reproduction
> Both examples are self-contained and require no registered tables or data.
> Parse and validate using:
> {code:java}
> SqlParser.config()
> .withLex(Lex.JAVA)
> .withCaseSensitive(true)
> {code}
> Use more than 20 fields because the existing {{RelRecordType}} field-name map
> is only created above that threshold. The following examples use 21 fields;
> increase the width to observe the scaling.
> h3. 1. Ambiguity checking ignores indexed lookup
> {code:sql}
> SELECT
> T.C0, T.C1, T.C2, T.C3, T.C4, T.C5, T.C6,
> T.C7, T.C8, T.C9, T.C10, T.C11, T.C12, T.C13,
> T.C14, T.C15, T.C16, T.C17, T.C18, T.C19, T.C20
> FROM (
> VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
> 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
> ) AS T(
> C0, C1, C2, C3, C4, C5, C6,
> C7, C8, C9, C10, C11, C12, C13,
> C14, C15, C16, C17, C18, C19, C20
> )
> {code}
> Ordinary field lookups use the existing exact-name map. However,
> {{fullyQualify}} also calls {{hasAmbiguousField}}, which scans the entire row
> for each resolved identifier.
> h3. 2. Case-insensitive lookup bypasses indexing
> {code:sql}
> SELECT
> T.R['c0'] AS C0, T.R['c1'] AS C1,
> T.R['c2'] AS C2, T.R['c3'] AS C3,
> T.R['c4'] AS C4, T.R['c5'] AS C5,
> T.R['c6'] AS C6, T.R['c7'] AS C7,
> T.R['c8'] AS C8, T.R['c9'] AS C9,
> T.R['c10'] AS C10, T.R['c11'] AS C11,
> T.R['c12'] AS C12, T.R['c13'] AS C13,
> T.R['c14'] AS C14, T.R['c15'] AS C15,
> T.R['c16'] AS C16, T.R['c17'] AS C17,
> T.R['c18'] AS C18, T.R['c19'] AS C19,
> T.R['c20'] AS C20
> FROM (
> SELECT CAST(
> ROW(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
> 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
> AS ROW(
> C0 INTEGER, C1 INTEGER, C2 INTEGER,
> C3 INTEGER, C4 INTEGER, C5 INTEGER,
> C6 INTEGER, C7 INTEGER, C8 INTEGER,
> C9 INTEGER, C10 INTEGER, C11 INTEGER,
> C12 INTEGER, C13 INTEGER, C14 INTEGER,
> C15 INTEGER, C16 INTEGER, C17 INTEGER,
> C18 INTEGER, C19 INTEGER, C20 INTEGER
> )
> ) AS R
> ) AS T
> {code}
> h2. Observed scaling
> The following counts were measured by observing field-name reads during
> validation. The fixed counts include one-time index construction.
> || Fields || Original ambiguity scans, example 1 || Original insensitive
> scans, example 2 ||
> | 128 | 49,152 | 16,512 |
> | 256 | 196,608 | 65,792 |
> | 512 | 786,432 | 262,656 |
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
