Optimization using SMO because of latency - SQL Server Management Object - To script table

Introduction

Optimization using SMO  - SQL Server Management Object - to script table.

Background

With SMO, it is easier to programmatically make same tasks than SQL Server 2008 Management Studio wizard which allows script all database objects (tables, stored procedure, index, data...):

The MSDN URL below will explain you how SMO works:

http://msdn.microsoft.com/en-us/library/ms162169(v=sql.100).aspx 

Using the Code

One trouble I have encountered in scripting with SMO tables (many hundred) was that scripting with SMO has taken 2 hours against only a few minutes with the SSMS wizard. One workaround was to firstly use PrefectObject and secondly make the  call script method with multithreading. It allows me to decrease time processing from 2 hours to 20 minutes.

Use PrefetchObject to make one call to load table collection:

Database db = ... // get your database root
ScriptingOptions so = new ScriptingOptions();
db.PrefetchObject(typeof(Table), so);

You have also to call your script method using multithreading (threadpool or task ).

Points of Interest

Without making a prefetchObject method call, your application will make a lot of round trip to SQL Server to make your script. The second point is that SMO is not written with multithreading so you have to do it on your own.