smo表脚本生成性能问题 [英] smo table script generation performance problem

问题描述

你好，
目前，我正在开发一个用于数据库比较的程序.在此程序中，我需要为数据库对象创建脚本.我发现脚本生成太慢，但是使用SSMS生成脚本非常快.在一个线程中，我发现SSMS使用SMO来生成脚本，但是找不到提高性能的建议.

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();
  .
  .
  .
}

有趣的是db.PrefetchObjects(typeof(Table)，so)对表的脚本生成几乎没有影响，但是迅速增加了存储过程的脚本生成.
有人可以向我解释一下SSMS用于生成脚本的机制是什么，还是最高机密的信息? :P


问候
Robert

解决方案

确定.我找到了解决方法:

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

我发现很多解决方法:在为每个SMO一张一张地记录所有脚本之后，我发现某些脚本花费不到1秒的时间，其他大约需要50秒！

因此，第一个解决方法是使用多线程，这样做使我花了2个小时才能完成的脚本编写仅花了20分钟600％的速度.我尝试使用基本的ThreadPool，而没有在测试多少线程允许最佳性能的情况下对其进行优化.我待会再说. (我将使用不同数量的线程进行测试并记录下来，以查看计算机中可以提高最佳性能的线程数)

另一个解决方法是在Codeplex中使用DBDiff，该源可让您在没有SMO的情况下更快地编写脚本，但对于那些想要较少代码(手动编写脚本；-)的人来说却无用.您必须使用类来创建sql脚本.但是该工具非常适合同步数据库；-)

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

解决方案

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.

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

这篇关于smo表脚本生成性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！