Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module