Is PlSql slow?

[kaby76]

This is a performance analysis of the grammars-v4/sql/plsql grammar, raised in multiple places:

• here (@nIxedoahz see the analysis below)
• here
• here
• here

Runtimes for C# target of grammar on regression tests

I've built and ran for C# the grammar on all the tests given (trgen; cd Generated; make; make test). This is a table of the results.

Test file	Time (s)	Line count	Word count	Char count
../examples/aggregate01.sql	00:00:01.3490301	72	291	3438
../examples/alter_database.sql	00:00:00.1432586	49	153	1416
../examples/alter_library.sql	00:00:00.0996223	1	4	37
../examples/alter_mv.sql	00:00:00.4469603	16	52	483
../examples/alter_mv_log.sql	00:00:00.1555638	5	19	145
../examples/alter_table.sql	00:00:00.5617908	112	325	2743
../examples/alter_tablespaces.sql	00:00:00.1230353	36	87	782
../examples/alter_view.sql	00:00:00.1466553	4	8	77
../examples/analytic_query.sql	00:00:02.2368695	101	548	4362
../examples/analyze.sql	00:00:00.1830864	9	42	356
../examples/associate_statistics.sql	00:00:00.1119825	2	16	139
../examples/audit.sql	00:00:00.1193848	27	73	566
../examples/bindvar01.sql	00:00:00.3967762	14	23	95
../examples/bindvar02.sql	00:00:00.5243425	6	16	60
../examples/bindvar03.sql	00:00:01.1061154	18	78	528
../examples/bindvar04.sql	00:00:00.6477942	7	15	130
../examples/bindvar05.sql	00:00:01.0832202	13	54	420
../examples/bulk_collect_fetch_clause01.sql	00:00:00.7730722	20	61	564
../examples/case_when01.sql	00:00:00.7067105	9	21	141
../examples/case_when02.sql	00:00:01.0867377	32	94	837
../examples/case_when03.sql	00:00:00.6842898	9	24	120
../examples/case_when04.sql	00:00:00.8593853	3	25	128
../examples/case_when05.sql	00:00:01.0492397	11	68	376
../examples/case_when06.sql	00:00:00.5441701	7	20	111
../examples/case_when07.sql	00:00:00.7217792	5	16	168
../examples/cast_multiset01.sql	00:00:00.8050218	7	21	231
../examples/cast_multiset02.sql	00:00:00.5068456	6	15	117
../examples/cast_multiset03.sql	00:00:00.5942158	7	21	117
../examples/cast_multiset04.sql	00:00:00.7722829	2	22	168
../examples/cast_multiset05.sql	00:00:00.4503307	6	7	45
../examples/cast_multiset06.sql	00:00:00.8221913	4	11	102
../examples/cast_multiset07.sql	00:00:01.5258295	39	59	629
../examples/cast_multiset08.sql	00:00:00.4820737	1	8	78
../examples/cluster.sql	00:00:00.4380997	19	58	479
../examples/columns01.sql	00:00:00.6809208	7	15	61
../examples/comments.sql	00:00:00.2712567	7	22	111
../examples/comment_on.sql	00:00:00.1725762	18	82	596
../examples/condition01.sql	00:00:00.6629690	7	21	180
../examples/condition02.sql	00:00:00.7399898	8	30	188
../examples/condition03.sql	00:00:00.8389263	10	34	206
../examples/condition04.sql	00:00:00.8194957	36	202	835
../examples/condition05.sql	00:00:00.6641943	20	166	678
../examples/condition06.sql	00:00:00.8752751	14	58	470
../examples/condition07.sql	00:00:00.5916557	5	16	85
../examples/condition08.sql	00:00:00.4733250	5	20	99
../examples/condition09.sql	00:00:00.9558291	16	45	278
../examples/condition10.sql	00:00:00.6751360	15	23	121
../examples/condition11.sql	00:00:01.0006593	7	23	146
../examples/condition12.sql	00:00:00.5851656	11	31	178
../examples/condition14.sql	00:00:00.5206723	2	9	56
../examples/condition15.sql	00:00:00.8795897	13	21	164
../examples/condition16.sql	00:00:00.5907209	2	11	81
../examples/connect_by01.sql	00:00:00.7815874	20	66	479
../examples/connect_by02.sql	00:00:00.8123043	6	26	203
../examples/connect_by03.sql	00:00:00.8339757	7	26	222
../examples/connect_by04.sql	00:00:00.8892947	7	22	194
../examples/connect_by05.sql	00:00:00.9627191	9	36	306
../examples/context.sql	00:00:00.1099019	3	5	44
../examples/create_package.sql	00:00:00.1439130	4	14	128
../examples/create_procedure.sql	00:00:00.3677569	6	14	122
../examples/create_synonym01.sql	00:00:00.1123516	1	6	43
../examples/create_synonym02.sql	00:00:00.1236984	0	5	63
../examples/create_synonym03.sql	00:00:00.1061733	0	8	54
../examples/create_table.sql	00:00:00.6806668	80	247	2066
../examples/create_tablespace.sql	00:00:00.1148922	57	172	1490
../examples/create_type.sql	00:00:00.1294192	15	34	295
../examples/ctas.sql	00:00:00.4330298	3	18	108
../examples/datetime01.sql	00:00:00.6886663	1	16	171
../examples/datetime02.sql	00:00:01.6083133	30	156	1333
../examples/datetime03.sql	00:00:00.4921600	3	9	75
../examples/datetime04.sql	00:00:00.4442125	4	6	50
../examples/datetime05.sql	00:00:00.3969652	1	5	35
../examples/dblink01.sql	00:00:00.5611845	5	10	136
../examples/directory.sql	00:00:00.0948090	3	12	117
../examples/drop_table.sql	00:00:00.1389047	2	7	48
../examples/drop_view.sql	00:00:00.1447711	2	8	59
../examples/explain01.sql	00:00:00.5369652	8	30	261
../examples/fetch_clause01.sql	00:00:00.3928874	4	12	65
../examples/fetch_clause02.sql	00:00:00.3894017	4	13	71
../examples/fetch_clause03.sql	00:00:00.5359106	5	18	108
../examples/fetch_clause04.sql	00:00:00.6275695	3	20	137
../examples/flashback01.sql	00:00:00.7041924	1	12	102
../examples/for_update01.sql	00:00:00.4285330	4	11	81
../examples/for_update02.sql	00:00:00.4780444	4	11	93
../examples/for_update03.sql	00:00:00.4796006	4	13	108
../examples/for_update04.sql	00:00:00.4778388	3	14	114
../examples/for_update05.sql	00:00:00.4592954	3	15	115
../examples/for_update06.sql	00:00:00.4809917	3	15	119
../examples/for_update07.sql	00:00:00.4747520	3	17	117
../examples/for_update08.sql	00:00:00.7689295	5	19	140
../examples/function01.sql	00:00:00.5976722	2	30	173
../examples/function02.sql	00:00:00.5814859	1	4	24
../examples/function03.sql	00:00:00.5399582	4	6	42
../examples/function04.sql	00:00:00.5549295	1	7	31
../examples/function05.sql	00:00:00.6299871	3	8	69
../examples/function06.sql	00:00:00.1422296	1	4	36
../examples/groupby01.sql	00:00:01.2129056	9	86	721
../examples/groupby02.sql	00:00:01.0839060	10	60	587
../examples/groupby03.sql	00:00:00.4666862	4	12	49
../examples/groupby04.sql	00:00:00.4837334	3	11	49
../examples/groupby05.sql	00:00:00.5581634	4	12	61
../examples/groupby06.sql	00:00:00.9869402	15	48	545
../examples/groupby07.sql	00:00:00.9609442	8	37	206
../examples/index.sql	00:00:00.6203230	83	207	1843
../examples/insert_statement.sql	00:00:00.5442488	5	18	141
../examples/interval01.sql	00:00:00.5282950	2	13	86
../examples/interval02.sql	00:00:00.4699590	1	6	35
../examples/interval03.sql	00:00:00.7879703	21	69	485
../examples/interval04.sql	00:00:00.4908686	1	8	55
../examples/join01.sql	00:00:00.9446105	5	24	211
../examples/join02.sql	00:00:00.8540624	6	19	165
../examples/join03.sql	00:00:00.7995217	6	19	174
../examples/join04.sql	00:00:00.7740030	6	16	160
../examples/join05.sql	00:00:00.8038421	8	28	276
../examples/join06.sql	00:00:00.5904815	5	23	134
../examples/join07.sql	00:00:00.5197979	5	15	78
../examples/join08.sql	00:00:00.6029040	8	48	304
../examples/join09.sql	00:00:00.5908849	8	54	337
../examples/join10.sql	00:00:00.4792470	5	17	105
../examples/join11.sql	00:00:00.4021758	1	12	57
../examples/join12.sql	00:00:00.4242671	2	11	62
../examples/join13.sql	00:00:00.4137118	4	12	80
../examples/join14.sql	00:00:00.4977412	9	29	163
../examples/join15.sql	00:00:00.6622843	3	19	109
../examples/join16.sql	00:00:00.5215516	5	23	144
../examples/join17.sql	00:00:00.6089157	7	21	83
../examples/join18.sql	00:00:00.7885889	14	40	300
../examples/join19.sql	00:00:00.3681770	1	10	50
../examples/join20.sql	00:00:00.3632578	1	8	45
../examples/join21.sql	00:00:00.3688177	2	7	47
../examples/keywordasidentifier01.sql	00:00:00.5272643	1	6	44
../examples/keywordasidentifier02.sql	00:00:00.4630451	1	4	26
../examples/keywordasidentifier03.sql	00:00:01.2833724	24	104	1644
../examples/keywordasidentifier04.sql	00:00:00.6108170	6	18	138
../examples/keywordasidentifier05.sql	00:00:00.4306889	0	4	31
../examples/keywordasidentifier06.sql	00:00:00.5911378	7	16	107
../examples/least_greatest.sql	00:00:00.6797235	0	15	101
../examples/lexer01.sql	00:00:00.4245786	2	21	67
../examples/lexer02.sql	00:00:00.4102263	2	7	31
../examples/lexer03.sql	00:00:00.5556914	2	23	81
../examples/lexer04.sql	00:00:00.7196497	1	6	78
../examples/lexer05.sql	00:00:00.7396222	1	6	78
../examples/like01.sql	00:00:00.5142246	5	13	92
../examples/materialized_views.sql	00:00:01.4031058	81	305	2663
../examples/max_001.sql	00:00:00.6275078	5	9	63
../examples/merge01.sql	00:00:00.7587738	9	41	307
../examples/merge02.sql	00:00:00.7236920	9	41	307
../examples/merge03.sql	00:00:01.4379273	17	103	1073
../examples/merge04.sql	00:00:01.4573441	18	108	1098
../examples/merge05.sql	00:00:00.6366870	7	39	316
../examples/model_clause01.sql	00:00:00.7387228	18	54	405
../examples/model_clause02.sql	00:00:00.8688612	16	46	456
../examples/model_clause03.sql	00:00:00.7030011	15	38	311
../examples/model_clause04.sql	00:00:00.6965309	14	38	314
../examples/model_clause05.sql	00:00:00.7152639	14	39	315
../examples/numbers01.sql	00:00:00.5379194	14	55	279
../examples/nvl_001.sql	00:00:00.6716340	3	15	139
../examples/object_access01.sql	00:00:00.9006910	18	38	566
../examples/offset_clause.sql	00:00:00.3803529	3	7	40
../examples/order_by01.sql	00:00:00.4353767	1	7	30
../examples/order_by02.sql	00:00:00.4325198	1	8	34
../examples/order_by03.sql	00:00:00.7696056	1	9	50
../examples/order_by04.sql	00:00:00.5698668	1	12	57
../examples/order_by05.sql	00:00:00.5892064	0	22	106
../examples/order_by06.sql	00:00:00.4369676	1	14	58
../examples/order_by07.sql	00:00:02.1922784	106	333	3584
../examples/packagebody_function.sql	00:00:00.2791900	12	47	374
../examples/packagespec_function.sql	00:00:00.1679120	4	24	174
../examples/package_body.sql	00:00:00.4346986	7	19	162
../examples/partitioned_tables.sql	00:00:00.5383462	154	528	5915
../examples/pivot01.sql	00:00:00.5447926	6	19	152
../examples/pivot02.sql	00:00:00.6728442	11	29	211
../examples/pivot03.sql	00:00:00.6855695	11	26	176
../examples/pivot04.sql	00:00:00.5504477	20	28	152
../examples/pivot05.sql	00:00:00.5663731	5	22	264
../examples/pivot06.sql	00:00:00.5904582	5	25	329
../examples/pivot07.sql	00:00:00.6662238	5	28	200
../examples/pivot08.sql	00:00:00.6426303	3	24	165
../examples/pivot09.sql	00:00:00.8093400	19	50	283
../examples/pivot10.sql	00:00:00.7678773	19	50	281
../examples/pivot11.sql	00:00:00.7955773	20	50	282
../examples/pivot12.sql	00:00:00.5756645	17	37	303
../examples/query_factoring01.sql	00:00:00.9450603	16	55	453
../examples/query_factoring02.sql	00:00:00.9723114	18	62	548
../examples/query_factoring03.sql	00:00:00.8383655	16	52	439
../examples/query_factoring04.sql	00:00:01.0059223	18	64	551
../examples/query_factoring05.sql	00:00:01.1223549	27	90	489
../examples/query_factoring06.sql	00:00:00.8043161	15	42	397
../examples/query_factoring07.sql	00:00:02.4968959	78	562	4299
../examples/query_factoring08.sql	00:00:00.5995364	10	40	168
../examples/query_factoring09.sql	00:00:00.6416881	8	29	153
../examples/query_factoring10.sql	00:00:01.0005138	33	109	742
../examples/query_factoring11.sql	00:00:01.0053287	9	36	296
../examples/rename_objects.sql	00:00:00.0957973	2	15	94
../examples/round_001.sql	00:00:00.7565748	7	15	138
../examples/round_decode_001.sql	00:00:00.8421668	10	19	178
../examples/row_number_001.sql	00:00:00.6351663	4	16	140
../examples/sample01.sql	00:00:00.5391314	4	20	104
../examples/select_package_function.sql	00:00:00.8273890	0	9	80
../examples/simple02.sql	00:00:00.4782489	3	11	92
../examples/simple03.sql	00:00:00.4785302	3	13	107
../examples/simple04.sql	00:00:00.3097828	11	19	95
../examples/simple05.sql	00:00:00.3070012	9	17	85
../examples/simple06.sql	00:00:00.2991053	2	9	41
../examples/simple07.sql	00:00:00.2682209	3	8	39
../examples/simple08.sql	00:00:00.6262217	2	11	52
../examples/simple09.sql	00:00:00.5203835	7	16	141
../examples/simple10.sql	00:00:00.4887892	1	6	27
../examples/simple11.sql	00:00:00.3296639	1	4	21
../examples/simple12.sql	00:00:00.7415169	7	9	72
../examples/simple13.sql	00:00:00.3802526	1	8	42
../examples/string01.sql	00:00:00.4106192	17	56	326
../examples/to_char.sql	00:00:00.5171524	0	11	110
../examples/to_char_001.sql	00:00:00.5052671	0	9	68
../examples/to_char_002.sql	00:00:00.4765708	0	6	61
../examples/to_date.sql	00:00:00.7907413	8	68	592
../examples/truncate_table.sql	00:00:00.1489097	0	3	20
../examples/unified.sql	00:00:00.1015683	3	9	57
../examples/union01.sql	00:00:00.5116526	7	42	256
../examples/union02.sql	00:00:00.4479565	4	12	90
../examples/union03.sql	00:00:00.4667899	6	14	95
../examples/union04.sql	00:00:00.5063244	45	113	810
../examples/union05.sql	00:00:00.4363466	30	66	279
../examples/union06.sql	00:00:00.7287402	35	62	611
../examples/union07.sql	00:00:01.2148582	47	171	3881
../examples/union08.sql	00:00:00.4164221	5	18	87
../examples/union09.sql	00:00:00.6777395	27	56	1041
../examples/union10.sql	00:00:00.6552116	14	45	2085
../examples/views.sql	00:00:01.1441570	59	203	1731
../examples/xmltable01.sql	00:00:00.9363135	12	32	527
../examples/xmltable02.sql	00:00:00.7286333	2	7	107
../examples/xmltable03.sql	00:00:00.7271640	16	52	757

