SQLPrompt slow to load DB metadata (suggestions) after upgrading to 10.16
nocky
Posts: 5 New member
in SQL Prompt
More of feedback than question but I have found that after upgrading to 10.16 my SQLPrompt is noticeably slower loading suggestions. Where it used to take about 5 seconds it seems now it is taking 30 seconds or more. For example if my database that I connect to is named Foo I will see a notification "loading Foo" in the bottom right corner. If it helps, the most common spot where it seems to get stuck is "Processing parameters...". Our database has approx 3000 tables, 13000 stored procedures.
Tagged:
Answers
Thanks for reaching out to Redgate Support with your feedback. I am actually surprised by your feedback as Prompt SQLPrompt_10.16.0.13262.exe was released specifically with Performance improvements for "Processing Parameters" that other users have already confirmed improvement for. (Almost 10X faster in my testing)
These improvements were the result of a performance issue we were seeing on version 10.15 and 10.14 of Prompt which was rooted in some changes we had to make in how Stored Procedures were loaded in to the application, so if you dont mind me asking, what version of Prompt did you upgrade from? Its possible if you upgraded from a dated version prior to the changes made for loading Stored Procedures in Prompt that you still would see some slowdown.
In this case, if you indeed think it has gotten worse, what we would need to gather for confirmation are the following:
Gather Debug logs from Prompt (Prompt > Help > Verbose Prompt > Help > Locate Log Files) now while you are on the latest version, in the logs we track how long it takes to load parameters. Then Fully uninstall SQL Prompt and downgrade to version 10.15.3.12768 and then repeat the test with Prompt in Debug. Then we can compare the logs from the latest version and version 10.15.3.12768 and see if there is a performance slowdown or speed up.
2024-12-13 12:18:16.007 -05:00 [Debug] [dev.sql.sis.jhu.edu].[dailydb] progress: Processing parameters...
2024-12-13 12:18:08.931 -05:00 [Debug] Time taken for parameters [master] = "00:00:04.1260636" [7679 rows]
New Version:
2024-12-13 12:31:15.246 -05:00 [Debug] Time taken for parameters [master] = "00:00:01.3722779" [7679 rows]
Will admit, I was expecting a bigger improvement from the new version than we saw here in this case, but the newer version is still faster. If you are still curious I would roll back two more versions, and then see if there is significant improvement, as this should be prior to the changes were made to Stored Procedures
Adding 10.14, I am fairly certain this one should be before the changes were made.
That is more of what I was expecting, so it seems that in your case, the improvement in the latest version of Prompt vs 10.15.3 is not as pronounced as we saw with other users, I will document these Logs you have included and I may have to send this up to be reviewed further and see if any additional optimization can be made using the findings.
You can of course continue to use 10.14.21 if you so choose for the time being