smo table script generation performance problem

Question

5.00/5 (1 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 23-Apr-11 20:51pm

Kanasz Robert

Updated 24-Apr-11 0:04am

Ed Nutting

v2

Add a Solution

Comments

Ed Nutting 24-Apr-11 6:04am

Improved for spelling/grammer a.k.a. readability.

2 solutions

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;-)

Posted 28-Aug-12 21:41pm

Hassan Boutougha

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kanasz Robert · Accepted Answer · 2011-04-27T00:02:00

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.