Click here to Skip to main content
Click here to Skip to main content

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

, 7 Oct 2012
Rate this:
Please Sign up or sign in to vote.
Workaround for SMO latency when scripting.

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 Wink).

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Hassan Boutougha
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionWhen Scriptingthousand tables, this method does not seem excellent. PinmemberHua Yujun3-Apr-13 0:28 
GeneralMy vote of 1 Pinmembercjb1107-Oct-12 21:31 
If this is just about the prefetch method, it should be a tip, not an article?
 
But an article covering using SMO to do the scripting would be useful.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140718.1 | Last Updated 7 Oct 2012
Article Copyright 2012 by Hassan Boutougha
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid