SetDefaultInitFields and SQL Server Management Objects ( SMO ) - Delayed Instantiation of Object Properties
by David Hayden ( .NET Developer )
SQL Server Management Objects ( SMO ) has this concept of Delayed Instantiation of Object Properties. The concept consists of not returning every property of a database object when the object is requested. Some properties, like the StoredProcedure's IsSystemObject property, are not returned by default when you request a Stored Procedure or a collection of Stored Procedures from the Database. It is only when you explicitly access the IsSystemObject property of the StoredProcedure that SMO goes back to the database to get the value of the property. The goal here is to save memory and network traffic. Why return a whole bunch of information when perhaps you only need a few properties? This concept is similar to the concept of O/R Mappers and Lazy-Loading.
You can see the problem here, however. What if you return a collection of Stored Procedures and a property you use is not one of the properties returned by default? Let's say we are iterating through a collection of Stored Procedures in a database and querying to see if it is a System Stored Procedure like below:
Server server = new Server();
StoredProcedureCollection storedProcedures =
server.Databases["AdventureWorks"].StoredProcedures;
foreach (StoredProcedure sp in storedProcedures)
{
// IsSystemObject not returned by default
if (!sp.IsSystemObject)
{
// We only want user
// stored procedures
}
}
The problem is performance. Since SMO has to fetch the IsSystemObject Property of each StoredProcedure within the foreach loop, you are incurring another roundtrip to the database for each Stored Procedure in the Database. I tried this using the AdventureWorks Database in SQL Server 2005 and it took anywhere from 1 - 2 minutes to complete the code. Check out SQL Profiler when you run the code to see all the roundtrips to the database.
SetDefaultInitFields
The SQL Server Management Object's Team created a way to force SMO to return a property not originally set as a default property using the Server.SetDefaultInitFields Method. In our case above with the StoredProcedure.IsSystemObject Property, we would call it as follows:
server.SetDefaultInitFields
(typeof(StoredProcedure), "IsSystemObject");
The above statement tells SMO to return the value of IsSystemObject when returning a Stored Procedure from the Database. Here is the complete code:
Server server = new Server();
// Force IsSystemObject to be returned
// by default.
server.SetDefaultInitFields
(typeof(StoredProcedure), "IsSystemObject");
StoredProcedureCollection storedProcedures =
server.Databases["AdventureWorks"].StoredProcedures;
foreach (StoredProcedure sp in storedProcedures)
{
if (!sp.IsSystemObject)
{
// We only want user
// stored procedures
}
}
The new version of the code completes in a matter of seconds and you will notice that the extra roundtrips to the database no longer exists.
Conclusion
If you find that your SQL Server Management Object's code is running really slow, check SQL Profiler to see if you are accessing properties that are not returned by default and creating extra roundtrips to the database. Force SMO to return those properties by default by using the Sever.SetDefaultInitFields method.
Source: David Hayden ( .NET Developer )
SMO Tutorials and Examples