Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

smo table script generation performance problem Expand / Collapse
Author
Message
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
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
Posted Sunday, May 22, 2011 8:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:01 PM
Points: 19, Visits: 159
Hi kanasz.robert,

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

keep watching.

tks
Post #1113096
Posted Monday, May 23, 2011 6:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 4:58 AM
Points: 1,057, Visits: 314
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 »

Add to briefcase

Permissions Expand / Collapse