다음을 통해 공유

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.

Comments

  • Anonymous
    May 02, 2005
    I'm starting to post articles around SMO and other topics, like WMI, SQLCMD, and other areas. This index...

  • Anonymous
    September 18, 2005
    Hi,
    Thanks for your example.. I have little question about 'SetDefaultInitFields' method.

    To display tables and their columns' name for a database I've written a piece of code. But it raises an error 'A column named 'ID' already belongs to this DataTable.'

    Thanks..

    The code is similar to following:

    Server server = new Server(".");
    server.SetDefaultInitFields(true);
    Database db = server.Databases["AdventureWorks"];
    foreach (Table t in db.Tables)
    {
    foreach (Column column in t.Columns)
    {
    string s = column.Name;
    }
    }

  • Anonymous
    April 10, 2006
    What about collection caching Michael ? How does one prevent SMO round-tripping to the database for each object in an SMO collection?

    An example of this would be the Parameters collection of a StoredProcedure - how can we get all the parameters in one go rather than roundtripping to the database for each parameter?

  • Anonymous
    August 15, 2006
    how can i know what are the SMO's defaults for each object ?

  • Anonymous
    November 29, 2006
    Today was a complete loss. I feel like a fog. I've just been hanging out doing nothing, but eh...

  • Anonymous
    November 30, 2006
    I've just been letting everything pass me by lately. I've more or less been doing nothing. Not much going on lately. I can't be bothered with anything recently.

  • Anonymous
    November 30, 2006
    Not much on my mind recently. I haven't gotten much done. What can I say? I haven't been up to much these days, but such is life. I feel like a complete blank, but so it goes...)))

  • Anonymous
    December 04, 2006
    Good site! Well.. i like design!

  • Anonymous
    December 09, 2006
    I love peace! and..your site..)))

  • Anonymous
    December 10, 2006
    Well...good news, i like your site, Happy new year! )))

  • Anonymous
    December 17, 2006
    Happy New Year! real good site!

  • Anonymous
    December 17, 2006
    real good site!