Notebook cells are taking more time to display results

[sumngh]

Issue Type: Performance Issue

In case of notebook, if we have executed cell 1 query its taking more time due to performance issue in connecting to DB the same query is running fine in SQL Server management studio. Azure data studio should be faster then SSMS app but isntead its very slow and not much useful as same as other Azure applications.

Azure Data Studio version: azuredatastudio 1.35.0 (e9c8eb8564f537726b36b5f15c7f35e62f229284, 2022-02-23T17:36:02.883Z) OS version: Windows_NT x64 10.0.19043 Restricted Mode: No

System Info

Item	Value
CPUs	Intel(R) Core(TM) i5-7200U CPU @ 2.50GHz (4 x 2712)
GPU Status	2d_canvas: enabled gpu_compositing: enabled multiple_raster_threads: enabled_on oop_rasterization: enabled opengl: enabled_on rasterization: enabled skia_renderer: enabled_on video_decode: enabled vulkan: disabled_off webgl: enabled webgl2: enabled
Load (avg)	undefined
Memory (System)	15.78GB (2.69GB free)
Process Argv
Screen Reader	no
VM	0%

Process Info

CPU %	Mem MB	   PID	Process
    0	    69	 13568	azuredatastudio main
    0	   401	 12684	   window (● Notebook-EDW.ipynb - Azure Data Studio)
    0	    62	 14576	     extensionHost
    0	    12	 14432	       "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\3.0.0-release.207\MicrosoftSqlToolsCredentials.exe" --locale en --log-file c:\Users\Sumer\AppData\Roaming\azuredatastudio\logs\20220531T181818\exthost1\Microsoft.mssql\credentialstore.log --tracing-level Critical
    0	     6	 39868	         console-window-host (Windows internal process)
    0	    10	 20524	       "C:\Program Files\Azure Data Studio\azuredatastudio.exe" "c:\Program Files\Azure Data Studio\resources\app\extensions\json-language-features\server\dist\node\jsonServerMain" --node-ipc --clientProcessId=14576
    0	     8	 37756	       "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\3.0.0-release.207\SqlToolsResourceProviderService.exe" --locale en --log-file c:\Users\Sumer\AppData\Roaming\azuredatastudio\logs\20220531T181818\exthost1\Microsoft.mssql\resourceprovider.log --tracing-level Critical
    0	     6	 32044	         console-window-host (Windows internal process)
    0	   104	 40308	       "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\3.0.0-release.207\MicrosoftSqlToolsServiceLayer.exe" --locale en --log-file c:\Users\Sumer\AppData\Roaming\azuredatastudio\logs\20220531T181818\exthost1\Microsoft.mssql\sqltools.log --tracing-level Critical
    0	     6	 25132	         console-window-host (Windows internal process)
    0	    86	 15888	   window (Issue Reporter)
    0	   144	 19944	   gpu-process
    0	    11	 21224	   utility
    0	    48	 22624	   shared-process
    0	    10	 24488	     ptyHost

Workspace Info

;

Extensions (8)

Extension	Author (truncated)	Version
admin-pack	Mic	0.0.2
agent	Mic	0.48.0
dacpac	Mic	1.10.0
import	Mic	1.5.0
machine-learning	Mic	0.11.0
profiler	Mic	0.11.0
query-history	Mic	0.1.0
azdata-sanddance	msr	3.3.0

[sumngh]

I have tried with updated version of Azure data studio also, performance issue is still there.

[sumngh]

