Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

smo table script generation performance problem Expand / Collapse
Posted Sunday, April 24, 2011 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 5:13 AM
Points: 1, Visits: 27
I'm currently working on program for database compare. In this program I need to create scripts for databse objects. i found out that script generation is too slow but generating scripts using SSMS it's pretty fast. In one thread I have found, that SSMS uses for script generation SMO, but I couldn't find any suggestions for performance improvement.
Server srv = new Server(new ServerConnection("SERVER", "USERID", "PASSWORD"));
srv.SetDefaultInitFields(typeof(Table), true);
Database db = srv.Databases[database];
ScriptingOptions so = new ScriptingOptions();
so.AllowSystemObjects = false;
foreach(Table tb in db.Tables)
StringCollection sc = tb.Script();

Interesting is that db.PrefetchObjects(typeof(Table),so) has almost no effect for script generatino of tables, but is rapidly
increases script generation of stored procedures.
Can someone explain me, what kind of mechnizm SSMS uses for script generation, or is top secret informacion? :P
Post #1097869
Posted Sunday, May 22, 2011 8:18 PM


Group: General Forum Members
Last Login: Thursday, December 1, 2016 2:19 AM
Points: 21, Visits: 206
Hi kanasz.robert,

What you are tolking about puzzles me for a long time.

keep watching.

Post #1113096
Posted Monday, May 23, 2011 6:25 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 13, 2016 2:54 PM
Points: 3,498, Visits: 1,239

According , i have seen
"You can use this method to adjust which properties are initialized for an object when it is first created to further optimize performance". I think that SetDefaultInitFields is useful only when we create a new object like Table, StoredProcedure but it is useless to access an existing object like in your case ( you cannot generate a generation script versus an not existing object ).
I have to do some tests as my last ones were versus SQL Server 2005 .

Have a nice day
Post #1113280
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse