cmd.ExecuteNonQuery() 很慢

问题描述

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

I want to update/insert my table in SQL Server using C# and a stored procedure. But I found it is slow to send the data to the database using cmd.ExecuteNonQuery(), so how can I improve the speed of data transmission? Now it is about 20,000 rows/min which cannot meet performance.

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

推荐答案

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

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

create Your Procedure with new name and recall again.

这个解决方案对我有用！

this solution worked for me!

上次运行时间约 6 分钟

Last Run time about 6 minutes

新的运行时间约 4 秒

New Run time about 4 seconds

这篇关于cmd.ExecuteNonQuery() 很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！