时间戳

首页 > 解决方案 > cmd.ExecuteNonQuery() 非常慢

c# - cmd.ExecuteNonQuery() 非常慢

问题描述

我想使用 C# 和存储过程在 SQL Server 中更新/插入我的表。但是我发现使用 将数据发送到数据库很慢cmd.ExecuteNonQuery()，那么如何提高数据传输速度呢？现在大约是 20,000 行/分钟，无法满足性能。

cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = @"insert_update_table";

SqlParameter p = cmd.Parameters.AddWithValue("@datatable", tempdatatable);

try
{
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    Console.WriteLine("{0}", ex);
}

alter proc insert_update_table
@datatable versiontable_4 readonly
as 
begin
declare @tmptime nvarchar(255)
    begin 
        merge into dbo.CurrentVersion_1 cvr
        using @datatable src on cvr.PackageName =src.PackageName and cvr.env_cloud_roleInstance = src.env_cloud_roleInstance 
        when matched and cvr.env_time<src.env_time then 
        update set cvr.env_time=src.env_time,
        cvr.PackageVersion=src.PackageVersion,
        cvr.PackageAge=src.PackageAge,
        cvr.DownloadMechanism=src.DownloadMechanism,
        cvr.env_cloud_deploymentUnit=src.env_cloud_deploymentUnit,
        cvr.env_cloud_location=src.env_cloud_location,
        cvr.env_cloud_role=src.env_cloud_role
        when not matched by target then 
        insert(env_time,PackageName,PackageVersion,PackageAge,DownloadMechanism,
        env_cloud_roleInstance,env_cloud_deploymentUnit,env_cloud_location,env_cloud_role)
        values(src.env_time,src.PackageName,src.PackageVersion,src.PackageAge,src.DownloadMechanism,
        src.env_cloud_roleInstance,src.env_cloud_deploymentUnit,src.env_cloud_location,src.env_cloud_role);
    end 
end

DataTable dt_init = new DataTable();
dt_init.Columns.Add("env_time", typeof(string));
dt_init.Columns.Add("PackageName", typeof(string));
dt_init.Columns.Add("PackageVersion", typeof(string));
dt_init.Columns.Add("PackageAge", typeof(string));
dt_init.Columns.Add("DownloadMechanism", typeof(string));
dt_init.Columns.Add("env_cloud_roleInstance", typeof(string));
dt_init.Columns.Add("env_cloud_deploymentUnit", typeof(string));
dt_init.Columns.Add("env_cloud_location", typeof(string));
dt_init.Columns.Add("env_cloud_role", typeof(string));

标签： c#sql-servertsql

解决方案

create proc insert_update_tableNew
@datatable versiontable_4 readonly
as 
begin
declare @tmptime nvarchar(255)
    begin 
        merge into dbo.CurrentVersion_1 cvr
        using @datatable src on cvr.PackageName =src.PackageName and cvr.env_cloud_roleInstance = src.env_cloud_roleInstance 
        when matched and cvr.env_time<src.env_time then 
        update set cvr.env_time=src.env_time,
        cvr.PackageVersion=src.PackageVersion,
        cvr.PackageAge=src.PackageAge,
        cvr.DownloadMechanism=src.DownloadMechanism,
        cvr.env_cloud_deploymentUnit=src.env_cloud_deploymentUnit,
        cvr.env_cloud_location=src.env_cloud_location,
        cvr.env_cloud_role=src.env_cloud_role
        when not matched by target then 
        insert(env_time,PackageName,PackageVersion,PackageAge,DownloadMechanism,
        env_cloud_roleInstance,env_cloud_deploymentUnit,env_cloud_location,env_cloud_role)
        values(src.env_time,src.PackageName,src.PackageVersion,src.PackageAge,src.DownloadMechanism,
        src.env_cloud_roleInstance,src.env_cloud_deploymentUnit,src.env_cloud_location,src.env_cloud_role);
    end 
end

使用新名称创建您的程序并再次调用。

这个解决方案对我有用！

上次运行时间约 6 分钟

新的运行时间约 4 秒

推荐阅读