Use sys.objects create/modify date to get new changes quicker
Using sys.objects you could determine objects which have been created/modified since the last check to speed things up
-
Bright commented
Commit takes 4-5minutes on out 20 000 objects database, making this tool practically unusable for us.
It is very possible to use modified date, if sql server is 2005 or higher, and comments are excluded from comparison. DML trigger are in sys.all_objects. Only rarely changing objects like DDL trigger, users etc which are very few in numbers are not in sys.all_objects.
You can give user a choice of such "quick compare" feature, and button "Full compare" to revert to old and slow "compare all" feature.
So, it is very possible to use modified date to dramatically, and I mean really dramatically speed-up the commit process.
If you do so, commit process should take sub-second time on 20 000 object db. -
mbryant commented
All of the things I need to track, tables, views and stored procedures are in there so if I could filter on just tracking those, theoretically that would make it quicker?
-
[Deleted User] commented
That's pretty much exactly what we do! Unfortunately not all objects are in sys.objects (e.g. triggers, assemblies, types, XML Schema Collections, etc) so we also poll the default trace and sys.types as well. The information we get from this is used to add things to the Object Explorer (if you've created something), and we also add a blue indicator to any object with changes.
The commit tab does a full search across your database, because some objects, such as full text catalogs, users, roles, etc, aren't in sys.objects, the default trace, nor sys.types. Also, changes to extended properties don't update the modification time of the object in sys.objects. Therefore, to be sure that we've detected absolutely every possible change, we do a full search across the database.