apache
[Feature](PreparedStatement) implement general server side prepared
Description:
This commit marks the initial step towards integrating a server-side prepared statement feature into the original planner, set to be implemented in Nereids soon.
Background:
Previously, we added support for prepared statements for point query and insert statements in #15491, aimed at enhancing front-end performance by introducing basic server-side prepared statement functionality. However, certain scenarios necessitate server-side prepared statements to ensure compatibility with Business Intelligence (BI) tools.
Details:
This pull request introduces a general prepared statement mechanism for all query types, including point queries. The implementation aligns with the MySQL protocol's server-side prepared statement workflow, which involves two phases:
- PREPARE Command: The first phase involves sending the PREPARE command to Doris, wherein statements with placeholders (e.g., SELECT * FROM tbl WHERE k = ?) are analyzed and cached.
- EXECUTE Command: Subsequently, the EXECUTE command is sent to Doris, utilizing the statement ID to retrieve the previously cached statement. Types of Prepared Statements:
This implementation supports three types of prepared statements:
- FULL: This type is exclusively supported by point queries, obviating the need for parsing, reanalysis, and planning. It triggers only when a schema change occurs (e.g., schema version change).
- STATEMENT: This type caches the statement, eliminating the need for parsing the SQL every time. However, it requires periodic reanalysis and planning.
- NONE: Not using prepared statement
This enhancement aims to improve query performance and compatibility with BI tools by leveraging server-side prepared statements across various query types.
Proposed changes
Issue Number: close #xxx
Further comments
If this is a relatively large or complex change, kick off the discussion at [email protected] by explaining why you chose the solution you did and what alternatives you considered, etc...
Thank you for your contribution to Apache Doris. Don't know what should be done next? See How to process your PR
Since 2024-03-18, the Document has been moved to doris-website. See Doris Document.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
TeamCity be ut coverage result: Function Coverage: 35.45% (8908/25128) Line Coverage: 27.14% (73155/269570) Region Coverage: 26.28% (37828/143942) Branch Coverage: 23.07% (19274/83528) Coverage Report: http://coverage.selectdb-in.cc/coverage/b9ca2b00a03141df5fc1e3042ff6494ce50a8bd0_b9ca2b00a03141df5fc1e3042ff6494ce50a8bd0/report/index.html
TeamCity be ut coverage result: Function Coverage: 35.52% (8926/25131) Line Coverage: 27.14% (73449/270653) Region Coverage: 26.32% (37939/144121) Branch Coverage: 23.11% (19325/83604) Coverage Report: http://coverage.selectdb-in.cc/coverage/acd9b6339dcc3baa5c4a47528735a5539cd99ad6_acd9b6339dcc3baa5c4a47528735a5539cd99ad6/report/index.html
TPC-H: Total hot run time: 41856 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit acd9b6339dcc3baa5c4a47528735a5539cd99ad6, data reload: false
------ Round 1 ----------------------------------
q1 17694 4434 4328 4328
q2 2021 191 211 191
q3 10502 1247 1255 1247
q4 10215 884 839 839
q5 7518 2778 2755 2755
q6 222 136 142 136
q7 1066 640 635 635
q8 9222 2162 2136 2136
q9 9227 6857 6783 6783
q10 9045 3908 3911 3908
q11 463 241 238 238
q12 426 224 221 221
q13 17711 3325 3153 3153
q14 287 255 249 249
q15 513 485 481 481
q16 498 391 377 377
q17 964 660 783 660
q18 8447 7951 7932 7932
q19 5279 1610 1592 1592
q20 652 338 312 312
q21 5282 3410 4283 3410
q22 357 285 273 273
Total cold run time: 117611 ms
Total hot run time: 41856 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4528 4465 4387 4387
q2 392 284 284 284
q3 3163 2917 2970 2917
q4 2025 1772 1585 1585
q5 5381 5560 5507 5507
q6 219 125 129 125
q7 2368 1978 2005 1978
q8 3262 3479 3453 3453
q9 8846 8840 8901 8840
q10 4090 3832 3753 3753
q11 592 485 496 485
q12 809 629 612 612
q13 15929 3088 3123 3088
q14 316 291 275 275
q15 537 494 485 485
q16 490 441 449 441
q17 1820 1518 1519 1518
q18 8104 7658 7384 7384
q19 1688 1569 1583 1569
q20 2029 1783 1754 1754
q21 14320 4852 4888 4852
q22 567 491 467 467
Total cold run time: 81475 ms
Total hot run time: 55759 ms
TPC-DS: Total hot run time: 185847 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit acd9b6339dcc3baa5c4a47528735a5539cd99ad6, data reload: false
query1 891 354 357 354
query2 6445 2415 2506 2415
query3 6642 196 209 196
query4 23658 21198 21242 21198
query5 4124 415 414 414
query6 263 183 179 179
query7 4593 289 281 281
query8 247 201 187 187
query9 8452 2328 2303 2303
query10 430 245 245 245
query11 14802 14155 14164 14155
query12 139 92 86 86
query13 1640 358 369 358
query14 9741 7575 8281 7575
query15 267 168 176 168
query16 8098 258 259 258
query17 1746 557 541 541
query18 2087 273 265 265
query19 207 149 142 142
query20 91 84 86 84
query21 196 132 134 132
query22 4995 4808 4758 4758
query23 33639 33254 32990 32990
query24 6999 2906 2901 2901
query25 579 371 379 371
query26 693 151 149 149
query27 2214 310 335 310
query28 4841 2021 2006 2006
query29 851 620 582 582
query30 254 157 156 156
query31 974 718 721 718
query32 90 50 52 50
query33 519 242 234 234
query34 884 466 465 465
query35 784 671 681 671
query36 1017 872 897 872
query37 107 64 67 64
query38 3147 3069 3066 3066
query39 1594 1532 1550 1532
query40 192 123 124 123
query41 43 39 39 39
query42 111 98 94 94
query43 568 559 541 541
query44 1102 727 744 727
query45 276 261 258 258
query46 1073 761 746 746
query47 1955 1883 1848 1848
query48 367 292 284 284
query49 880 397 394 394
query50 771 377 365 365
query51 6690 6639 6766 6639
query52 99 94 90 90
query53 347 273 269 269
query54 270 235 230 230
query55 76 73 68 68
query56 233 221 220 220
query57 1216 1142 1153 1142
query58 218 190 196 190
query59 3436 3215 3307 3215
query60 248 233 244 233
query61 94 89 133 89
query62 590 439 435 435
query63 309 271 273 271
query64 8217 7175 7131 7131
query65 3117 3010 3058 3010
query66 780 339 335 335
query67 15360 14862 15118 14862
query68 5199 529 554 529
query69 472 311 304 304
query70 1178 1146 1052 1052
query71 393 274 278 274
query72 7707 2630 2451 2451
query73 702 318 320 318
query74 6492 6062 6122 6062
query75 3392 2670 2765 2670
query76 2856 1039 926 926
query77 411 266 261 261
query78 11062 10289 10292 10289
query79 2893 509 532 509
query80 1868 433 437 433
query81 526 222 222 222
query82 775 98 93 93
query83 303 171 169 169
query84 267 83 92 83
query85 1757 294 262 262
query86 501 297 302 297
query87 3297 3081 3156 3081
query88 4480 2333 2325 2325
query89 497 391 385 385
query90 2038 185 184 184
query91 128 98 101 98
query92 60 51 49 49
query93 5283 509 511 509
query94 1269 182 183 182
query95 388 305 298 298
query96 615 265 266 265
query97 3126 2933 2911 2911
query98 252 227 225 225
query99 1212 852 865 852
Total cold run time: 278977 ms
Total hot run time: 185847 ms
PR approved by at least one committer and no changes requested.
TPC-H: Total hot run time: 41320 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit e1922a463f4bb19d0eea3b2c0f6e1a8003c8204e, data reload: false
------ Round 1 ----------------------------------
q1 17925 4757 4320 4320
q2 2019 191 190 190
q3 10662 1232 1261 1232
q4 10724 794 723 723
q5 7609 2757 2708 2708
q6 219 132 134 132
q7 1043 641 622 622
q8 9596 2146 2093 2093
q9 9179 6760 6701 6701
q10 9512 3865 3889 3865
q11 478 247 240 240
q12 493 223 215 215
q13 18748 3089 2972 2972
q14 268 224 226 224
q15 519 482 476 476
q16 503 381 390 381
q17 975 706 792 706
q18 8069 7410 7371 7371
q19 2458 1516 1502 1502
q20 646 300 297 297
q21 5089 4082 4110 4082
q22 346 268 272 268
Total cold run time: 117080 ms
Total hot run time: 41320 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4340 4250 4252 4250
q2 371 266 276 266
q3 3007 2729 2708 2708
q4 1902 1590 1569 1569
q5 5331 5317 5351 5317
q6 220 123 127 123
q7 2256 1881 1851 1851
q8 3242 3375 3378 3375
q9 8678 8573 8570 8570
q10 3914 3754 3632 3632
q11 585 493 495 493
q12 766 572 629 572
q13 16511 2970 3004 2970
q14 299 298 278 278
q15 511 472 469 469
q16 482 435 438 435
q17 1798 1483 1476 1476
q18 7639 7628 7452 7452
q19 1698 1542 1551 1542
q20 1993 1747 1792 1747
q21 5035 4855 4755 4755
q22 591 515 490 490
Total cold run time: 71169 ms
Total hot run time: 54340 ms
TPC-DS: Total hot run time: 185796 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit e1922a463f4bb19d0eea3b2c0f6e1a8003c8204e, data reload: false
query1 912 358 349 349
query2 6455 2346 2357 2346
query3 6664 207 210 207
query4 22860 21197 21056 21056
query5 4213 424 447 424
query6 264 173 170 170
query7 4591 289 294 289
query8 249 187 182 182
query9 8559 2383 2365 2365
query10 429 240 273 240
query11 14670 14054 14116 14054
query12 133 94 83 83
query13 1624 359 367 359
query14 9768 7500 7373 7373
query15 239 180 169 169
query16 8063 262 256 256
query17 1872 548 561 548
query18 2053 270 263 263
query19 196 146 145 145
query20 92 84 84 84
query21 196 121 127 121
query22 5025 4868 4870 4868
query23 33582 33088 33316 33088
query24 12096 2944 2895 2895
query25 676 390 411 390
query26 1792 152 150 150
query27 3105 347 341 341
query28 7672 2029 2024 2024
query29 1045 594 606 594
query30 289 147 148 147
query31 986 743 714 714
query32 98 54 54 54
query33 727 243 240 240
query34 1094 479 474 474
query35 819 667 672 667
query36 1042 889 935 889
query37 271 67 65 65
query38 3142 3006 2979 2979
query39 1565 1518 1522 1518
query40 285 125 126 125
query41 43 38 39 38
query42 101 96 100 96
query43 583 515 534 515
query44 1246 723 730 723
query45 262 260 268 260
query46 1093 694 734 694
query47 1940 1871 1891 1871
query48 370 296 294 294
query49 1184 401 390 390
query50 780 377 389 377
query51 6800 6664 6698 6664
query52 101 92 87 87
query53 350 285 273 273
query54 309 230 234 230
query55 78 77 76 76
query56 243 221 220 220
query57 1207 1136 1122 1122
query58 231 202 215 202
query59 3572 2960 3143 2960
query60 253 242 235 235
query61 90 86 86 86
query62 647 447 441 441
query63 313 280 282 280
query64 9466 7120 7111 7111
query65 3125 3060 3030 3030
query66 1382 350 343 343
query67 15181 15162 15271 15162
query68 5246 550 562 550
query69 517 306 307 306
query70 1177 1069 1174 1069
query71 420 267 269 267
query72 7239 2619 2421 2421
query73 715 328 332 328
query74 6465 6142 6249 6142
query75 3515 2655 2659 2655
query76 3476 1007 980 980
query77 611 281 260 260
query78 10915 10368 10210 10210
query79 4054 535 519 519
query80 1866 425 433 425
query81 530 229 236 229
query82 1130 94 97 94
query83 329 169 167 167
query84 270 89 85 85
query85 1603 268 259 259
query86 498 288 300 288
query87 3236 3087 3050 3050
query88 4709 2440 2417 2417
query89 489 375 371 371
query90 1964 201 200 200
query91 126 97 97 97
query92 62 46 50 46
query93 5201 521 517 517
query94 1237 183 217 183
query95 387 305 299 299
query96 599 270 272 270
query97 3139 2943 2953 2943
query98 250 223 219 219
query99 1222 900 845 845
Total cold run time: 293560 ms
Total hot run time: 185796 ms
TeamCity be ut coverage result: Function Coverage: 35.49% (8925/25146) Line Coverage: 27.14% (73474/270721) Region Coverage: 26.32% (37947/144171) Branch Coverage: 23.12% (19332/83632) Coverage Report: http://coverage.selectdb-in.cc/coverage/e1922a463f4bb19d0eea3b2c0f6e1a8003c8204e_e1922a463f4bb19d0eea3b2c0f6e1a8003c8204e/report/index.html
PR approved by at least one committer and no changes requested.
TPC-H: Total hot run time: 41804 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 929061140e8531bceb0aca91b046943679786e1c, data reload: false
------ Round 1 ----------------------------------
q1 17608 4398 4245 4245
q2 2013 186 192 186
q3 10466 1303 1237 1237
q4 10204 746 714 714
q5 7522 2785 2806 2785
q6 218 135 133 133
q7 983 572 547 547
q8 9539 2162 2187 2162
q9 10160 7537 7437 7437
q10 9033 3941 3869 3869
q11 442 241 228 228
q12 592 226 222 222
q13 17186 3269 3044 3044
q14 276 230 243 230
q15 526 475 470 470
q16 510 409 393 393
q17 984 749 737 737
q18 8348 7820 7670 7670
q19 2475 1580 1560 1560
q20 667 327 318 318
q21 5591 3333 4061 3333
q22 344 287 284 284
Total cold run time: 115687 ms
Total hot run time: 41804 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4584 4435 4391 4391
q2 378 273 272 272
q3 3235 2949 3019 2949
q4 1922 1634 1633 1633
q5 5567 5517 5524 5517
q6 221 125 127 125
q7 1807 1470 1436 1436
q8 3267 3443 3441 3441
q9 9474 9647 9685 9647
q10 4079 3748 3794 3748
q11 603 476 508 476
q12 801 656 613 613
q13 17112 3142 3097 3097
q14 314 297 281 281
q15 522 487 493 487
q16 499 430 442 430
q17 1792 1488 1474 1474
q18 7677 7596 7424 7424
q19 1711 1731 1563 1563
q20 1968 1774 1765 1765
q21 4959 4984 4969 4969
q22 533 479 493 479
Total cold run time: 73025 ms
Total hot run time: 56217 ms
TeamCity be ut coverage result: Function Coverage: 35.61% (8932/25083) Line Coverage: 27.23% (73655/270460) Region Coverage: 26.42% (38053/144018) Branch Coverage: 23.19% (19393/83610) Coverage Report: http://coverage.selectdb-in.cc/coverage/929061140e8531bceb0aca91b046943679786e1c_929061140e8531bceb0aca91b046943679786e1c/report/index.html
TPC-DS: Total hot run time: 186650 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 929061140e8531bceb0aca91b046943679786e1c, data reload: false
query1 914 363 351 351
query2 6436 2433 2277 2277
query3 6644 209 211 209
query4 23356 21213 21200 21200
query5 4127 403 437 403
query6 267 189 178 178
query7 4589 287 299 287
query8 243 188 192 188
query9 8398 2302 2298 2298
query10 433 243 250 243
query11 15156 14233 14262 14233
query12 138 90 86 86
query13 1650 363 375 363
query14 9887 8447 8304 8304
query15 231 172 183 172
query16 7655 272 262 262
query17 1305 569 565 565
query18 1955 297 278 278
query19 203 154 156 154
query20 93 87 86 86
query21 203 128 133 128
query22 4983 4845 4812 4812
query23 33877 33125 32966 32966
query24 5153 3009 2964 2964
query25 487 390 393 390
query26 692 159 150 150
query27 1849 317 329 317
query28 3721 2023 2008 2008
query29 863 624 612 612
query30 236 153 158 153
query31 956 744 727 727
query32 64 54 55 54
query33 500 255 247 247
query34 885 470 479 470
query35 753 704 686 686
query36 1020 943 876 876
query37 105 71 68 68
query38 3114 3040 3000 3000
query39 1581 1527 1525 1525
query40 196 129 124 124
query41 44 42 41 41
query42 106 95 105 95
query43 557 553 547 547
query44 1099 724 736 724
query45 287 332 267 267
query46 1055 694 723 694
query47 1917 1855 1832 1832
query48 354 302 290 290
query49 743 392 396 392
query50 771 384 391 384
query51 6737 6619 6650 6619
query52 102 91 92 91
query53 358 278 280 278
query54 256 241 247 241
query55 74 74 75 74
query56 234 232 221 221
query57 1197 1131 1142 1131
query58 214 198 197 197
query59 3443 3088 3213 3088
query60 260 230 230 230
query61 104 86 86 86
query62 551 433 445 433
query63 311 278 274 274
query64 7465 7135 7118 7118
query65 3116 3045 3044 3044
query66 785 336 339 336
query67 15513 14894 15038 14894
query68 9681 559 548 548
query69 569 303 296 296
query70 1192 1121 1124 1121
query71 509 270 266 266
query72 8163 2618 2422 2422
query73 861 336 330 330
query74 6459 6160 6057 6057
query75 4670 2712 2652 2652
query76 5047 1001 983 983
query77 694 262 262 262
query78 10912 10309 10264 10264
query79 11792 511 524 511
query80 1742 431 437 431
query81 500 222 216 216
query82 237 96 96 96
query83 204 167 168 167
query84 269 83 81 81
query85 899 299 255 255
query86 349 326 310 310
query87 3251 3168 3092 3092
query88 5132 2337 2329 2329
query89 524 383 378 378
query90 2173 182 185 182
query91 125 97 95 95
query92 63 46 47 46
query93 6770 509 500 500
query94 1426 181 179 179
query95 404 290 298 290
query96 605 271 263 263
query97 3145 2923 2955 2923
query98 234 227 216 216
query99 1107 882 854 854
Total cold run time: 292279 ms
Total hot run time: 186650 ms