Hi,
I have an application that prints the Table details using SMO. I specify all the columns that I need to print in a call to SetDefaultInitFields as shown below:
oSQLServer.SetDefaultInitFields(GetType(Column), New String() { _
"Computed", _
"DataType", _
"Default", _
"ID", _
"Identity", _
"InPrimaryKey", _
"IsForeignKey", _
"IsFullTextIndexed", _
"IsPersisted", _
"Name", _
"NotForReplication", _
"Nullable", _
"NumericPrecision", _
"NumericScale", _
"SystemType", _
"DataTypeSchema", _
"XmlSchemaNamespaceSchema" _
})
Adding DefaultConstraint property to the above list causes an exception.
Retrieving DefaultConstraint property from the Column object appears to be very slow.
Time take to retrieve the above columns of all the non-system tables in AdventureWorks is doubled if DefaultConstraint property is retrieved.
How can I improve the performance
I have a sample application that you can download that demonstrates this.
http://starprint2000.com/downloads/OutputTables.zip
This is how you run it:
To Output Table columns without DefaultConstraint
1. Open Solution OutputTables
2. Run it
3. Wait for the connect form to emumerate a list of SQL servers
4. Connect to a Server (AdventureWorks if possible)
5. Click on DoIt button
6. The RichTextBox at the bottom will display the table columns
7. The Status bar will show the elapsed time
8. Stop the application
To Output Table columns with DefaultConstraint
1. Open Solution OutputTables
2. Run it.
3. Wait for the connect form to emumerate a list of SQL servers
4. Connect to a Server (AdventureWorks if possible)
5. Tick the checkbox Output Column Defaults
6. Click on DoIt button
7. The RichTextBox at the bootom will display the table columns
8. The Status bar will show the elapsed time
9. Stop the application
Please remember that if you run the application twice without restarting it, the 2nd and subsequnt runs are much faster.
Retrieving DefaultConstraint property of a Table column is too slow
Juan Carlos Trimino
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=800595&SiteID=1
skinny
Please post the bug on on http://connect.microsoft.com/SQLServer/Feedback. We would like to fix them in the next release.
Thanks,
Alok
Will Strootman
My original application was developed using SQL-DMO and it was FAST in retreiving information. So I decided to rewrite the above code using SQL-DMO & VB .NET. I found that SQL-DMO is much much faster or I am doing something wrong. I am happy receieve any performance improvement tips.
Regards,
Dim oSQLServer As New SQLDMO.SQLServerJoginder Nahil
www.starprint2000.com
SQL-DMO code which takes 6.27 seconds:
Dim db As SQLDMO.Database2
Dim t As SQLDMO.Table2
Dim c As SQLDMO.Column2
Dim ts As TimeSpan
Dim stopwatch As Stopwatch = New Stopwatch() oSQLServer.LoginSecure = True
oSQLServer.Connect("Development")
db = oSQLServer.Databases.Item("AdventureWorks") stopwatch.Start() For Each t In db.Tables
For Each c In t.Columns
Console.WriteLine(String.Format("{0} - {1} [{2} {3} {4}]", t.Name, c.Name, c.Default, c.InPrimaryKey, c.IsComputed))
Next
Next ts = stopwatch.Elapsed
Console.WriteLine(String.Format("Main(): {0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10))
SMO code which takes 2 Minutes and 2.17 seconds
db = (Database)DatabasesComboBox.SelectedItem;
TimeSpan ts;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
db.Parent.SetDefaultInitFields(typeof(Table), new String[] { "Name" });
db.Parent.SetDefaultInitFields(typeof(Column), new String[] { "DefaultConstraint", "Computed", "InPrimaryKey", "IsFulltextIndexed", "IsForeignKey", "IdentityIncrement", "IdentitySeed", "NumericPrecision", "XmlDocumentConstraint", "XmlSchemaNamespace", "NumericScale" });
db.PrefetchObjects(typeof(Table));
foreach (Table t in db.Tables)
{
foreach (Column c in t.Columns)
{
Console.WriteLine(String.Format("{0} - {1} [{2} {3} {4}]", t.Name, c.Name, c.Default, c.InPrimaryKey, c.Computed));
}
}
ts = stopWatch.Elapsed;
Console.WriteLine(String.Format("Main(): {0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10));
vesuvius
Hi James,
A call to PrefetchObjects makes all the difference for the DefaultConstraint property. It is much much faster now, however my application needs to print the following set of properties:
Computed
DataType
Default
ID
Identity
InPrimaryKey
IsForeignKey
IsFullTextIndexed
IsPersisted
Name
NotForReplication
Nullable
NumericPrecision
NumericScale
SystemType
DataTypeSchema
When I modified your example to include just InPrimaryKey property the performance degraded many times over.
Please try the following code:
Regards,
Joginder Nahil
www.starprint2000.com
----------------------------------------------------------------------------------------------------------
TimeSpan ts;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
Server srv = new Server("you_server_goes_here");
Database db = srv.Databases["AdventureWorks"];
db.PrefetchObjects(typeof(Table), new ScriptingOptions());
foreach( Table t in db.Tables )
{
foreach( Column c in t.Columns )
{
Console.WriteLine(c.DefaultConstraint);
Console.WriteLine(c.InPrimaryKey );
}
}
ts = stopWatch.Elapsed;
Console.WriteLine(String.Format("Main(): {0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds/10));
Emac80
If you have not done so, issue a call to PrefetchObjects before enumerating the tables. In the following sample, server trips go from 1662 to 56 with addition of the boldfaced method call.
TimeSpan ts;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
Server srv = new Server("you_server_goes_here");
Database db = srv.Databases["AdventureWorks"];
db.PrefetchObjects(typeof(Table), new ScriptingOptions());
foreach( Table t in db.Tables )
{
foreach( Column c in t.Columns )
{
Console.WriteLine(c.DefaultConstraint);
}
}
ts = stopWatch.Elapsed;
Console.WriteLine(String.Format("Main(): {0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds/10));