SQL SMO 执行批处理 TSQL 脚本
我正在使用 SMO 执行批处理 SQL 脚本。在 Management Studio 中，该脚本的执行时间约为 2 秒。使用以下代码，大约需要 15 秒。
var connectionString = GetConnectionString();
// need to use master because the DB in the connection string no longer exists
// because we dropped it already
var builder = new SqlConnectionStringBuilder(connectionString)
{
InitialCatalog = "master"
};
using (var sqlConnection = new SqlConnection(builder.ToString()))
{
var serverConnection = new ServerConnection(sqlConnection);
var server = new Server(serverConnection);
// hangs here for about 12 -15 seconds
server.ConnectionContext.ExecuteNonQuery(sql);
}
该脚本创建一个新数据库并在几个表中插入几千行。生成的数据库大小约为 5MB。
任何人有这方面的经验或有关于为什么 SMO 运行如此缓慢的建议吗？
I'm using SMO to execute a batch SQL script. In Management Studio, the script executes in about 2 seconds. With the following code, it takes about 15 seconds.
var connectionString = GetConnectionString();
// need to use master because the DB in the connection string no longer exists
// because we dropped it already
var builder = new SqlConnectionStringBuilder(connectionString)
{
InitialCatalog = "master"
};
using (var sqlConnection = new SqlConnection(builder.ToString()))
{
var serverConnection = new ServerConnection(sqlConnection);
var server = new Server(serverConnection);
// hangs here for about 12 -15 seconds
server.ConnectionContext.ExecuteNonQuery(sql);
}
The script creates a new database and inserts a few thousand rows across a few tables. The resulting DB size is about 5MB.
Anyone have any experience with this or have a suggestion on why this might be running so slowly with SMO?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SMO 在后台做了很多奇怪的事情，这是您为以面向对象的方式处理服务器/数据库对象的能力付出的代价。
既然您没有使用 SMO 的 OO 功能，为什么不完全忽略 SMO 并简单地通过普通 ADO 运行脚本呢？
SMO does lots of weird .. stuff in the background, which is a price you pay for ability to treat server/database objects in an object-oriented way.
Since you're not using the OO capabilites of SMO, why don't you just ignore SMO completely and simply run the script through normal ADO?
将记录上传到数据库的最佳且最快的方法是通过 SqlBulkCopy。
特别是当您的脚本超过 1000 条记录时 - 这将显着提高速度。
您需要做一些工作才能将数据放入 DataSet，但这可以使用 DataSet xml 函数轻松完成。
The best and fastest way to upload records into a database is through SqlBulkCopy.
Particularly when your scripts are ~1000 records plus - this will make a significant speed improvement.
You will need to do a little work to get your data into a DataSet, but this can easily be done using the DataSet xml functions.