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 (50)

  1. I’m starting to post articles around SMO and other topics, like WMI, SQLCMD, and other areas. This index…

  2. evreno says:

    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;

    }

    }

  3. John Gunning says:

    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?

  4. tomer says:

    how can i know what are the SMO’s defaults for each object ?

  5. Donna says:

    Today was a complete loss. I feel like a fog. I’ve just been hanging out doing nothing, but eh…

  6. Linda says:

    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.

  7. Alise says:

    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…)))

  8. Mikle says:

    Good site! Well.. i like design!

  9. Mister says:

    I love peace! and..your site..)))

  10. Milas says:

    Well…good news, i like your site, Happy new year! )))

  11. Jonn says:

    Happy New Year! real good site!

  12. Jak says:

    real good site!

  13. Dingo says:

    real good news! good site, respect webmaster!

  14. Lohness says:

    …good day! Congratulations on a great web site….))

  15. ware,ware says:

    Good Site .Nice work.,Good Site .Nice work.

  16. besti,besti says:

    Good Site .Nice work.,Good Site .Nice work.

  17. sarde,sarde says:

    Excellent browsing have the to,Excellent browsing have the to

  18. assic,assic says:

    Excellent browsing have the to,Excellent browsing have the to

  19. While site keep Good work,While site keep Good work

  20. Very good website you have here.: Thanks!,Very good website you have here.: Thanks!

  21. Unison says:

    You have very nice site! well,,, happy new Year!!!

    My site: http://www.onlinewebservice6.de/gastbuch.php?id=126942

  22. Very good website you have here.: Thanks!,Very good website you have here.: Thanks!

  23. Bob says:

    You have a good site! Real good html-code

  24. Unison says:

    Best site! Great! wow wow wow!

  25. [5!],[5!] says:

    Nise site. Thanks you Thanks!,Nise site. Thanks you Thanks!

  26. Wien ist mir im moment zu distanziert… Dann will ich dir wenigstens auf diesem wege meine lieben grube zukommen lassen!!! 🙂 Thanks!,Wien ist mir im moment zu distanziert… Dann will ich dir wenigstens auf diesem wege meine lieben grube zukommen lassen!!! 🙂 Thanks!

  27. Will return in the near future. good job. excellent site. Thanks!,Will return in the near future. good job. excellent site. Thanks!

  28. Really amazing! interesting site. keep up the good work. Thanks!,Really amazing! interesting site. keep up the good work. Thanks!

  29. appa[3!] says:

    Excellent browsing have the to

  30. Bill says:

    More information, you work, stress, then you focus on a creative. Appraisal discussion, ask figure of his new overcome, or off to describe. Foundation for them out the basic level, this regularly undertake a [url=http://learnin.43i.net ]learning center.[/url].

  31. Jak says:

    <a href="http://cigarsworld.net/Quintero-cigars.php">Quintero cigars</a> are

    famous because of their specific aromas and strong flavors, which can be sweet

    or spicy depending on the cigar type.

  32. Guruchel says:

    best <a href="http://replica-watchesz.com">replica watch</a> retailer in the world

  33. Rick says:

    best <a href="http://replica-watchesz.com">replica watch</a> retailer in the world

  34. Britneyyeyea says:

    Very nice! I have some LJ with news, check this out:

    <a href= http://michelas.livejournal.com >My live journal</a>

    <a href= http://homerius.livejournal.com >Lastest news</a>

    <a href= http://johnyknoxw.livejournal.com >My live journal</a>

  35. Britneyclooe says:

    Very nice! I have some LJ with news, check this out:

    <a href= http://iwantubadlyz.livejournal.com >Newest news</a>

    <a href= http://annakubat.livejournal.com >Check this out</a>

    <a href= http://jackie_simpson.livejournal.com >livejournal</a>

  36. Victor says:

    All <a href="http://replica-watchesz.com/Fendi-Watches.htm">Fendi watches</a> contain ETA Swiss movements and are water resistant to 100 feet.

  37. Jak says:

    All <a href="http://replica-watchesz.com/Fendi-Watches.htm">Fendi watches</a> contain ETA Swiss movements and are water resistant to 100 feet.

  38. Rick says:

    <a href="http://muonline-info.blogspot.com">MU</a>-online info – gameplay, characters, maps..

  39. The Best Catalog.

    <a href=http://healthpiece.info/>Real”>http://healthpiece.info/>Real Catalog</a>[url=http://healthpiece.info/]The Real Catalog[/url]

  40. Hi Sam! Photos i send on e-mail.

    Green,Hi Sam! Photos i send on e-mail.

    Green