Hi,
I was trying to solve SQL Treeo’s performance issue on slow connection. This posts is also covers feature request of having separate folder structures for each developer (store folder structure in local file). SQL Treeo currently stores object’s folder name to its extended property named “VirtualFolder”. This helps to identify which object belongs to which folder.
Advantages of this apporach:
- folder integration is completely seamless as it’s part of database data (extended properties of e.g. stored procedure)
- if it’s shared database, other developers with SQL Treeo installed have access to same folder structure
Disadvantages:
- it’s slower on non-local connections. It’s because of fetching extended properties using fn_listextendedproperty one-by-one. For database with thousands of objects accessed via e.g. VPN, it’s slow. (fetch all extended properties in batch is possible but not usable within add-in).
- it’s limit for storing more attributes to folders (such as color, completely own structure etc.)
Based on that, I would suggest to completely get rid of storing folder name to extended properties and store them to local file instead.
Advantages:
- performance, no access to database is needed (probably only EXISTS for objects within folders)
- each user could have its own folder structure
- more flexibility for future enhancements (such as mixing more SQL object types in one folder.)
Disadvantages:
- local file will not be shared with other database developers by default. However this could be solved by storing this file to any source control or placing it to network shared folder.
Please let me know if you agree with new “local file” approach.
Use this poll:
Loading …Thanks.
Tags: Add-In, custom folders, denali, download, Microsoft SQL Server, productivity, refactoring, SQL, SQL Server, SSMS, table, treeo, view
My first vote would be to try to speed up the extended properties by accessing the table directly instead of calling fn_listextendedproperty in a loop, but you say that’s not possible. With that in mind, I voted for local file – SQLTreeo is very useful, but I had to uninstall it because of performance, and I was on a normal network connection, not a slow VPN connection. I would definitely use it if performance was improved. (heck, I would probably pay for it).
It’s long story why speeding up extended properties solution is too complicated and possible buggy. Like I commented already here, using configuration file don’t have to necessarily bring any obstacles, this is just change of thiking about how SQL Treeo stores configuration data.
I like the second option but the local file I think that its very important to do it in xml format and it will be nice if we can store it in a custom path.
Yes, both is mandatory and it’s planned in same way you’ve mentioned.
How about adding an option to change it?
I would like to use solution with configuration file anyway. And yes, I was thinking about keeping ext. properties as well if there would be big push to keep them. On the other hand I would like to keep it as simple as possible and ideally have only “one store solution”. I did some prototyping and it seems that practice will not bring any obstacles while using file to store folders. It will be XML, very well mergable with source controls, no issues… If you would just keep that file with your scripts in e.g. SVN it would work seamlessly and logically. Also by changing configuration file path, you could use your own structure.
It would also suggest Local file / Shared (network folder) to store the information in, so that when you work in a team that the team don’t need to re-setup the default folder folders.
I don’t understand. You will have possiblity to change configuration file location. This means you can store it to network folder to share it with other team members. Setting file location is one-time action after installation. Is that what you’ve meant.
Still can’t understand, why “fetching extended properties using fn_listextendedproperty one-by-one”, but “SELECT * FROM fn_listextendedproperty(‘VirtualFolder’, ‘schema’, ‘dbo’, ‘table’, default, default, default);”.
Local file is more flexible, of course. Except of making it synked between developers (
There is no issue to pull all extended properties from either sys.extended_properties or fn_listextendedproperty. Issue is to use this approach in add-in with events it provides.
I think that if you put file on network share or to source contorl, it has very same sharing potential as ext. props.
Jakub will recall that we have few (2-3) developers but many (11) database instances; some common databases exist on all 11 of them.
We only have fast connections, BUT if the file can be easily COPIED to apply to multiple database instances, then there would be an additional benefit, much easier than copying hundreds of extended properties among database instances.
Yet another benefit would accrue if different developer’s copies of the “file” could easily be merged (say, with a source control tool), so that changes made by more than one person could accumulate in the “master” SqlTreeo file under source control.
Overall we have not used SqlTreeo because we use SSMS Solutions and Projects, and SqlTreeo doesn’t work in the solution explorer, but perhaps with this file-based approach we could share (and maybe merge) SqlTreeo structure and get some very good use out of it.
We do have about 200 stored procedure in one database, and it is painful to work with without any tool to help us.
Your situation also makes me thing that configuration is better approach than ext. props.
I’d think a config file would be the way to go. Like you said, added flexibility for more fields would prove valuable down the line for things like folder color.
Anything to make things faster is a huge plus for me though. The database I use primarily has over 10k tables and 15k stored procedures. I’m on a local connection, but things are still very slow.
Thanks for confirming my direction.
I voted for local file system, but how about adding a button to sync the local file system to/from a db table. That may be better than using svn because it would be all built into SSMS.
I am not sure about that. To transfer data in that table would be even more diffucult that transferring extended props. Ext. props are transferred automatically with object’s script. Data from that table wouldn’t.
I definitely support for local file.
A few of us in our organisation have started using and really like TreeO.
However we only wish to use it on certain server instances. We’ve noticed the performance issues when opening servers that contain many databases / objects. With the local config file each user can chose which servers are included and what folders apply will be read directly from the local file. Another consideration, we have been a little reluctant to add extended properties to objects in servers in our organisation that we don’t really own. Another reason to go local.
This post bring back the hope of finally getting a usable SQLTreeo! Hoping and waiting for the local file implementation. Instead of coming from time to time to check the news about this extension, I subscribed to the RSS today. Pretty strange thing to do for an extension I do not use. This should explain how much the suggested change is important.
We do a lot of report writing on vendor DB’s that we don’t have the ability to write extended properties to. The added benefit is that each developer can organize their own views on the data. I’m fully in favor of the file based approach and once you have a version out, I’m sure I’ll muster up another 15-20 users for you.
Thanks for your work on this Jakub, it’s a great tool and by the sounds of it, you’re going to take a great step in making it better.
Cheers
My group recently started using this tool and we love it. We’re using Red-Gate SQL Server Source Control and with the folders stored in extended properties, it lets us propagate the folders between our test and production servers through source control. I can also easily add or change folders with SPs if needed.
In a setting where we have some non-tech users (on the test servers), decentralizing the folders and making it so they have to keep theirs current on their own would probably make it unusable for us.
[...] which will help us to give more attention to further development and support. We are now working on local storage support which was desperately demanded by almost everybody who used SQLTreeo in the past. However we will [...]
If you uninstall the product, does it remove the extended properties it has made? Thanks.
Hi,
no, it may be used by someone other. Purpose of extended properties is about sharing folder structure – that’s why they’re not removed while one use uninstall SQLTreeo. We address this issue in next release by implementing local storage option.
SQLTreeo