Queries run slow

[joshuaquiz]

Issue Type: Performance Issue

All SQL queries take a couple seconds to start in the UI and the UI can become unresponsive. The actual query says it executes fine, but the UI can take a really long time to display results, render the list, or respond.

Azure Data Studio version: azuredatastudio 1.7.0 (e1280022d69b651cfff04b30e830904575c8acda, 2019-05-08T00:55:40.928Z) OS version: Windows_NT x64 10.0.17134

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-6800K CPU @ 3.40GHz (12 x 3398)| |GPU Status|2d_canvas: enabled
checker_imaging: disabled_off
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
multiple_raster_threads: enabled_on
native_gpu_memory_buffers: disabled_software
rasterization: enabled
surface_synchronization: enabled_on
video_decode: enabled
webgl: enabled
webgl2: enabled| |Load (avg)|undefined| |Memory (System)|23.91GB (3.74GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

Process Info

``` CPU % Mem MB PID Process 0 59 19008 azuredatastudio main 0 27 7188 shared-process 0 108 12540 gpu-process 1 1073 20976 window (● SQLQuery_1 - Generic2.leadcrumb_odiamonds (DevGroup_ReadOnly) - Azure Data Studio) 2 220 19728 extensionHost 0 8 6060 "c:\Users\joshu\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.91\MicrosoftSqlToolsCredentials.exe" --log-file C:\Users\joshu\AppData\Roaming\azuredatastudio\mssql\credentialstore_19728.log --tracing-level Critical 0 1 20852 console-window-host (Windows internal process) 9 192 14040 "c:\Users\joshu\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.91\MicrosoftSqlToolsServiceLayer.exe" --log-file C:\Users\joshu\AppData\Roaming\azuredatastudio\mssql\sqltools_19728.log --tracing-level Critical 0 1 10356 console-window-host (Windows internal process) 0 5 15392 "C:\Users\joshu\AppData\Local\Programs\Azure Data Studio\azuredatastudio.exe" "c:\Users\joshu\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\json-language-features\server\dist\jsonServerMain" --node-ipc --clientProcessId=19728 0 7 17004 "c:\Users\joshu\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.91\SqlToolsResourceProviderService.exe" --log-file C:\Users\joshu\AppData\Roaming\azuredatastudio\mssql\resourceprovider_19728.log --tracing-level Critical 0 1 18504 console-window-host (Windows internal process) 0 77 24140 window (Issue Reporter) ```

Workspace Info

``` ; ```

Extensions: none

[GrzegorzLyp]

I can confirm the problem on 1.8.0 and 1.9.0 (insiders).

[anthonydresser]

Can you provide information on what type of queries you are running? Large number of result sets (10+), large number of rows (order of millions), large number of columns (~30+), small queries, large number of messages (order of thousands), etc.

[GrzegorzLyp]

It is not about many rows or many columns. It happens that from pressing "Run" to start query timer it takes seconds. When I press Ctrl+N at the moment of waiting new query window is disconnected. So probably the first tab is disconnected and that is why it is slow. Maybe some connection indicator would be a solution (SSMS shows window on reconnecting).

[GrzegorzLyp]

Zip contains movie with the case where ADS is broken so I can't perform query or event reconnect. Second movie shows that after restart connections work fine. Tested on today update of ADS.

Version: 1.9.0-insider (user setup) Commit: e8d4fba3c06614585fcfdb75428f9ea5f5047412 Date: 2019-07-02T16:35:24.822Z VS Code 1.34.0 Electron: 3.1.8 Chrome: 66.0.3359.181 Node.js: 10.2.0 V8: 6.6.346.32 OS: Windows_NT x64 10.0.18362

Captures.zip

[cheenamalhotra]

Since the problem is generic and we've been working on fixing #7440 and related performance issues in Query execution, I will proceed to close this and recommend tracking #7440 for future reference.