Thursday, November 29, 2007

Why is SQL Server 2005 SMO So Slow at Scripting Tables?

We currently use the SQL Server 2005 SMO objects for scripting up our database objects.  As part of our build process we have an executable that loops through the database objects and generates scripts for them.  An example of what we do is below:

Server server = new Server("(local)");
 
Database db = server.Databases["AdventureWorks"];
 
Console.WriteLine("Beginning Scripting Process.");
Console.WriteLine();
 
ScriptingOptions options = new ScriptingOptions();
 
options.AllowSystemObjects = false;
options.AppendToFile = false;
options.ContinueScriptingOnError = false;
options.ConvertUserDefinedDataTypesToBaseType = false;
options.IncludeHeaders = false;
options.IncludeIfNotExists = false;
options.NoCollation = false;
options.Default = true;
options.ExtendedProperties = true;
options.TargetServerVersion = SqlServerVersion.Version90;
options.LoginSid = false;
options.Permissions = false;
options.Statistics = false;
options.Indexes = true;
options.Triggers = true;
options.ClusteredIndexes = true;
options.NonClusteredIndexes = true;
options.XmlIndexes = true;
options.Bindings = true;
options.WithDependencies = false;
options.DriAllConstraints = true;
options.DriAllKeys = true;
options.DriChecks = true;
options.DriClustered = true;
options.DriDefaults = true;
options.DriForeignKeys = true;
options.DriIncludeSystemNames = true;
options.DriIndexes = true;
options.DriNonClustered = true;
options.DriPrimaryKey = true;
options.DriUniqueKeys = true;
 
Console.WriteLine("Begin Scripting Tables");
foreach (Table table in db.Tables)
{
    if (!table.IsSystemObject)
        table.Script(options);
}
Console.WriteLine("End Scripting Tables");

If you've ever tried using the SQL Server 2005 objects to script a large database, you know it's extremely slow.  Our database has about 700 tables, which is a pretty good size database, probably on the high end, but not outrageous.  When using SMO to script each of these entities, it takes about 45 minutes.  Using the old SQL 2000 DMO objects, this took about 5 minutes.  So why the 450% increase.  Well, I fired up SQL Profiler to take a look at the results, and here's what I found.  Each of these items happens at each iteration of the tables (all 21 of these steps happen per table).

  1. Get schemas, rules and defaults for the database
  2. Get schema for table to be scripted
  3. Get columns with column definitions
  4. Get column names
  5. Get replication info
  6. Get column names
  7. Get foreign keys
  8. Get trigger information
  9. Get constraints
  10. Get indexes
  11. Get full-text change tracking
  12. Get language for table
  13. Get column compute information
  14. Get column names
  15. Get extended properties
  16. Get more extended property info
  17. Get even more extended property info
  18. Get some more extended property info
  19. Get more extended property info
  20. Get CLR integration information
  21. Get more constraint information

Keep in mind that each of these are a sql query, some with complex joins, and each of which are being run as a separate database call.  Each database call is preceded with a database context switch ("USE [DATABASENAME]").

This is sooooo verbose.  I really hope that they do some work with this piece so that they get this thing to perform.

Another thing I don't understand, is that if you script through the SQL Server Management Studio, it performs much better.  Why would the SSMS use anything other than the SMO objects?  Is it using them differently, in a more performant way?  I've done quite a bit of searching, and have yet to find anything that would suggest a more efficient way to put together this code.

I think the only thing I can do for now is to switch my build process to do an incremental script, and only script the tables that have changed since the last release.  That stinks though, cause it's more logic in my build scripts that I don't need.

2 comments:

Stian said...

Hi Jim.

Before creating the database object you need to specify that SMO should return the IsSystemObject property by default. Add this line of code before creating the database:

Server.SetDefaultInitFields(GetType(Smo.StoredProcedure),"IsSystemObject")

That should speed things up a bit.

Check out http://sqljunkies.com/WebLog/euang/archive/2004/04/01/1889.aspx post for an explanation.

Cheers

Stian

Jim Fiorato said...

Thanks Stian.

I've had a good look at posts about pre-fetching properties. However, for scripting objects, this optimization provides little help. I took my sample and timed it before and after adding the property pre-fetching.

These numbers are based on the code in the sample, and the AdventureWorks database sample you can find on codeplex.

Before: 7.707 seconds
After pre-loading IsSystemObject property: 7.403 seconds

Now, the AdventureWorks database contains only 71 tables.

On my database with 744 tables, here are the results:

Before: 17 minutes 50.656 Seconds
After pre-loading IsSystemObject property: 17 minutes 51.596 seconds

As you can see, the additional code didn't help all that much. SMO is still verbosely communicating with the server when scripting the objects.

Now, as you can see by the results there could be something going on with my database. Going from 70 tables to 700 tables increases the time by an order of magnitude.

Jim