smo table script generation performance problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » SMO/RMO/DMO » smo table script generation performance...

smo table script generation performance problem

Rate Topic

Display Mode

Topic Options

Author

Message

kanasz.robert

Posted Sunday, April 24, 2011 2:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 5:13 AM
Points: 1, Visits: 27

Hello,
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.
Collapse
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;
.
.
.
db.PrefetchObjects(typeof(Table),so);
.
.
.
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
Regards
Robert

Post #1097869

cherish.j.wang

Posted Sunday, May 22, 2011 8:18 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 12:50 AM
Points: 14, Visits: 93

Hi kanasz.robert,

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

keep watching.

tks

Post #1113096

patricklambin

Posted Monday, May 23, 2011 6:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 12:46 AM
Points: 91, Visits: 124

Hello,

According http://msdn.microsoft.com/en-us/library/ms210376(v=SQL.100).aspx , 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 »

Permissions