Observations:

• Most tests are under one second.
• Only 27 of 232 ran over one second.

[kaby76]

Runtimes for C# target of combined, predicate-free grammar on regression tests

Combining and removing all semantic predicates from the grammar (see attached), I reran the tests. Two files were removed from the test due to parse errors.

Test file	Time (s)	Line count	Word count	Char count
../examples/aggregate01.sql	00:00:01.3266260	72	291	3438
../examples/alter_database.sql	00:00:00.1350081	49	153	1416
../examples/alter_library.sql	00:00:00.0969811	1	4	37
../examples/alter_mv.sql	00:00:00.4495350	16	52	483
../examples/alter_mv_log.sql	00:00:00.1486535	5	19	145
../examples/alter_table.sql	00:00:00.5799460	112	325	2743
../examples/alter_tablespaces.sql	00:00:00.1162886	36	87	782
../examples/alter_view.sql	00:00:00.1398758	4	8	77
../examples/analytic_query.sql	00:00:02.4352579	101	548	4362
../examples/analyze.sql	00:00:00.1843679	9	42	356
../examples/associate_statistics.sql	00:00:00.1146528	2	16	139
../examples/audit.sql	00:00:00.1189127	27	73	566
../examples/bindvar01.sql	00:00:00.3950475	14	23	95
../examples/bindvar02.sql	00:00:00.5276927	6	16	60
../examples/bindvar03.sql	00:00:01.0944524	18	78	528
../examples/bindvar04.sql	00:00:00.6363985	7	15	130
../examples/bindvar05.sql	00:00:01.0390227	13	54	420
../examples/bulk_collect_fetch_clause01.sql	00:00:00.8685580	20	61	564
../examples/case_when01.sql	00:00:00.7718619	9	21	141
../examples/case_when02.sql	00:00:01.1938723	32	94	837
../examples/case_when03.sql	00:00:00.6861213	9	24	120
../examples/case_when04.sql	00:00:00.7862531	3	25	128
../examples/case_when05.sql	00:00:01.0045796	11	68	376
../examples/case_when06.sql	00:00:00.5581720	7	20	111
../examples/case_when07.sql	00:00:00.7144197	5	16	168
../examples/cast_multiset01.sql	00:00:00.8037938	7	21	231
../examples/cast_multiset02.sql	00:00:00.5281802	6	15	117
../examples/cast_multiset03.sql	00:00:00.5633789	7	21	117
../examples/cast_multiset04.sql	00:00:00.8129370	2	22	168
../examples/cast_multiset05.sql	00:00:00.4488507	6	7	45
../examples/cast_multiset06.sql	00:00:00.8485703	4	11	102
../examples/cast_multiset07.sql	00:00:01.5529468	39	59	629
../examples/cast_multiset08.sql	00:00:00.4967553	1	8	78
../examples/cluster.sql	00:00:00.4552496	19	58	479
../examples/columns01.sql	00:00:00.6903809	7	15	61
../examples/comments.sql	00:00:00.2924093	7	22	111
../examples/comment_on.sql	00:00:00.1838695	18	82	596
../examples/condition01.sql	00:00:00.7105090	7	21	180
../examples/condition02.sql	00:00:00.7919739	8	30	188
../examples/condition03.sql	00:00:00.7918992	10	34	206
../examples/condition04.sql	00:00:00.7571345	36	202	835
../examples/condition05.sql	00:00:00.7345432	20	166	678
../examples/condition06.sql	00:00:00.8884329	14	58	470
../examples/condition07.sql	00:00:00.6717972	5	16	85
../examples/condition08.sql	00:00:00.4922992	5	20	99
../examples/condition09.sql	00:00:00.9630270	16	45	278
../examples/condition10.sql	00:00:00.6727426	15	23	121
../examples/condition11.sql	00:00:00.9096712	7	23	146
../examples/condition12.sql	00:00:00.6662508	11	31	178
../examples/condition14.sql	00:00:00.5443450	2	9	56
../examples/condition15.sql	00:00:00.8362636	13	21	164
../examples/condition16.sql	00:00:00.5939074	2	11	81
../examples/connect_by01.sql	00:00:00.8071734	20	66	479
../examples/connect_by02.sql	00:00:00.7991508	6	26	203
../examples/connect_by03.sql	00:00:00.8261579	7	26	222
../examples/connect_by04.sql	00:00:00.7891407	7	22	194
../examples/connect_by05.sql	00:00:00.9325283	9	36	306
../examples/context.sql	00:00:00.1053497	3	5	44
../examples/create_package.sql	00:00:00.1465556	4	14	128
../examples/create_procedure.sql	00:00:00.3637273	6	14	122
../examples/create_synonym01.sql	00:00:00.1207528	1	6	43
../examples/create_synonym02.sql	00:00:00.1251981	0	5	63
../examples/create_synonym03.sql	00:00:00.1114189	0	8	54
../examples/create_table.sql	00:00:00.6905801	80	247	2066
../examples/create_tablespace.sql	00:00:00.1137762	57	172	1490
../examples/create_type.sql	00:00:00.1307503	15	34	295
../examples/ctas.sql	00:00:00.4601335	3	18	108
../examples/datetime01.sql	00:00:00.7163916	1	16	171
../examples/datetime02.sql	00:00:01.6583632	30	156	1333
../examples/datetime03.sql	00:00:00.5021515	3	9	75
../examples/datetime04.sql	00:00:00.4503473	4	6	50
../examples/datetime05.sql	00:00:00.4023941	1	5	35
../examples/directory.sql	00:00:00.0953254	3	12	117
../examples/drop_table.sql	00:00:00.1406032	2	7	48
../examples/drop_view.sql	00:00:00.1518502	2	8	59
../examples/explain01.sql	00:00:00.5402821	8	30	261
../examples/fetch_clause01.sql	00:00:00.3938237	4	12	65
../examples/fetch_clause02.sql	00:00:00.4062165	4	13	71
../examples/fetch_clause03.sql	00:00:00.5938120	5	18	108
../examples/fetch_clause04.sql	00:00:00.6237268	3	20	137
../examples/flashback01.sql	00:00:00.7339357	1	12	102
../examples/for_update01.sql	00:00:00.4460173	4	11	81
../examples/for_update02.sql	00:00:00.5005925	4	11	93
../examples/for_update03.sql	00:00:00.5026095	4	13	108
../examples/for_update04.sql	00:00:00.4961679	3	14	114
../examples/for_update05.sql	00:00:00.4883951	3	15	115
../examples/for_update06.sql	00:00:00.4828234	3	15	119
../examples/for_update07.sql	00:00:00.4942867	3	17	117
../examples/for_update08.sql	00:00:00.7381149	5	19	140
../examples/function01.sql	00:00:00.5952932	2	30	173
../examples/function02.sql	00:00:00.5774773	1	4	24
../examples/function03.sql	00:00:00.5324401	4	6	42
../examples/function04.sql	00:00:00.5247913	1	7	31
../examples/function05.sql	00:00:00.6263521	3	8	69
../examples/function06.sql	00:00:00.1441717	1	4	36
../examples/groupby01.sql	00:00:01.1902210	9	86	721
../examples/groupby02.sql	00:00:01.0757647	10	60	587
../examples/groupby03.sql	00:00:00.5382326	4	12	49
../examples/groupby04.sql	00:00:00.5032223	3	11	49
../examples/groupby05.sql	00:00:00.5875294	4	12	61
../examples/groupby06.sql	00:00:01.0261143	15	48	545
../examples/groupby07.sql	00:00:00.9931023	8	37	206
../examples/index.sql	00:00:00.6520782	83	207	1843
../examples/insert_statement.sql	00:00:00.5513687	5	18	141
../examples/interval01.sql	00:00:00.5252175	2	13	86
../examples/interval02.sql	00:00:00.4663065	1	6	35
../examples/interval03.sql	00:00:00.8304096	21	69	485
../examples/interval04.sql	00:00:00.4980168	1	8	55
../examples/join01.sql	00:00:00.8007767	5	24	211
../examples/join02.sql	00:00:00.8169351	6	19	165
../examples/join03.sql	00:00:00.7271405	6	19	174
../examples/join04.sql	00:00:00.7741207	6	16	160
../examples/join05.sql	00:00:00.8355442	8	28	276
../examples/join06.sql	00:00:00.6710846	5	23	134
../examples/join07.sql	00:00:00.5366240	5	15	78
../examples/join08.sql	00:00:00.6758721	8	48	304
../examples/join09.sql	00:00:00.6537869	8	54	337
../examples/join10.sql	00:00:00.4931681	5	17	105
../examples/join11.sql	00:00:00.4229886	1	12	57
../examples/join12.sql	00:00:00.4142719	2	11	62
../examples/join13.sql	00:00:00.4274715	4	12	80
../examples/join14.sql	00:00:00.5758152	9	29	163
../examples/join15.sql	00:00:00.6478187	3	19	109
../examples/join16.sql	00:00:00.5201947	5	23	144
../examples/join17.sql	00:00:00.6392313	7	21	83
../examples/join18.sql	00:00:00.7949886	14	40	300
../examples/join19.sql	00:00:00.3794156	1	10	50
../examples/join20.sql	00:00:00.3690927	1	8	45
../examples/join21.sql	00:00:00.3838334	2	7	47
../examples/keywordasidentifier01.sql	00:00:00.5245301	1	6	44
../examples/keywordasidentifier02.sql	00:00:00.4680801	1	4	26
../examples/keywordasidentifier03.sql	00:00:01.4345825	24	104	1644
../examples/keywordasidentifier04.sql	00:00:00.6624083	6	18	138
../examples/keywordasidentifier05.sql	00:00:00.4561690	0	4	31
../examples/keywordasidentifier06.sql	00:00:00.5505219	7	16	107
../examples/least_greatest.sql	00:00:00.6492281	0	15	101
../examples/lexer01.sql	00:00:00.4422786	2	21	67
../examples/lexer02.sql	00:00:00.4256424	2	7	31
../examples/lexer03.sql	00:00:00.5464376	2	23	81
../examples/lexer04.sql	00:00:00.6951342	1	6	78
../examples/lexer05.sql	00:00:00.6830663	1	6	78
../examples/like01.sql	00:00:00.5128784	5	13	92
../examples/max_001.sql	00:00:00.6017690	5	9	63
../examples/merge01.sql	00:00:00.7167823	9	41	307
../examples/merge02.sql	00:00:00.7097463	9	41	307
../examples/merge03.sql	00:00:01.5644029	17	103	1073
../examples/merge04.sql	00:00:01.5421283	18	108	1098
../examples/merge05.sql	00:00:00.6454229	7	39	316
../examples/model_clause01.sql	00:00:00.7831576	18	54	405
../examples/model_clause02.sql	00:00:00.8676193	16	46	456
../examples/model_clause03.sql	00:00:00.7761378	15	38	311
../examples/model_clause04.sql	00:00:00.7264767	14	38	314
../examples/model_clause05.sql	00:00:00.7098295	14	39	315
../examples/numbers01.sql	00:00:00.5515083	14	55	279
../examples/nvl_001.sql	00:00:00.6667271	3	15	139
../examples/object_access01.sql	00:00:00.8462546	18	38	566
../examples/offset_clause.sql	00:00:00.3818963	3	7	40
../examples/order_by01.sql	00:00:00.4149244	1	7	30
../examples/order_by02.sql	00:00:00.4175363	1	8	34
../examples/order_by03.sql	00:00:00.7201172	1	9	50
../examples/order_by04.sql	00:00:00.5105669	1	12	57
../examples/order_by05.sql	00:00:00.5841009	0	22	106
../examples/order_by06.sql	00:00:00.4233489	1	14	58
../examples/order_by07.sql	00:00:02.2273393	106	333	3584
../examples/packagebody_function.sql	00:00:00.2860443	12	47	374
../examples/packagespec_function.sql	00:00:00.1307139	4	24	174
../examples/package_body.sql	00:00:00.3557070	7	19	162
../examples/partitioned_tables.sql	00:00:00.5777628	154	528	5915
../examples/pivot01.sql	00:00:00.5605738	6	19	152
../examples/pivot02.sql	00:00:00.7074263	11	29	211
../examples/pivot03.sql	00:00:00.6789814	11	26	176
../examples/pivot04.sql	00:00:00.5792188	20	28	152
../examples/pivot05.sql	00:00:00.5400618	5	22	264
../examples/pivot06.sql	00:00:00.5755163	5	25	329
../examples/pivot07.sql	00:00:00.7128543	5	28	200
../examples/pivot08.sql	00:00:00.6768898	3	24	165
../examples/pivot09.sql	00:00:00.8643953	19	50	283
../examples/pivot10.sql	00:00:00.7753964	19	50	281
../examples/pivot11.sql	00:00:00.8061053	20	50	282
../examples/pivot12.sql	00:00:00.6049050	17	37	303
../examples/query_factoring01.sql	00:00:00.9081026	16	55	453
../examples/query_factoring02.sql	00:00:00.9334433	18	62	548
../examples/query_factoring03.sql	00:00:00.8469547	16	52	439
../examples/query_factoring04.sql	00:00:00.9943664	18	64	551
../examples/query_factoring05.sql	00:00:01.1241150	27	90	489
../examples/query_factoring06.sql	00:00:00.8127124	15	42	397
../examples/query_factoring07.sql	00:00:02.3969010	78	562	4299
../examples/query_factoring08.sql	00:00:00.5887163	10	40	168
../examples/query_factoring09.sql	00:00:00.6678669	8	29	153
../examples/query_factoring10.sql	00:00:00.9902062	33	109	742
../examples/query_factoring11.sql	00:00:00.9989916	9	36	296
../examples/rename_objects.sql	00:00:00.0980339	2	15	94
../examples/round_001.sql	00:00:00.7799688	7	15	138
../examples/round_decode_001.sql	00:00:00.8895967	10	19	178
../examples/row_number_001.sql	00:00:00.6308744	4	16	140
../examples/sample01.sql	00:00:00.5503644	4	20	104
../examples/select_package_function.sql	00:00:00.8164281	0	9	80
../examples/simple02.sql	00:00:00.4872221	3	11	92
../examples/simple03.sql	00:00:00.4941383	3	13	107
../examples/simple04.sql	00:00:00.3343697	11	19	95
../examples/simple05.sql	00:00:00.3149420	9	17	85
../examples/simple06.sql	00:00:00.3014028	2	9	41
../examples/simple07.sql	00:00:00.2741735	3	8	39
../examples/simple08.sql	00:00:00.6197858	2	11	52
../examples/simple09.sql	00:00:00.5237392	7	16	141
../examples/simple10.sql	00:00:00.4733324	1	6	27
../examples/simple11.sql	00:00:00.2983974	1	4	21
../examples/simple12.sql	00:00:00.7133329	7	9	72
../examples/simple13.sql	00:00:00.3584996	1	8	42
../examples/string01.sql	00:00:00.3976591	17	56	326
../examples/to_char.sql	00:00:00.5154009	0	11	110
../examples/to_char_001.sql	00:00:00.5039240	0	9	68
../examples/to_char_002.sql	00:00:00.4814823	0	6	61
../examples/to_date.sql	00:00:00.8045755	8	68	592
../examples/truncate_table.sql	00:00:00.1506500	0	3	20
../examples/unified.sql	00:00:00.0986064	3	9	57
../examples/union01.sql	00:00:00.5156094	7	42	256
../examples/union02.sql	00:00:00.4653216	4	12	90
../examples/union03.sql	00:00:00.4860031	6	14	95
../examples/union04.sql	00:00:00.5310043	45	113	810
../examples/union05.sql	00:00:00.4500685	30	66	279
../examples/union06.sql	00:00:00.7166827	35	62	611
../examples/union07.sql	00:00:01.1521679	47	171	3881
../examples/union08.sql	00:00:00.4149006	5	18	87
../examples/union09.sql	00:00:00.6560631	27	56	1041
../examples/union10.sql	00:00:00.5819724	14	45	2085
../examples/views.sql	00:00:01.0828387	59	203	1731
../examples/xmltable01.sql	00:00:00.8527133	12	32	527
../examples/xmltable02.sql	00:00:00.7281806	2	7	107
../examples/xmltable03.sql	00:00:00.7653428	16	52	757

