search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.56k stars 901 forks source link

Delay in query open for scripting actions #8926

Closed gregveres closed 1 year ago

gregveres commented 4 years ago

Issue Type: Performance Issue

I work on Azure SQL servers. I mostly run queries and edit data on those servers and using Azure Data Studio is hell. Here's why... (please keep reading for a trivial fix)

First, when I want to query a table, I will right click on that table and select menu "Select Top 1000". Once I do that, I sit and wait 3-5 seconds before a window even appears. That is way too long to wait for some feedback that the system actually accepted my input.

Fix: Create the window immediately and put a spinner in the query section of the window. Then replace that spinner with the query after you have data that is the query. Then put a spinner in the table portion of the window while the query is being executed.

Second, I never want the default query to run, so as soon as the query is shown in the query window, I start modifying it. Then when the query results return, focus is RIPPED away from my and put in the query results window. WHY?

FIX: create the query results window as soon a the query text is ready and displayed. Then when I am modifying the query and the original query results return, don't remove focus from the query editing field.

Third, when I re-run the query, I get zero feedback that it took my command to run the query again. I have to wait another 5-10 seconds for the query to return. All the while. I am sitting there wondering if the query is executing or not.

Fix: once the query starts running, add a spinner at the top of the query results window until the query returns.

I can't under state how hellish these issues make using Azure Data Studio with remote servers.

Azure Data Studio version: azuredatastudio 1.13.0 (cacd481bff25c890e4130aee392569bae43b7e60, 2019-11-01T23:25:46.501Z) OS version: Darwin x64 19.3.0