New Version: Azure Data Studio version: azuredatastudio 1.36.2 (https://github.com/microsoft/azuredatastudio/commit/2299a76973585fa962cfd06e8acab4abb41567de, 2022-05-19T04:18:20.649Z)

[chlafreniere]

Hi @sumngh do you have a sample query where you see significant time differences between SSMS and ADS (or generically, is there anything that this particular query may be doing that's not a simple, say, SELECT statement)?

I believe we have a known issue that we need to address around XML and vast differences in execution times, but don't have other categories of issues identified. Would love to understand if these same queries are also slow in the ADS query editor as well.

[sumngh]

Hi Chris,

No its not this specific query. You can try any query. Even a query as simple as SELECT GETDATE() is taking time. And the execution time shows by results in milliseconds thats also a strange part. So that means in every execution it’s taking time to verify connection or something related to same.

Thanks & Regards ••••••••••••••••••••••••••••••••••••••••••••••• Sumer Singh +91-8287014644

| Deal with the world the way it is, not the way you wish it was. |

••• From Mobile (Outlook)

On 02-Jun-2022, at 3:42 AM, Chris LaFreniere @.***> wrote:

 Hi @sumngh do you have a sample query where you see significant time differences between SSMS and ADS (or generically, is there anything that this particular query may be doing that's not a simple, say, SELECT statement)?

I believe we have a known issue that we need to address around XML and vast differences in execution times, but don't have other categories of issues identified. Would love to understand if these same queries are also slow in the ADS query editor as well.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.

[lucyzhang929]

Hi @sumngh, is this only happening for the first cell you run when you open a notebook? How long are the cells running for?

[sumngh]

Hi Team,

As I mentioned earlier there is no fixed time , my query was running for more then 15 mins, when it should get results in a min… And I have not tested everything from my end, I tried with one cell and faced issues.

Thanks & Regards ••••••••••••••••••••••••••••••••••••••••••••••• Sumer Singh +91-8287014644

| Deal with the world the way it is, not the way you wish it was. |

••• From Mobile (Outlook)

On 06-Jul-2022, at 3:05 AM, Lucy Zhang @.***> wrote:

 Hi @sumngh, is this only happening for the first cell you run when you open a notebook? How long are the cells running for?

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.

[lucyzhang929]

@sumngh For the query that ran for over 15 minutes, did it ever complete? Would you be able to try running that same query in a query editor and see if it also takes >15 mins?

I think it is expected that the first query ran will take longer however >15 mins when expected execution time is only 1 min is definitely odd.

I don't think this is a notebook specific issue. I tried a simple SELECT GETDATE() in both a notebook code cell and a query editor and the first time I ran both, the results took ~10s to return even though the execution time is <1s likely because of the new connection. Subsequent runs all take <1s to get results back.

[sumngh]

Hi Lucy,

But this time frame for initial load is also not correct, when the connection was already established when we login and opened the notebook, then why its spending the time again for connection while execution. Although my queries still takes more time as compared to normal SQL server studio. The Azure studio should be faster then the SSMS not slow.

Thanks & Regards ••••••••••••••••••••••••••••••••••••••••••••••• Sumer Singh +91-8287014644

| Deal with the world the way it is, not the way you wish it was. |

••• From Mobile (Outlook)

On 30-Aug-2022, at 11:59 PM, Lucy Zhang @.***> wrote:

 @sumngh For the query that ran for over 15 minutes, did it ever complete? Would you be able to try running that same query in a query editor and see if it also takes >15 mins?

I think it is expected that the first query ran will take longer however >15 mins when expected execution time is only 1 min is definitely odd.

I don't think this is a notebook specific issue. I tried a simple SELECT GETDATE() in both a notebook code cell and a query editor and the first time I ran both, the results took ~10s to return even though the execution time is <1s likely because of the new connection. Subsequent runs all take <1s to get results back.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.

[lucyzhang929]

Hi @sumngh,

Could you try running the same query in a query editor to see if the first run also takes longer there? You can open a new query editor using the keyboard shortcuts cmd+n or going to File > New Query.

I agree that the initial loading time should not take this long if the connection was already established. I would like to clarify if this is a notebook specific issue or a general connection issue in ADS.

[sumngh]

Hi Lucy,

I have checked the query again, the first time it's taking a very long time, and then some time it works fine and some time it takes more time even after the first attempt. THis is the same for query like "SELECT GETDATE()" this has nothing to do with any connection still it takes time.

Thanks & Regards *Sumer Singh | *

• +91-8287014644*

*|Deal with the world the way it is, not the way you wish it was.| https://www.facebook.com/sumngh https://twitter.com/sumngh * https://www.linkedin.com/in/sumngh/

• https://is.gd/GVxrfA*

On Wed, Sep 7, 2022 at 6:53 PM Lucy Zhang @.***> wrote:

Hi @sumngh https://github.com/sumngh,

Could you try running the same query in a query editor to see if the first run also takes longer there? You can open a new query editor using the keyboard shortcuts cmd+n or going to File > New Query.

I agree that the initial loading time should not take this long if the connection was already established. I would like to clarify if this is a notebook specific issue or a general connection issue in ADS.

— Reply to this email directly, view it on GitHub https://github.com/microsoft/azuredatastudio/issues/19586#issuecomment-1239385022, or unsubscribe https://github.com/notifications/unsubscribe-auth/AM622LCSHLRSCX5LNEIUGULV5CJMLANCNFSM5XQ2RHVA . You are receiving this because you were mentioned.Message ID: @.***>

[lucyzhang929]

@sumngh Did you run the query in a query editor or notebook? I would like to clarify if the issue lies in notebooks or elsewhere.

[chlafreniere]

Waiting on more info, so closing for now.