Wednesday, September 14, 2005

SQL Server 2005 SMO Optimization

While digging the Books Online trying to see what is going on as my SMO program is super slow, Dr.Google lead me to this site.

There's some optimization that you can do to tell the SMO class to retrieve some initial properties of a instance of a SMO class. For convinient, this is a cut-n-paste from the site.

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];
svr.SetDefaultInitFields(typeof(Table), "CreateDate");
foreach (Table t in db.Tables)
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);

By adding this line:
svr.SetDefaultInitFields(typeof(Table), "CreateDate");

It tells the SQL SMO class to retrieve only the specified property (or collection of properties), so that you won't be bloated with lots of SQL statement firing which really slow down the SMO apps.

Well, after all, as the application that I'm doing had to read each table and its columns for its datatypes, thru the Profiler, I can actually see that there's 1 SQL statement being fired for each field in the database, so to say, if I've 120 tables which each tables have more than 8 fields in average, I've to wait for 120*80=960 SQL statements to be fired to get the schema of my database using SMO, that I tell you that took me more than 30 minutes!!

So how? I gave up SMO for this particular app.

What I did finally is to create a Virtual SMO Objects, that means, I create those object but didn't commit it to the database (without firing the .Create()). And using the TSQL querying the system tables, I retrieve all my schema at one go in 1 SQL for each table and fill up the SMO objects. And subsequently, send the SMO objects to my SMO apps. Cool! it works really fast by then.



Babu M said...

In your post you mentioned that "I retrieve all my schema at one go in 1 SQL for each table and fill up the SMO objects". I too am in a similar situation where SMO takes painfully long to complete the iteration on one database. So, I am going your way to get the Schema through sys tables or INFORMATION_SCHEMA. But I still need to create smo objects out of it for business reasons. So, how do I create SMO objects out of the results of sys table queries. Any help in the form of sample code or a pointer in the right direction is highly appreciated. Thank you.

choongseng said...

You need to execute your SQL queries against the system table and then fill up your SMO object.

Below is some codes excerpt from my project, hope it helps. I think it is pretty straight forward.

Dim iTable As New Smo.Table(_Database, strTableName)
.. follow up filling up ...
Dim iColumn As New Smo.Column(iTable, iColumnName)
With iColumn
.DataType = New Smo.DataType
.DataType.MaximumLength = iMaxLength
.DataType.SqlDataType = CType([Enum].Parse(GetType(Smo.SqlDataType), iDataType, True), Smo.SqlDataType)
.DataType.NumericPrecision = iPrecision
.DataType.NumericScale = iScale
.Nullable = iIsNullable
.Identity = iIsIdentity
.Default = iDefault
'.Properties.Item("InPrimaryKey").Value = True
End With