Mark Gilbert's Blog

Science and technology, served light and fluffy.

SSMS Scripter Updated – Major Performance Improvement

I just released a new version of the SSMS Scripter – version 2.6.0 is now available at http://CodePlex.com/SSMSScripter.

The major update for this version was a huge (two-orders-of-magnitude-huge) improvement in the time needed for the Scripter to load up once invoked from SQL Server Management Studio.  With version 2.5.3, even databases with a handful of stored procedures, views, or user-defined functions would take 100-200 seconds for the Scripter to spin up.  It ended up being three lines of code to drop that load time to two seconds or less, even for large databases.

After doing some digging, I found this article by Bill Graziano dated November 29, 2005 (just about a year before I wrote the very first version of the Scripter): http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated.  This article was doing the same basic thing that I was – enumerating the non-system objects in a database via SMO.  Bill’s sample code was referencing the same property I am – IsSystemObject – and found it to be very slow.  Boy, this sounds familiar.

The reason for the lousy performance was explained by a member of the SQL Server Product Team, Euan Garden, who was responding to Bill’s post (a link to Euan’s comments is included in Bill’s article).  By default, the IsSystemObject property is not returned when you get a collection of objects back (like stored procedures).  As Euan explains, this was done to optimize how much SMO needs to retrieve with each request.  As it turns out, you can force specific properties to be returned, though:

Me._CurrentServer.SetDefaultInitFields(GetType(StoredProcedure), “IsSystemObject”)
Me._CurrentServer.SetDefaultInitFields(GetType(View), “IsSystemObject”)
Me._CurrentServer.SetDefaultInitFields(GetType(UserDefinedFunction), “IsSystemObject”)

I added these lines to the newly created (as the result of a refactoring) “CurrentServer” property in SSMSScriptor.vb.  This configures the SMO.Database object to bring IsSystemObject back as one of the properties of the StoredProcedure, View, and UserDefinedFunction objects.  By not doing this, the code was having to make a round trip to the database for EVERY object in the collection before it could say whether or not it was a system object.  This meant every stored procedure in the database, plus all of the ones in Master – thousands in all.  And it ONLY took 200 seconds.  Ahem.

In addition to the performance boost, I also updated the class headers throughout to reflect the license that the Scripter is being released under now, refactored the main interface class a bit, modified the interface so that it no longer closes once you finish scripting to a file or to the clipboard, and adjusted how the progress bar/status messages are displayed.

Enjoy!

Advertisements

October 7, 2008 - Posted by | SQL Server, Tools and Toys

Sorry, the comment form is closed at this time.

%d bloggers like this: