Schema Compare slow and then fails

[Whalee110]

Type: Bug

Using Schema compare on an object is quite slow compared to ADS version 1.44 (Which the extension no longer works for)

Previously clicking the "compare" button would show results within a few minutes, now it can take over 30 minutes. Once compare is complete and changes are selected choosing the "apply" option has no effect or output with the button becoming possible to press after a few minutes.

Expectation is, compare loads and then apply will update the project with the changes.

The --trustserver flag was testing to see if helped it at all but it did not. Same outcome without it.

Extension version: 1.21.0 Azure Data Studio version: azuredatastudio 1.46.1 (ba29842b81dec01177415e53948ca2168e69c3f8, 2023-10-02T18:14:22.887Z) OS version: Windows_NT x64 10.0.22621 Restricted Mode: No Preview Features: Enabled Modes:

System Info

|Item|Value| |---|---| |CPUs|12th Gen Intel(R) Core(TM) i7-1255U (12 x 2611)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|31.64GB (18.50GB free)| |Process Argv|--trust-server-certificate| |Screen Reader|yes| |VM|0%|

[kisantia]

@Whalee110 was schema compare also slow in ADS 1.45.1?

If needed, you can download older versions of the schema compare extension and manually install it, which will work with ADS 1.44 ex: https://sqlopsextensions.blob.core.windows.net/extensions/schema-compare/schema-compare-1.19.0.vsix

[Whalee110]

Thank you for that, that will help alot - The extension that upgraded though was SQL Projects - Do you have a link for that one too?

The Schema Compare extension did seem to break where it cut off most of the output window so I could only "see" 1 row at a time, but I believe that is more likely from extensions updates so I will give that a try.

If there are any logs I can provide for info on the above let me know! I couldn't find any info except the internal logs saying "check logs"

[SakshiS-harma]

@Whalee110 If you are on 1.3.1 of SQL Projects and want to test with the older version, you can try: https://sqlopsextensions.blob.core.windows.net/extensions/sql-database-projects/sql-database-projects-1.2.0.vsix to access it. It is compatible with ADS 1.44

[Whalee110]

An update that may help.

I downgraded ADS to 1.45.1 and downgraded all extensions to work, Schema Compare 1.19.0 and Database Projects 1.1.1 - This has no change in the performance in running Schema Compare within database projects still took well over 30 minutes and then would not function when attempting to "apply"

Downgrading ADS to 1.44.1 and Schema Compare 1.19.0 with Database Projects 1.2.0 completes the compare within a few minutes as expected but the "apply" is still not functioning in updating the project. I am going to attempt another clean install then try again.

[Whalee110]

So Schema Compare taking excessive time seems to be tied to ADS 1.45.1 +

Apply not working based on developer tool console seems to occur when there is an error during the compare selecting what to include etc. It then gives this output when you attempt to hit apply. (This is when I have loaded a .scmp files or started from scratch but have selected and deselected what you want to update)

[[object Object]]Cannot read properties of undefined (reading 'sourceValue')

If I don't deselect any items the apply works as intended.

[SakshiS-harma]

@Whalee110 as for the apply not working- this is currently a known issue where include/excluding changes takes some time in the background (there is no visual notification of if the include/exclude calculation has finalized). So, waiting out a little bit (a couple of mins may be depending on how big the schema is) and then trying Apply should ideally work. Do let us know if that doesn't work. Thanks!

[Whalee110]

I am currently using 1.44, Database projects 1.1.1 and Schema compare 1.19.0. This combination functions as intended.

Database projects 1.3.1 / Schema compare 1.21.0 breaks the compare, within ADS 1.44 the compare window becomes incredibly small only showing at most 1 row at a time in the "Selection" and you cannot adjust it's size. (This may be due to the change in Schema compare that made the window resizable? - that was only other issue I saw similar to it)

Updating to 1.45 or 1.46 fixes that but then the compare only portion goes from a minute or so to take 30 minutes.

The apply does take a big chunk of time with no visual in both, so that itself isn't too bad but the 30 minute wait just for the compare is a huge impact.

[kisantia]

The DacFx version used by schema compare was updated in insiders and some performance slowdowns were fixed - @Whalee110 can you try using the latest insiders release and see if it's any better?

