How to fix slow SMO performance
By default, when SMO queries the database for the properties of an object, it only loads the basic properties. This reduces the number of tables it needs to join in the query, so dramatically improves performance, as long as you don't need the other properties.
As an example, lets look at building a simple front end to display the tables in a database and the columns of the selected table.
Using SMO to request the TableCollection, the query requests just the schema name and table name from sys.tables. This is perfect for our requirements: we can populate our list of tables using the names and we don't care about the other details. We've saved time because we didn't join to the other system tables we would have needed if we were interested in properties such as indexing, keys, partitioning etc.
Now we come to populate the column details. If we use SMO in its default mode then populating the ColumnsCollection of our table will use a simple query getting the column's ID and name, joining sys.tables with sys.all_columns:
If we were only displaying the column name that that would be fine. However, our listview is going to show more than just the name, and we create our listviewitem objects using some other interesting properties of the column:
Running SQL Server Profiler you'll notice that SMO has lost its way a bit. We'd expect it to fire another query to get the extended properties, but it does the extended query once for each column! Given that the full query now joins 13 tables, it's no surprise that people complain about SMO being slower than DMO.
The solution to this problem is to tell SMO what you're going to need in advance. Using SetDefaultInitFields you can tell SMO to load all extra properties or you can even specify a list of exactly which properties you're going to need.
SetDefaultInitFields is set at the server level. The simplest way to set it is just to tell it that all column properties should be loaded at once.
Checking the SQL Profiler again reveals that the initial query is now the 13-table version, so is a little bit slower, but there are no further queries. If we were to specify the exact list of properties we were interested in then we could cut down the number of tables joined and make it even faster.
Bonus improvements: AddRange and BeginUpdate
If you do write an application which sticks the column details into a listview, there are two other ways to improve performance: the AddRange method of the listview adds a collection or array of listviewitems to the listview in one operation, so avoids the nasty flickering as the control is repainted between add operations. If you prefer to use add, then the other way to stop the flickering is to use BeginUpdate and EndUpdate.