System Info |Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i5-4308U CPU @ 2.80GHz (4 x 2800)| |GPU Status|2d_canvas: enabled
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
multiple_raster_threads: enabled_on
native_gpu_memory_buffers: enabled
oop_rasterization: disabled_off
protected_video_decode: unavailable_off
rasterization: enabled
skia_deferred_display_list: disabled_off
skia_renderer: disabled_off
surface_synchronization: enabled_on
video_decode: enabled
viz_display_compositor: disabled_off
webgl: enabled
webgl2: enabled| |Load (avg)|310, 102, 45| |Memory (System)|16.00GB (5.10GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|
Process Info ``` CPU % Mem MB PID Process 33 229 70626 azuredatastudio main 1 49 70627 gpu-process 0 311 70630 window (Welcome) 3 229 70701 extensionHost 0 49 70714 /Applications/Azure Data Studio.app/Contents/Resources/app/extensions/mssql/sqltoolsservice/OSX/2.0.0-release.30/MicrosoftSqlToolsServiceLayer --log-file /Users/greg/Library/Application Support/azuredatastudio/logs/20200121T221314/exthost1/Microsoft.mssql/sqltools.log --tracing-level Critical 0 33 70715 /Applications/Azure Data Studio.app/Contents/Resources/app/extensions/mssql/sqltoolsservice/OSX/2.0.0-release.30/SqlToolsResourceProviderService --log-file /Users/greg/Library/Application Support/azuredatastudio/logs/20200121T221314/exthost1/Microsoft.mssql/resourceprovider.log --tracing-level Critical 0 33 70716 /Applications/Azure Data Studio.app/Contents/Resources/app/extensions/mssql/sqltoolsservice/OSX/2.0.0-release.30/MicrosoftSqlToolsCredentials --log-file /Users/greg/Library/Application Support/azuredatastudio/logs/20200121T221314/exthost1/Microsoft.mssql/credentialstore.log --tracing-level Critical 3 82 70697 shared-process 0 0 70978 /bin/ps -ax -o pid=,ppid=,pcpu=,pmem=,command= 5 82 70725 window (Issue Reporter) ```
Workspace Info ``` ; ```
Extensions (3) Extension|Author (truncated)|Version ---|---|--- import|Mic|0.12.0 server-report|Mic|0.2.1 whoisactive|Mic|0.1.3 (1 theme extensions excluded)
adsbot[bot] commented 4 years ago

Thanks for submitting this issue. Please also check if it is already covered by an existing one, like:

gregveres commented 4 years ago

@anthonydresser If you think this is a performance issue you are looking at it wrong. This is a usability issue that should be really simple for a developer to fix. I really don't mind that the query takes that long to execute - 3 to 5 seconds isn't bad. It is the way the product behaves during those 3-5 seconds that is the problem.

Now, I assume that this would be fixed pretty quickly if the developers working on it all had to endure a slow connection to their test database servers. I am going to go out on the limb and guess that all of your database servers are either right on your local machine or down the hall on blazingly fast servers over blazingly fast network connections. Stop developing that way - inject a 3 second delay for every server response and try using the product for a week. You will go insane.

Here is what would really stop the insanity:

  1. when you initiate a query, open the window immediately and put a spinner in the window until the query string is available
  2. when the results come back, don't rip focus away from the window that has focus.
  3. when the query is running, put a spinner in the query results window to show that the query is running
  4. don't auto-run queries - give me a chance to change the query before it runs for the first time. I want the results of the default query maybe 1% of the time.

If those things were changed in the behavour of the app, then nothing would have to change on the performance side.

gregveres commented 4 years ago

I see that some performance work was done in the April 2020 release and building the query and executing the query seem to be significantly faster. Thank you for that.

However, that doesn't change the need for this item. This is not a performance issue, even though somebody flagged it as such. It is a UI issue that I just don't understand why it isn't really easy to fix. Open the window and put a spinner in it before touching the server. I have seen other people asking for the same behaviour. You can make the code as fast as you can, but you can never completely hide the latency of contacting the sql server, when that machine is being connected to across the internet.

alanrenmsft commented 1 year ago

@gregveres I added a simple in progress indicator for the scripting actions, does this help? scripting-progress

gregveres commented 1 year ago

Yes, that would be great. It's nice that it says up until the query finishes too.

What does it do to the focus? One of my other complaints is that as soon as the query completes, the focus is stolen from the query edit area.

Why is focus important? It is because I NEVER want the default response of the query. I never want to see the first 1000 entries in the table and I rarely want to edit the first 200 rows of the table. So, my workflow is always:

But thanks for the progress bar. That will help a lot.

alanrenmsft commented 1 year ago

@gregveres I just tried the latest insiders build, it doesn't steal the focus away from the editor. could you please try again?

gregveres commented 1 year ago

I finally got a moment to try the insider build (sorry for the delay, I was on vacation last week). It definitely steals focus. Here are my steps:

  1. Right click on a table and select "Edit Data"
  2. when the new tab finally opens, click on the "Show SQL Pane"
  3. start editing the query by inserting a where clause
  4. at some point the query will return and the results table will be populated.

Once the table is finished being populated, the first cell of the table is given focus. This steals the focus away from the sql editing panel. This isn't too bad if I have finished typing the where clause, but often I am in the middle of typing the where clause and then all of a sudden I am typing into the contents of the first cell. If this is a string based cell, then what I type starts to overwrite the contents of that cell.

This is the about info for the insider version I just tried:

Version: 1.45.0-insider (user setup)
Commit: 55b8e3f6372175032393418d64301eb5c301b1c0
Date: 2023-07-06T06:10:48.337Z
VS Code: 1.79.0
Electron: 22.3.14
Chromium: 108.0.5359.215
Node.js: 16.17.1
V8: 10.8.168.25-electron.0
OS: Windows_NT x64 10.0.22621
alanrenmsft commented 1 year ago

@smartguest @cheenamalhotra could you please take a look?

cheenamalhotra commented 1 year ago

Hi @gregveres

Regarding the 3 issues you mentioned - here's some update:

First, when I want to query a table, I will right click on that table and select menu "Select Top 1000". Once I do that, I sit and wait 3-5 seconds before a window even appears. That is way too long to wait for some feedback that the system actually accepted my input.

The reason for this delay is likely related with new connection being made with server on every action. In the upcoming release (July '23) we've added support for enabling connection pooling, which can be enabled on demand - and once you enable this setting, you should see improvement in this area:

image (Currently disabled by default, will be enabled in future releases - available in Insiders as of today)

We've also added feedback notification on action execution. If you continue to experience unexpected delays here, please upload MSSQL Log file captured as per instructions in the next comment.

Second, I never want the default query to run, so as soon as the query is shown in the query window, I start modifying it. Then when the query results return, focus is RIPPED away from my and put in the query results window. WHY?

We will address this soon as the behavior is not expected.

Third, when I re-run the query, I get zero feedback that it took my command to run the query again. I have to wait another 5-10 seconds for the query to return. All the while. I am sitting there wondering if the query is executing or not.

There are some cases where intermittent delays are observed, and we are actively investigating the same. Related issues: #23400 #23401

github-actions[bot] commented 1 year ago

We need more info to debug your particular issue. If you could attach your logs to the issue (ensure no private data is in them), it would help us fix the issue much faster.

  1. First open the Settings page, find the Mssql: Tracing Level setting and change that to All then restart Azure Data Studio.
  2. Open command palette (Click View -> Command Palette) and run the command Developer: Set Log Level... and set the level to Trace.
  3. Run the repro scenarios.

  4. Collect the following logs:

    Console Logs

    • Open Developer Tools (Help -> Toggle Developer Tools)
    • Click the Console tab
    • Click in the log area and select all text (CTRL+A)
    • Save this text into a file named console.log and attach it to this issue.
    • Developer Tools can be closed via Help -> Toggle Developer Tools

    Application Logs

    • Open command palette (Click View -> Command Palette)
    • Run the command: Developer: Open Logs Folder
    • This will open the log folder locally. Please zip up this folder and attach it to the issue.
  5. Now you can revert the Log/Tracing levels changes made in step 1 and step 2.
gregveres commented 1 year ago

The reason for this delay is likely related with new connection being made with server on every action. In the upcoming release (July '23) we've added support for enabling connection pooling, which can be enabled on demand - and once you enable this setting, you should see improvement in this area:

I thought the connection pooling was already released. I thought I saw that in the release notes. I have noticed in the latest update that it is quite a bit faster. Maybe you are saying that it is going to get even faster.

We've also added feedback notification on action execution. If you continue to experience unexpected delays here, please upload MSSQL Log file captured as per instructions in the next comment.

Yes, that is this issue we are commenting on. I have seen it on the insider build. It is good. I would have put the pop up on the left side of the window rather than the right side of the window, but ... The reason for the left side is because that is much closer to the text I am editing and it is in my sight line. Way over in the lower right corner is not in my sight line. I have my window maximized on a 2500x1440 screen.

Second, I never want the default query to run, so as soon as the query is shown in the query window, I start modifying it. Then when the query results return, focus is RIPPED away from my and put in the query results window. WHY?

We will address this soon as the behavior is not expected.

That was the main feedback I was giving in my comment, that this focus ripping is very annoying.

Third, when I re-run the query, I get zero feedback that it took my command to run the query again. I have to wait another 5-10 seconds for the query to return. All the while. I am sitting there wondering if the query is executing or not.

There are some cases where intermittent delays are observed, and we are actively investigating the same. Related issues: #23400 #23401

Once the connection pooling shows up, I will keep an eye out for delays and file a bug when I see them.

However, delays can be tolerated with the proper UI. For instance, when I click on the "Run" button after making a change to the sql script, the button should instantly become disabled. Ideally it would turn into a spinner and stay a spinner until the query completes.

But right now, the Run button remains enabled while the connection to the server is established (or something) and this gives me no feedback that the system is doing something with my run command.

cssuh commented 1 year ago

Second, I never want the default query to run, so as soon as the query is shown in the query window, I start modifying it. Then when the query results return, focus is RIPPED away from my and put in the query results window. WHY?

We will address this soon as the behavior is not expected.

That was the main feedback I was giving in my comment, that this focus ripping is very annoying.

I just tested in the latest release and I'm not seeing this issue, the focus stays in the query editor.

Third, when I re-run the query, I get zero feedback that it took my command to run the query again. I have to wait another 5-10 seconds for the query to return. All the while. I am sitting there wondering if the query is executing or not.

There are some cases where intermittent delays are observed, and we are actively investigating the same. Related issues: #23400 #23401

Once the connection pooling shows up, I will keep an eye out for delays and file a bug when I see them.

However, delays can be tolerated with the proper UI. For instance, when I click on the "Run" button after making a change to the sql script, the button should instantly become disabled. Ideally it would turn into a spinner and stay a spinner until the query completes.

But right now, the Run button remains enabled while the connection to the server is established (or something) and this gives me no feedback that the system is doing something with my run command.

When you hit the Run button, there is a message in the bottom right hand corner saying "executing query".

@gregveres seems like the issues have been addressed in 1.45.1, so closing this issue. Please feel free to open a new issue if you have other bugs / feature requests you'd like to report.

gregveres commented 1 year ago

Second, I never want the default query to run, so as soon as the query is shown in the query window, I start modifying it. Then when the query results return, focus is RIPPED away from my and put in the query results window. WHY?

We will address this soon as the behavior is not expected.

That was the main feedback I was giving in my comment, that this focus ripping is very annoying.

I just tested in the latest release and I'm not seeing this issue, the focus stays in the query editor.

@cssuh I just installed 1.45.1 and the behaviour is still there. Let me give you the reproduction steps again:

Should I open another issue for this? I think I may have already done that, but I can check.

But right now, the Run button remains enabled while the connection to the server is established (or something) and this gives me no feedback that the system is doing something with my run command.

When you hit the Run button, there is a message in the bottom right hand corner saying "executing query".

@gregveres seems like the issues have been addressed in 1.45.1, so closing this issue. Please feel free to open a new issue if you have other bugs / feature requests you'd like to report.

Yes, I see that the feedback is in 1.45.1. That's good feedback. Thanks!