SQL Server: Tuning your SMO Application for great performance -- PART 2
In the previous post I provided you with a fundamental tool to minimize the amount of SQL statements emitted and therefore limit expensive network round-trips (and SQL statements) using the Server.SetDefaultInitFields() method. In this post I will provide you with some more detail about this method, alongside with a recommendation about its use.
Let's take a quick look at the optimized sample that I provided last article:
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);
}
This optimization is particular to this piece of code, but the setting is global to the Server instance so every time you will retrieve a Table object later in your code, it will load the same fields as just specified. It's recommended to save the state of the previous SetDefaultInitFields setting so you can set it to the same value after you have retrieved your objects. The following sample shows you have to save the state, and then set if back to that state:
Server svr = new Server();
Database db = svr.Databases["AdventureWorks"];
StringCollection sc = svr.GetDefaultInitFields(typeof(Table));
svr.SetDefaultInitFields(typeof(Table), "CreateDate");
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}
svr.SetDefaultInitFields(typeof(Table), sc);
The reason you want to be careful with this is that the SetDefaultInitFields setting is global to the Server object reference, and you are likely to hold on to the Server object instance reference (svr in the above example) to perform additional work with it. For example, if you were to call Refresh() on the Tables collection, any additional operation that would cause a table object to be instantiated in the collection will use the latest SetDefaultInitFields setting.
SetDefaultInitFields has a few more helpful overloads that I will describe here:
|
SetDefaultInitFields(System.Type typeObject, System.Boolean allFields) |
Specifies all properties are fetched when the specified object is instantiated. When set to false, the fields will be reset to default. |
|
SetDefaultInitFields(System.Boolean allFields) |
Specifies all properties are fetched when any object is instantiated. When set to false, the fields will be reset to default.. |
Recommendation: make sure your application is aware of this global Server state, and either specifically set the fields or reset to the previous value. If you do not take care about this, you may see sudden application degradation (or your customers may start to notice), and as it will be very hard to test all code paths you cannot know beforehand how your application is going to behave in the future if· this is not carefully managed. Also, if you pass the Server reference on to another library that component may change the field settings and cause 'mysterious' performance degradation that may be hard to debug.