• Only 23 of 230 ran over one second, about the same as the split grammar above.

PlSql.txt

[kaby76]

Test of query_factoring11.sql

This test runs in about 1s, which is surprising because the file is not very long:

WITH COL_GENERATOR AS (
SELECT T1.BATCH_ID, DECODE(T1.ACTION, 'SENT', T1.ACTDATE) SENT,
DECODE(T2.ACTION,'RECV', T2.ACTDATE) RECEIVED
FROM TEST T1, TEST T2
WHERE T2.BATCH_ID(+) = T1.BATCH_ID)
SELECT BATCH_ID, MAX(SENT) SENT, MAX(RECEIVED) RECEIVED
FROM COL_GENERATOR
GROUP BY BATCH_ID
ORDER BY 1

Using Intellij, the combined grammar I give, and the Antlr plugin, I tested the input.

Profiling:

• The majority of the time is involved with the atom rule. In fact, the rule has many ambiguities.

Conclusion:

• Yes, this is slow. The target does not matter here.
• The problems observed are likely due to the atom rule.

[kaby76]

The atom rule is terrible, and is likely a major cause of problems in runtime.

• There is much in common between table_element ((INTRODUCER char_set_name)? id_expression ('.' id_expression)*); general_element (general_element_part ('.' general_element_part)*); ...
• Right. No shite, Sherlock..
• The entire rule must be rewritten.
• Heuristically, performance increases with unfolding, left factoring (grouping), recursion rewritten using kleene-operators.

[kaby76]

• Indeed, atom is one problem. I have a refactoring that improves parse times by 50%. But, it needs work.
• Why in the world would a "quoted_string" --- things with quotes! -- ever, ever, ever derive a "variable_name"?? here. This is complete nonsense, done here.

[KvanTTT]

Why in the world would a "quoted_string" --- things with quotes! -- ever, ever, ever derive a "variable_name"?? here. This is complete nonsense, done here.

Sorry, it was merged by me. I should have been more attentive.

[kaby76]

No one probably knew the issues way way back then, so you were right to check it in as is. It's a little challenging to fix atom.

[Commodore68]

I have a personal one that mostly supports oracle 19 that has fixed the quoted string issue in a lot of places. In fact, quoted string shouldn't really be used for anything other than straight strings in the plsql grammar. I have noticed that the quoted string is used in a lot of places for identifiers in which it will never actually match.

[kaby76]

Several changes were applied. Closing this Issue but further improvements should be done at some point.