SMO Tips & Tricks: Improving SMO Performance.
One of the few limiting factors with SMO is that if you do not know what you are doing and iterating through large numbers of databases it will seem extremely slow…..If you would look through profiler you would see why. Everytime you call on an object property it makes a new call to the database. That can make for a pretty damn chatty application. However, the server object provides a way in which you can speed this up …the SetDefaultInitFields() method. This allows you to tell the server object that you are wanting to ‘preload’ certain traits of objects when they are initially populated within the object. This cuts down on the calls back to the database and dramatically speeds up performance. For example……
Dim oServer as Server = New Server("AdventureWorks")
oServer.SetDefaultInitFields(GetType(Database), "CompatibilityLevel")
oServer.SetDefaultInitFields(GetType(Database), "Name")
oServer.SetDefaultInitFields(GetType(Database), "IsSystemObject")
oServer.SetDefaultInitFields(GetType(StoredProcedure), "Name")
oServer.SetDefaultInitFields(GetType(View), "Name")
oServer.SetDefaultInitFields(GetType(StoredProcedure), "TextBody")
Hope this helps!
Cheers,
AJ