Click here to Skip to main content
14,176,540 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hello,
Currently I'm working on a program for database comparison. 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 is pretty fast. In one thread I have found that SSMS uses SMO for script generation, 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;
.
.
.
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 rapidly increases script generation of stored procedures.
Can someone explain to me, what kind of mechanism SSMS uses for script generation, or is it top secret information? :P


Regards
Robert
Posted
Updated 24-Apr-11 0:04am
v2
Comments
Ed Nutting 24-Apr-11 6:04am
   
Improved for spelling/grammer a.k.a. readability.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

OK. I found a solution:
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;
var dt = db.EnumObjects(DatabaseObjectTypes.Table);
var urns = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[dt.Rows.Count];
//get urns of tables to script
for (int rowIndex = 0; rowIndex < dt.Rows.Count; ++rowIndex)
{
 urns[rowIndex] = dt.Rows[rowIndex]["urn"].ToString();
}
//script tables
var scripter = new Scripter(srv);
var scripts = scripter.Script(urns);
foreach (var script in scripts)
{
 System.Diagnostics.Trace.WriteLine(script);
}

it is necessary to script all objects at once.
   
Comments
lemur 5-Aug-15 9:07am
   
Its worth noting that you can if you don't have the Management SDK you can use an array of Microsoft.SqlServer.Management.Smo.Urn objects as well.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

I find many workaround: After logging all script one by one for each SMO.Table, I discover that some script take less than 1 second, other take about 50 second!

So a first workaround is to work with multithreading.Doing so, make my scripting that took 2 hours to take know only 20 minutes 600% faster. I try with a basic ThreadPool, without optimize it in testing how many thread allow the best performance. I will say you it after. (I will test using different number of thread and logged it in order to see the number of thread in my computer that improve best performance)

Another workaround, is to use DBDiff in codeplex which source allow you to script faster without SMO but not usefull for those who want less code (script manually ;-). You have to use class to create sql script.But the tool is very good to synchronise database;-)
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190525.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100