Performance Improvement Wishlist

DanW564

In a nutshell, I wish SQL Source Control (SSC) could isolate context of changes similar to the way TortoiseSVN does so for my working directory on my C:\ drive. Virtually all of my source control experience comes from TortoiseSVN, so I'll admit there's probably some of "I only have a hammer, so everything looks like a nail" going on. I've learned a little about Git, and the Bitbucket Server implementation of Git, with all of it's branching and pull requests. It sound intriguing and all, for compile-your-software development, but to me the Subversion paradigm seems to be a closer fit to databases, or at least to the databases that I support.

I just spent some time over a couple of days investigating SQL Source Control to see if it might be an improvement to our current release process, or if it might provide any increased control over changes in a single environment, where there are 15 people that could be making development changes on any given day. I see glimmers of hope for providing better options, but the performance delays seem to be too much to be workable. However, I'd like to throw out some suggestions for what I see as opportunities for improvement of the interface that could really allow this product to scale and shine.

My biggest pain point is the delay of the Commit and Get latest tabs of the SQL Source Control Window. The Migrations tab might have a similar issue, but I haven't looked at it.. I'm just going to focus on the Commit tab. Each time focus changes away from Commit, or changes to another database in Object Explorer, the whole refresh process looks like it starts over from the beginning. I just timed two similar refreshes from two different databases that are each linked differently. One took 45 seconds, while another took 3 minutes. In the mean time, my only real option for doing anything else with SSMS is to open and use a second window so that the refresh can actually finish. So, it seems to me that it must be re-evaluating the status of all objects, comparing my database to my subversion server (because I linked to SVN for Shared mode). Even so, I can tell the SQL Source Control just be maintaining some sort of cache of object statuses, because I see the little blue dots in Object Explorer only on the categories and specific objects that are recognized as having changes to commit.

So, that brings me to wishlist item #1: Use the cache (that appears to be built in the background asynchronously, which is ideal) to display the list of objects with changes to commit. I'm looking at a list of 30 objects that are showing up because of a change to my filter, which were all identified in the Object explorer before I ever clicked on the commit tab. If that was refreshed from a cache, 30 objects should pull up instantaneously.

If I got wishlist #1, that just might be enough for this to be workable. However, I'm still going to list a couple other ideas that seem like easy wins.

#2: Provide a way to isolate or pin the current context of the SQL Source Control window so that while it's working, I could click on whatever else I wanted to in SSMS without stopping the display process. If the display was instantaneous, this might not matter, but I still find it frustrating that I can't tell SSC to stay put while I go look at something else. Why not just provide a drop-down selection for the database to focus on instead of piggy-backing off of my context in Object Explorer?

#3: Let me narrow or isolate the context of the changes I want to consider committing, separate from changing the filter that already exists. The current filter controls what is considered to be under source control, which is good, but I would like to also be able to search or filter the list of changes to consider what I want to commit right now. In our team environment, I might be looking for one out of a hundred pending changes. One idea/example for how to narrow the context would be drilling into my list of views, seeing blue dots on two views, and being able to quickly initiate a commit of both views by right-clicking on "Views" (the same way if I right click on a folder in Windows Explorer, TortoiseSVN only pulls up a list of changes identified in that folder and child folders). Another idea would be to be able to right-click on a single view object to initiate a commit of only the changes of that view. Of course, but just to be clear, this idea is about any of the various database object types, not just about views.

For the databases I work on, many objects are independent of each other, so those of us on my team often don't have to keep change A in lock-step with change B, so isolating context is often needed. I could see SSC working well for situations where the database is just part of an application that gets distributed, but that's not my use case.

Thank you to everyone at RedGate that has provided so many awesome tools to help with database development and support. SQL Compare is a lifesaver, and SQL Prompt is a breath of fresh air to database development. Right now SQL Source Control is so close, yet so far out of reach, that I wanted to share what keeps it from being workable for me and how it seems feasible to make it workable for situations like mine. Please keep up the great work at RedGate!

