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.

regards,
choongseng