Performance Issue in Schema Extract
I wrote some recursive piece of code to populate a windows forms treeview control with SQL SMO objects. The target is to have something like Sql Management Studio Server/Database Explorer.
I'm trying to build the following hierarchy
Server
|_ Database
|_ Table
|_ FieldI've done this and it works fine, but It's too slow. Is there any issues about that which I shoud care about?
*Note: I've wrote the code both in Sync ad Async pattern using delegates and BackgroundWorker, all the same response time.
Answers
As much as I love SMO, I think it is overkill for just populating a treeview with schema and metadata from a database.
I built a database explorer and code generator ( Database Explorer and Code Generator - Stored Procedures and SqlConnectionStringBuilder ) that has a treeview similar to what you are talking about, and you can get all of that information from GetSchema and INFORMATION_SCHEMA.
Check out my article here on GetSchema, which is new in ADO.NET 2.0 and will give you enough to create a treeview:
Here is an example on INFORMATION_SCHEMA to query tables, but you can use it for much, much more:
Get List of Tables in a Database - Query INFORMATION_SCHEMA.Tables - ADO.NET
However, if you really want to do this in SMO, your performance issue probably has to do with the fact that SMO has this concept of delayed instantiation ( aka lazy loading ) of object properties. This means that when you ask SMO to get you a table, for example, it does not bring over all the properties of the table from the database.
You can see the problem here. If you are looping through 50 tables and asking for a table property that SMO didn't originally bring over, SMO will make 50 additional calls over the network to SQL Server, one time for each table. This causes huge performance issues.
The good news is that there is a SetDefaultInitFields command that you can call to make sure SMO includes a lazy-loaded field by default when bringing over a database object. I wrote an article on that too :)
Best of luck,
Dave
All Replies
As much as I love SMO, I think it is overkill for just populating a treeview with schema and metadata from a database.
I built a database explorer and code generator ( Database Explorer and Code Generator - Stored Procedures and SqlConnectionStringBuilder ) that has a treeview similar to what you are talking about, and you can get all of that information from GetSchema and INFORMATION_SCHEMA.
Check out my article here on GetSchema, which is new in ADO.NET 2.0 and will give you enough to create a treeview:
Here is an example on INFORMATION_SCHEMA to query tables, but you can use it for much, much more:
Get List of Tables in a Database - Query INFORMATION_SCHEMA.Tables - ADO.NET
However, if you really want to do this in SMO, your performance issue probably has to do with the fact that SMO has this concept of delayed instantiation ( aka lazy loading ) of object properties. This means that when you ask SMO to get you a table, for example, it does not bring over all the properties of the table from the database.
You can see the problem here. If you are looping through 50 tables and asking for a table property that SMO didn't originally bring over, SMO will make 50 additional calls over the network to SQL Server, one time for each table. This causes huge performance issues.
The good news is that there is a SetDefaultInitFields command that you can call to make sure SMO includes a lazy-loaded field by default when bringing over a database object. I wrote an article on that too :)
Best of luck,
Dave
Thank you Dave, it worked, and I'm sorry if I was offensive to your love :P
I extracted the schema your way and SMO is doing Management Job.