My environment specs:
A data warehouse type of database with about 9000 objects in the initial commit
Both the database and subversion servers reside in a remote data center with a very high speed WAN connection between my location and the data center
SQL Server 2014
Subversion Server version unknown, but likely quite old
SQL Source Control version 6.2.3.7820
SSMS version 17.3
Windows 7, 64 bit Core i7, 16 GB Ram, SSD local drive

way0utwest

I can't quite speak for the team, but I've been working with SQL Source Control for years, and I feel quite a bit of pain. I've had similar issues, and discussions about these items, so let me try and explain a bit. Some of this might be different for different SSMS versions (and Source Control, SQL, etc.)

#1 - I've debated this and talked deeply through the cache items. The blue dots are really a note of an event. SQL Source Control (SOC) detects a change and adds the blue dot. This is a polling process, but if you undo a change or someone else changes something, your SSMS doesn't know something changed. At least not in the moment.

The same argument goes with I've changed proc x and it's marked. That's in my cache.Now another change occurs to a table referenced by proc x, perhaps removing a column that is referenced. Without a check, my SOC doesn't know this has happened. We commit a change, potentially seeing or not thinking the table change is related, and we have problems.

As much as I'd like to say "don't recheck the repo v database", I also know this would likely cause no shortage of complaints and issues, especially in shared environments. Imagine SSMS crashes, which we have to account for. If you then go to commit, are we sure the cache is correct?

This is really an issue. Ideally we'd be able to read an XE session of all changes that have occurred, but that's a really hard problem.

Conflicts become even more of an issue with shared work. Multiple developers could end up committing the same work. I wish this were simpler,but it's not, and databases are similar to, but vastly different than app code (C# ,Java, etc).

I agree with you we ought to do something, but every time this gets tackled, we struggle with finding a solution that actually protects code and scales to multiple developers. If you always work dedicated, I think this an easier thing to solve, though even then you'd have to say only SSMS can make changes.

#2 - SSMS is a piece of manure in some ways. We're hacked in there and have some limits in what we can do with the UI. That's part of the issue, though I'd like to think that what we ought to do here is disconnect from OE. Just have a drop down and force you to do that every time. We've had some UI feedback that people like the "current" database to be the one SOC finds, but over time I think this is bad. I'd rather explicitly select this.

I have little confidence I could make this change work, but if you can rally more votes - https://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/35143396-disconnect-the-soc-pane-from-object-explorer

#3 - filtering by user would be nice, for sure. The problems this introduces are that potentially you are committing work that is incomplete and problematic. I get that you want to say "I only want to commit change x", but think if changes y and z are related/dependent? In a shared environment especially, this is dangerous. When teams work, they conflict more often than they realize, and one reason that the object locking was highly requested early on. Teams just don't communicate enough.

I think the UI to easily/quickly limit objects in some filter isn't necessarily easy, but I do think that perhaps a "search" feature for commits might eb nice, allowing me to see specific changes and commit those. The hard part of implementing this is that I search for "Sales" and click the Sales table. However, I want to also commit "GetWeeklyTotals" and search for that. Keeping a running list along with a changing list of selected items is likely a larger UI change than anyone wants to make.

That being said, if you wanted to propose a fix like that, I could be convinced to vote. The hard part here, and why I think a shared database with source control is a problem, is that you are potentially making changes, as are others, and until you commit, it's possible someone else will overwrite your change. It's also possible that they won't realize that their change later might conflict with yours if they can limit filter too many things.

DanW564

way0utwest, thank you for your thoughtful response and your work on this over the years. I voted for and commented on item you mentioned.

Re#1: I think I hear your concern. Essentially, I hear you saying that you need to guard against users blaming you for something going wrong because the tool didn't protect them from something they thought it would protect them from. I get that, but I'm also confident that it's possible to cause damage with any tool, and you can't completely protect us from ourselves.

