[Bug]: sp_describe_undeclared_parameters is slow #1317
Labels
Comments
|
It runs really fast for me in 2.1.0, 2.2.0 and 2.3.0. But is it possible that you have a very large number of objects in your database for the case where it runs slow? There is a know performance issue related to that which I suspect may have something to do with it. |
|
Tested in Docker on Win10. 390 table and 1132 index exists. Mostly tables is empty. Some initializations inserted. |
1 task
Deepesh125
pushed a commit
that referenced
this issue
Nov 20, 2023
sp_describe_undeclared_parameters is slow. The issue is reported here: #1317. Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
ahmed-shameem
added a commit
to amazon-aurora/babelfish_extensions
that referenced
this issue
Nov 21, 2023
…or-postgresql#1905) sp_describe_undeclared_parameters is slow. The issue is reported here: babelfish-for-postgresql#1317. Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
1 task
Deepesh125
pushed a commit
that referenced
this issue
Nov 21, 2023
sp_describe_undeclared_parameters is slow. The issue is reported here: #1317 Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
ahmed-shameem
added a commit
to amazon-aurora/babelfish_extensions
that referenced
this issue
Nov 21, 2023
…or-postgresql#1905) sp_describe_undeclared_parameters is slow. The issue is reported here: babelfish-for-postgresql#1317. Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
1 task
ahmed-shameem
added a commit
to amazon-aurora/babelfish_extensions
that referenced
this issue
Nov 21, 2023
…or-postgresql#2040) sp_describe_undeclared_parameters is slow. The issue is reported here: babelfish-for-postgresql#1317 Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
1 task
shardgupta
pushed a commit
that referenced
this issue
Nov 22, 2023
sp_describe_undeclared_parameters is slow. The issue is reported here: #1317. Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. The changes are tested locally. Performance improvements (All the times are in ms): -------------- INSERT -------------- | No of Params | Before | After | |--------------|-------|--------| | 1 | 3796 | 281 | | 2 | 6774 | 44 | | 8 | 27648 | 1323 | | 10 | 35425 | 1363 | | 20 | 70130 | 1844 | | 30 | 106210 | 1937 | | 40 | 141363 | 2712 | | 50 | 179453 | 2940 | | 60 | 223271 | 4861 | | 70 | 257286 | 6499 | | 80 | 298696 | 8966 | | 90 | 344058 | 10309 | | 100 | 385037 | 12815 | -------------- UPDATE -------------- | No of Params | Before | After | |--------------|-------|--------| | 1 | 7024 | 133 | | 2 | 10389 | 64 | | 8 | 31522 | 1283 | | 10 | 39633 | 1314 | | 20 | 73458 | 1710 | | 30 | 109540 | 1927 | | 40 | 144949 | 2762 | | 50 | 184793 | 2940 | | 60 | 227304 | 5151 | | 70 | 259366 | 6398 | | 80 | 302109 | 9135 | | 90 | 345392 | 10677 | | 100 | 387352 | 12857 | -------------- DELETE -------------- | No of Params | Before | After | |--------------|-------|--------| | 1 | 3519 | 64 | | 2 | 6975 | 45 | | 8 | 27997 | 1282 | | 10 | 34696 | 1350 | | 20 | 70144 | 1727 | | 30 | 105511 | 1933 | | 40 | 144679 | 2701 | | 50 | 180619 | 2955 | | 60 | 224197 | 4864 | | 70 | 260444 | 6457 | | 80 | 305708 | 8955 | | 90 | 350545 | 10393 | | 100 | 391443 | 12878 | Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
shardgupta
pushed a commit
that referenced
this issue
Nov 22, 2023
### Description sp_describe_undeclared_parameters is slow. The issue is reported here: #1317. Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. The changes are tested locally. <!--- Performance improvements (All the times are in ms): -------------- INSERT -------------- |No of Params|Before |After| |----------|------|--------| |1|1109|334| |2|1619|61| |8|6061|292| |10|7600|367| |20|15498|674| |30|23112|1027| |40|31076|1354| |50|39429|1779| |60|49043|2196| |70|55999|2497| |80|65040|2921| |90|73428|3251| |100|83244|3597| -------------- UPDATE -------------- |No of Params|Before |After| |----------|------|--------| |1|1681|204| |2|2323|99| |8|6919|323| |10|8406|407| |20|16280|713| |30|23911|1068| |40|31936|1398| |50|40249|1823| |60|49052|2232| |70|57128|2530| |80|65178|2890| |90|73632|3261| |100|83183|3634| -------------- DELETE -------------- |No of Params|Before |After| |----------|------|--------| |1|803|84| |2|1507|66| |8|6135|310| |10|7710|365| |20|15561|673| |30|23361|1032| |40|31125|1366| |50|39824|1791| |60|48933|2310| |70|57075|2514| |80|65444|2864| |90|73762|3249| |100|83991|3648| Query plans before modification: 1. INSERT: a. sys.columns: https://explain.dalibo.com/plan/033e354d56b2833e b. Complete plan: https://explain.dalibo.com/plan/fd4965765649d5b0 2. UPDATE: a. sys.columns: https://explain.dalibo.com/plan/28c1g72cfa4151ch b. Complete plan: https://explain.dalibo.com/plan/6bd40ah1b822084b 3. DELETE: a. sys.columns: https://explain.dalibo.com/plan/a3c6c3c387b48218 b. Complete plan: https://explain.dalibo.com/plan/2ge4425f5d3c9f7a Query plans after modification: 1. INSERT: https://explain.dalibo.com/plan/c96f94bge2632fa5 5. UPDATE: https://explain.dalibo.com/plan/2g8bd8feegfg4626 6. DELETE: https://explain.dalibo.com/plan/61g46gg71a853d7b QUERY PLAN AFTER MODIFICATION WITH EXPLICIT JOINS: 1. INSERT: https://explain.dalibo.com/plan/094bcg7de3bgg176 2. UPDATE: https://explain.dalibo.com/plan/4af4ba696e2hbdge 3. DELETE: https://explain.dalibo.com/plan/0c6356641a787394 ---> Performance improvements (All the times are in ms): -------------- INSERT -------------- | No of Params | Before | After | |--------------|-------|--------| | 1 | 1109 | 298 | | 2 | 1619 | 39 | | 8 | 6061 | 562 | | 10 | 7600 | 573 | | 20 | 15498 | 967 | | 30 | 23112 | 1170 | | 40 | 31076 | 1563 | | 50 | 39429 | 1745 | | 60 | 49043 | 2628 | | 70 | 55999 | 3318 | | 80 | 65040 | 4381 | | 90 | 73428 | 4978 | | 100 | 83244 | 6072 | -------------- UPDATE -------------- | No of Params | Before | After | |--------------|-------|--------| | 1 | 1681 | 177 | | 2 | 2323 | 55 | | 8 | 6919 | 556 | | 10 | 8406 | 594 | | 20 | 16280 | 976 | | 30 | 23911 | 1195 | | 40 | 31936 | 1581 | | 50 | 40249 | 1765 | | 60 | 49052 | 2606 | | 70 | 57128 | 3318 | | 80 | 65178 | 4365 | | 90 | 73632 | 5028 | | 100 | 83183 | 6102 | -------------- DELETE -------------- | No of Params | Before | After | |--------------|-------|--------| | 1 | 803 | 71 | | 2 | 1507 | 37 | | 8 | 6135 | 538 | | 10 | 7710 | 575 | | 20 | 15561 | 975 | | 30 | 23361 | 1173 | | 40 | 31125 | 1570 | | 50 | 39824 | 1772 | | 60 | 48933 | 2620 | | 70 | 57075 | 3362 | | 80 | 65444 | 4481 | | 90 | 73762 | 5127 | | 100 | 83991 | 6248 | QUERY PLAN AFTER MODIFICATION WITH EXPLICIT JOINS AND REMOVING UNNECESSARY CHECK ON t2.NAME: 1. INSERT: https://explain.dalibo.com/plan/dh97223b15e92h0d 2. UPDATE: https://explain.dalibo.com/plan/d28ec392d560b25c 3. DELETE: https://explain.dalibo.com/plan/348269ffgb6ae9g9 Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
staticlibs
pushed a commit
to wiltondb/babelfish_extensions
that referenced
this issue
Apr 12, 2024
…or-postgresql#1905) sp_describe_undeclared_parameters is slow. The issue is reported here: babelfish-for-postgresql#1317. Initially, we used to create a query in C for every parameter passed as an argument to sp_describe_undeclared_parameters and then execute it. This was taking huge amount of time as it used JOINS among multiple views like sys.objects, sys.columns, sys.types T. In this commit, we have replaced the use of such views as much as possible with pg catalogs. This aids to the improvement in performance. Task: BABEL-3705 Signed-off-by: Shameem Ahmed <[email protected]>
|
I believe this is fixed with #1905 . In local test I can see It is still noticeably slower than with MSSQL, if someone is hitting |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
What happened?
sp_describe_undeclared_parameters is very slow, 50sec waiting.
Tested with 2.3.0.
Works good with 2.1.2
sample code:
Version
BABEL_3_X_DEV (Default)
Extension
babelfishpg_tsql (Default)
Which flavor of Linux are you using when you see the bug?
Ubuntu (Default)
Relevant log output
Code of Conduct
The text was updated successfully, but these errors were encountered: