Performance impact: Speeding up SMO script generation – the test code

Linchi Shea — Tue, 15 Dec 2009 19:10:00 GMT

The attached is the C# code that I used for generating the test results charted in my previous post.

Note that for testing no generated script is actually persisted into a file by this program. The Script() method is applied, but the resulting script strings are thrown away. For example, here is how a stored procedure is scripted:

StringCollection sc = db.StoredProcedures.ItemById(objectId).Script();

foreach (string s in sc)

{ };

You can run the compiled program as follows:

cmd>smpScript.exe <server name> <database name> <number of threads>

Performance impact: Speeding up SMO script generation

Linchi Shea — Sun, 13 Dec 2009 22:18:00 GMT

Generating scripts through SMO can be as simple as walking down the database object tree and applying the Script() method to each scriptable object. Well, that is until you start to try it on a database that has a large number of objects (say a few thousands), and the long wait the becomes rather frustrating. I’m not talking about such silly performance problems as the horrific roundtrips caused by the IsSystemObject check (which has a simple workaround). Rather, it’s just sloooow to script out thousands (or even a few hundreds) of database objects.

But there is hope, and one could even argue that it’s a matter of how you use SMO.

One way to significantly speed up scripting the objects in a database is to do away with single-threaded programming and resort to multiple threads.

I have run some simple tests with a various number of threads in a C# program against a database that has 632 top-level objects, including tables, stored procedures, user-defined functions, and views, but not counting triggers, indexes, and constraints that hang off each table.

The chart below shows that on my test workstation, using 6~8 threads in the C# program produced the optimal performance in terms of the scripting elapsed time. When done in a single thread, the performance was the worst. With 6~8 threads, the elapsed time was almost four times shorter than with a single thread, a ~400% improvement. Not bad with a simple change!

Unfortunately, in 99% of cases I see people scripting the objects in a database via SMO in a single thread. If you drive it with Powershell, I'm not sure if there is a simple way to do multithreading. If you know how to do multithreading in Powershell, please share.

The reason that I could dramatically speed up the script generation via SMO using multiple threads is that the resources on the workstation and on the server were greatly underutilized with a single thread. Note that when I pushed it hard with 50 threads, overhead on the workstation became a significant drag. But even in that case, it performed much better than a single-thread program.

It is worth reiterating that the specific numbers charted above were obtained on my workstation. In a different setting, the number of threads for the most optimal performance may vary. But I do not expect the overall message of multiple threads leading to shorter script generation time with SMO to change.

Search results matching tags 'SMO' and 'Performance'

Performance impact: Speeding up SMO script generation – the test code

Performance impact: Speeding up SMO script generation