Here's a compromise idea that I would be thrilled about: What if the cache of identified items was used to quickly identify objects, and initially, only those objects were re-checked (database vs repo)? Then, this short list was displayed, but the full re-check was initiated. While the full re-check was running, allow for an option to stop/cancel the full re-check (such that we can't commit unless the full refresh is cancelled), but show some warning that the user has to acknowledge before the full re-check gets stopped (maybe even log it to protect yourselves...?). That way, when users complain that something broke because of a change someone else made that they were not aware of, at least they know, and you can remind them, that they intentionally disabled your safety net. If something like that was implemented, I'd have a new lease on the possibility of using SQL Source Control in my current job.

With any source code repository (at least as I understand it), it's possible to commit a change that will have a conflict with a commit done by someone else. We have to do an Update (SVN) or Pull (Git) to see changes potentially done by someone else. Relative to that, I see cached list of recognized changes as analogous to my working copy folder (SVN) or local repository (Git), so I don't see trusting the cache to identify differences between the database an the linked repository to be all that big of deal. Wouldn't any repository warn of a conflict if someone committed a different change to the same object/file, or at least a change that couldn't be automatically merged?

Re#3: I wasn't trying to ask for a filter by user, only by object type, or a way to commit a single object, based on the changes identified by the polling process.
I tried yesterday to post a new idea to the uservoice site, but the post idea button appeared to not be working for me. I tried both Chrome and IE, but neither worked from what I could see. Either that, or there are several new ideas posted by me with no real content that I couldn't find with the search.

way0utwest

A little, busy, but let me respond to #1 for now.

I get your point with VCS. We do get merge conflicts, and we catch things. The problem with checking the cache objects only is twofold.

1. The comparison engine (which is also the basis for SSDT, and some other competitor objects) can't do that. It needs a full dependency tree to work, which can be off because of changes not made on this instance, so that's most of the time. Re-reading the local file system is also slightly slow for comparison, though arguably there might be shortcuts that can be taken to check DateModified, names of objects, etc.

The issue for trying to catch things in the VCS occurs with related objects. Let me give you an example.

2:00 Dan changes the proc to include a NoLock (please don't do this, Dan)

2:01 Sally opens the proc in SSMS. She has the old versions

2:02: Dan commits the proc to SVN

2:03 Sally changes the proc to add a TRY..CATCH around an update

2:04 Sally tries to commit (or push in git) - gets merge conflict.

This is easy. Cache would make both commits quick, though Sally's check would be longer because there would an actual comparison between the SVN file and the object code from the server.

What about this:

2:00 Dan changes the proc to include a NoLock (please don't do this, Dan)

2:01 Sally opens a table referenced in the proc in SSMS. Removed a column being used.

2:02: Dan commits the proc to SVN. I just check the cache, see no changes to worry about.

2:04 Sally tries to commit (or push in git) - works fine

Downstream things are broken. Obviously CI catches this, deployments will catch this, but imagine it's dependent logical changes. Those are a problem  We want to let you know that there are issues. Having a cache can help, but the cache can be really out of dates, especially in the cases where SSMS might die. We'd have to have a transactionally consistent cache.

That being said, while anyone can mess up any tool, our job is to minimize the places where the tool could lead you to make mistakes.

I would love to get more resources to fix some of these things in SOC, and I regularly complain, but I have limited success. I continue to push, but having more Uservoice votes for things would be good. I'll pass this along, but not confident I'll get anything fixed anytime soon. There is a lot of legacy code, and I suspect a cache based system might be really difficult to implement.

As a side, my vote was to write this in a local log file, as a key-value store. If I commit, remove the items committed. If I get latest/pull, remove items as well. If I detect a change, add an item.That's slightly slower, but gives me a physical list that I can also troubleshoot instead of a cache that gets wiped on process end.

Re #3 - A search by whatever would be nice. Just type "Sales" or "Dan" and get a filtered list in the changes dialog.

One thing I can suggest is press the sales side for these changes. The more voices, the more chance I can convince someone to allocate resources.