If not, then there a couple other things we'll ask you to try to help pinpoint where this slowdown was introduced in the DacFx codebase.

[Whalee110]

Yep I will give the insider release a go, can't do right now because of work but I'll do asap and provide an update.

If there are tools or the like that would help point to the issue I can do that too :)

[kisantia]

thanks for the update @Whalee110!

Here's the other tool to try out, in case insiders is still much slower than the 1.44 release. For some context, schema compare uses DacFx in the backend to do the comparison. The command line tool sqlpackage also uses DacFx, with the script action being similar to a schema compare generate script.

• ADS release 1.44 is using a DacFx version just before the Sqlpackage 162.0.52 version
• Current ADS insiders (1.47-insiders) is using the same DacFx version as SqlPackage 162.1.167

If you could try doing the sqlpackage script action with these two version of sqlpackage and see if there's any significant difference in the time it takes, then that would be very helpful. Also use the /df parameter to collect logs, so that this will log where time is spent. Note that sqlpackage only works on databases and dacpacs, not currently for sql projects.

[Whalee110]

Sorry I haven't provided update. We are currently undergoing a migration and strict timelines doesn't allow too much time to try the various packages / updates.

I will try get to it this week

[christophesnowflake]

We are currently experiencing the issue with our biggest project (schema compare hangs, and ADS crashes after consuming 12GB+ memory.

it there updates on planned release dates and/or possible workaround ?

[Whalee110]

thanks for the update @Whalee110!

Here's the other tool to try out, in case insiders is still much slower than the 1.44 release. For some context, schema compare uses DacFx in the backend to do the comparison. The command line tool sqlpackage also uses DacFx, with the script action being similar to a schema compare generate script.

• ADS release 1.44 is using a DacFx version just before the Sqlpackage 162.0.52 version
• Current ADS insiders (1.47-insiders) is using the same DacFx version as SqlPackage 162.1.167

If you could try doing the sqlpackage script action with these two version of sqlpackage and see if there's any significant difference in the time it takes, then that would be very helpful. Also use the /df parameter to collect logs, so that this will log where time is spent. Note that sqlpackage only works on databases and dacpacs, not currently for sql projects.

Apologies for the delay - I tried the insider version however there was no change in outcome. Still an excessive time to run the compare.

I will try doing it with logs when time allows!

[aistvan]

My experience is with the latest version of ADS with the latest version of Schema Compare still doesn't works. Years (!) ago our dev and production databases compare took about several minute. Now the same process still in progress in the last 6 hours. Not acceptable.

Version: 1.48.0 (system setup) Commit: 4970733324ef8254b7c22a5dc55af7f8a1dea93f Date: 2024-02-27T00:05:08.293Z VS Code: 1.82.0 Electron: 25.9.8 Chromium: 114.0.5735.289 Node.js: 18.15.0 V8: 11.4.183.29-electron.0 OS: Windows_NT x64 10.0.22631

[ChrisPalmerNZ]

I am also on ADS 1.48.0 and schema compare 1.21.0, and I am getting an error "Apply schema compare changes failed....Error: index and length must refer to a location within the string. (Parameter 'length')" when I apply changes from the database to the project. This is covered in issue #22957. Is there a recommendation for an older version of schema compare that will work with ADS 1.48?

[Whalee110]

So recently I updated ADS and Database projects / Schema compare to test and it works again. Clicking apply still returns a build error but that has always been the case, it now completes in a reasonable time.

For those still with issues these are the versions I am using: Azure Data Studio Version: 1.48.1 (system setup) Commit: 8825ec21e67a34ede00e4f02a4afc692eb6cab61 Date: 2024-06-13T21:07:19.253Z VS Code: 1.82.0 Electron: 25.9.8 Chromium: 114.0.5735.289 Node.js: 18.15.0 V8: 11.4.183.29-electron.0 OS: Windows_NT x64 10.0.22631

Extensions Microsoft.sql-database-projects 1.4.2 Microsoft.schema-compare 1.21.0 Microsoft.agent 0.49.0 Microsoft.mssql 0.1.0

I reverted the change, but in case it was something that helped I did first use the experimental setting "affinity" for extensions - setting mssql and schema compare to 1