What are the challenges you face when working across database platforms? Take the survey
Options

Does the command line /Include switch have some massive inefficiency?

TheGTTheGT Posts: 4 Bronze 1
The /Include switch is meant to reduce the comparison to specific objects. I have a database with thousands of stored procedures, and when I set it to compare only a single stored procedure, it takes over 107 seconds to do the compare. If I use the same command on a database with far fewer objects, it takes a few seconds.

Example command line arguments that take 107 seconds: (the source is a folder with 1 file containing just the specified stored procedure)
.\SQLCompare.exe /scr1:"C:\GitRepos\TempDBScriptsForComparison" /scr2:"C:\GitRepos\MYDB" /Synchronize /IgnoreParserErrors /Options:IgnoreFillFactor,IgnoreWhiteSpace,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName /Include:StoredProcedure /Include:StoredProcedure:"\[dbo\]\.\[my_stored_proc\]"

The first /Include:StoredProcedure is supposed to make it ignore all other object types
The second /Include:StoredProcedure:[dbo].[my_stored_proc] is supposed to make it only compare that one proc

While the compare ultimately works, the fact that it takes 107 seconds, and that time scales up based on database size, indicates something with the /Include filtering is not working efficiently. The object name is specified so it should take no more than the time it takes to compare the one object definition, clearly it is reading through more/all? the objects on the target which should not be read due to the /Include.

Tagged:

Answers

  • Options
    David AtkinsonDavid Atkinson Posts: 1,451 Rose Gold 2
    edited July 20, 2023 8:52AM
    Although you're selecting a subset of objects SQL Compare needs to read and parse the entire schema to build a dependency tree so it can correctly ensure that when the deployment script is generated that it can pull in the dependencies. This is why there's no performance improvement. That's not to say there couldn't in future be a 'mode' implemented in the tool that would allow a quicker comparison, although it couldn't then guarantee as high a quality of deployment script as it wouldn't have read in the dependencies.
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    TheGTTheGT Posts: 4 Bronze 1
    Thank you for the reply. Since I'm not using the default options I'm not using IncludeDependencies, so it would make a lot of sense if the compare would not spend time doing the dependency tree when all I need to do is directly compare a short list of a few object definitions. If such a mode gets added it would be great!
Sign In or Register to comment.