有没有办法加快 SQL Server 管理对象对现有数据库的遍历速度?
我目前正在使用 SMO 和 C# 遍历数据库来创建代表两个数据库各个方面的设置树,然后比较这些树以了解它们在何处以及如何不同。
问题是,对于 2 个大小合理的数据库,在本地抓取它们并收集我想要比较的表/列/存储过程信息需要几乎 10 分钟。
有没有比 SMO 更好的接口来以这种方式访问数据库? 我不想包含任何额外的依赖项,但为了将速度提高 50%,我会承受这种痛苦。 下面是我如何枚举表和列的示例。
Microsoft.SqlServer.Management.Smo.Database db = db_in;
foreach (Table t in db.Tables)
{
if (t.IsSystemObject == false)
{
foreach (Column c in t.Columns)
{
}
}
}
I'm currently using SMO and C# to traverse databases to create a tree of settings representing various aspects of the two databases, then comparing these trees to see where and how they are different.
The problem is, for 2 reasonably sized database, it takes almost 10mins to crawl them locally and collect table/column/stored procedure information I wish to compare.
Is there a better interface then SMO to access databases in such a fashion? I would like to not include any additional dependencies, but I'll take that pain for a 50% speed improvement. Below is a sample of how I'm enumerating tables and columns.
Microsoft.SqlServer.Management.Smo.Database db = db_in;
foreach (Table t in db.Tables)
{
if (t.IsSystemObject == false)
{
foreach (Column c in t.Columns)
{
}
}
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
尝试强制 SMO 一次读取所有必填字段,而不是在访问时查询。
有关详细信息,请参阅此博客
编辑:链接已失效但我发现 archive.org 上的页面。 这是相关代码:
Try to force SMO to read all the required fields at once, instead of querying on access.
See this blog for more information
EDIT: Link is dead but I found the page on archive.org. Here's the relevant code:
使用各数据库中的系统视图并按常规查询。
http://www.microsoft.com/downloads/details.aspx microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en
Use the system views in each database and query conventionally.
http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en
几乎没有什么是您无法通过 TSQL 查询获得的。 以这种方式获取元数据通常非常快。
There is little that you can't get via TSQL queries. Getting metadata that way is usually very fast.