search

bugzinga / sql-hunting-dog

Quick Search Tool (AddIn) for Microsoft SQL Management Studio
http://www.sql-hunting-dog.com
Other
36 stars 18 forks source link

Allow loading of specified databases only #5

Open kgreiner-pcapps-com opened 7 years ago

kgreiner-pcapps-com commented 7 years ago

I work on a server over a VPN that has about 50 databases, some of which have many tables and stored procedures. I only want to work in 2-3 of these databases. Loading this server takes more than 10 minutes, completely freezing SSMS. Sometime SHD glitches and doesn't load correctly.

As a proof-of-concept, I'm envisioning a list where each member contains a server name regex and a database name regex. If the server regex matches, load only those databases matched by the database regex. If this is useful, it could be extended to include another regex for schema name.

csbubbles commented 7 years ago

Is it possible it happens because the add-in is trying to load all the metadata from that server right away? If that's the case, probably it's not the right behavior. I guess, the add-in should just load the list of databases first, and then either a) load the metadata for each of them only when you connect to some specific one, or b) proceed with getting all the information about tables / stored procedures for each database in background not blocking anything. Do you think this kind of behavior would fix the issue in your particular scenario?

FastNinja commented 7 years ago

It is already loading metadata only from the selected database...

On 10 Jun 2017, at 5:25 am, Maxim Novikov notifications@github.com wrote:

Assigned #5 to @FastNinja.

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

csbubbles commented 7 years ago

@FastNinja

I might be missing something, but here goes the code...

  1. When a new server gets connected (or refreshed), it triggers OnServersAdded event: https://github.com/bugzinga/sql-hunting-dog/blob/master/HuntingDog/DogEngine/Impl/ServerWatcher.cs#L160
  2. Then for each server added the following event listener triggers DatabaseLoader initialization for each server: https://github.com/bugzinga/sql-hunting-dog/blob/4200cad0044644ea835db33c621fa4e4cd7a29a7/HuntingDog/DogEngine/Impl/StudioController.cs#L71
  3. Then DatabaseLoader triggers initialization for the bunch of meta fields (databases, tables, views, stored procedures, etc.): https://github.com/bugzinga/sql-hunting-dog/blob/cc6dd6e83ed864628ce4235edbe65075219c8078/HuntingDog/DogEngine/Impl/DatabaseLoader.cs#L81

I guess it actually triggers all the metadata load while a server gets connected / reconnected. And, while it's written in the comments that it is supposed to improve the performance, it might work the opposite way in the case Kevin has.

Please correct me if I am mistaken here...

kgreiner-pcapps-com commented 7 years ago

My experience with slow loading leads me to think that all the metadata is loaded for the entire server. Yes, loading only from the selected database would be a great start. One problem is that rarely do I want the first database in the list.