c#客户端调用sql server 存储过程速度慢的问题-优快云博客

本文探讨了使用C#代码与SQL存储过程进行数据操作时遇到的性能瓶颈，包括代码实现、存储过程设计以及性能优化策略。通过实例分析，揭示了在C#中直接调用存储过程相较于在SSMS中执行性能差异的原因，并分享了解决方案，旨在提升系统响应速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 ： c#代码是

                        SqlConnection connNew = null;

                        connNew = new SqlConnection(@"Data Source="XXX");
                        connNew.Open();

                        SqlCommand commNew = connNew.CreateCommand();
                        commNew.CommandText = "sp_catcher_MeraRptCAV_Insert_detail";
                        commNew.CommandType = System.Data.CommandType.StoredProcedure;
                        commNew.CommandTimeout = 300;

                        commNew.Parameters.Add(new SqlParameter()
                        {
                            ParameterName = "@TVP",
                            SqlDbType = SqlDbType.Structured,
                            Value = GetDataTableParamNew(CANewReport)
                        });

                        //File.AppendAllText(strCurrentPath + @"\test.txt", "Now about to insert report " + reportID + " records into db " + "\r\n");
                        try
                        {
                            commNew.CommandTimeout = 300;
                            commNew.ExecuteNonQuery();

                        }

                        catch (Exception ex)
                        {

                            //Logger.Error("Execute Procedure 'sp_merarpt_create' Failed, Error Message : '" + ex.Message + "'.");
                        }
                        finally
                        {
                            commNew.Dispose();
                            if (commNew != null)
                            {
                                connNew.Close();
                                connNew.Dispose();
                            }
                        }

2 : 被调用的存储过程，原来的代码是：

USE [Smartbilling_AI]
GO
/****** Object:  StoredProcedure [dbo].[sp_catcher_MeraRptCAV_Insert_detail]    Script Date: 10/29/2014 09:28:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_catcher_MeraRptCAV_Insert_detail]
(
	@TVP dbo.MeraRpt_CAV_AI READONLY
)
AS
BEGIN
declare @reportid as int

	SET NOCOUNT ON;

        INSERT INTO MeraRpt_Detail_ByPrefix
        (
		[reportID]
		,[Customer]
		,[Prefix]
		,[Route]
		,[ASRSuccessCalls]
		,[ACDSuccessCalls]
		,[TotalCalls]
		,[TotalMins]
		,[ASR]
		,[ACD]
		,[Fee]
		,[Cost]
		,[Profit]
	)
	SELECT [reportID]
		,[Customer]
		,[Area]
		,[Vendor]
		
		,[ASRsuccessCalls]
		,[ACDsuccessCalls]
		,[totalCalls]
		,[totalMins]
		
		,[ASR]
		,[ACD]
		,[Fee]
		,[Cost]
		,[profix]
		
	FROM @TVP tvp


	select @reportid = max(reportid) from merarpt

	

	--refresh area report
	--exec MeraRpt_Refresh_Area 
	--exec MeraRpt_Refresh_cr

	--Update Report By Prefix
	
	BEGIN TRY
		exec [sp_i_Refresh_Cust_Prefix] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Cust_Prefix] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH
	

	--Update Report By Prefix Hourly
	BEGIN TRY
		exec [sp_i_Refresh_Cust_Prefix_hour] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Cust_Prefix_hour] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH

	----Update Report By Area
	BEGIN TRY
		exec [sp_i_Refresh_Cust_Area] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Cust_Area] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH

	----Update Report By Area Hourly
	BEGIN TRY
		exec [sp_i_Refresh_Cust_Area_Hour] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Cust_Area_Hour] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH

	----Update Report By Area Route
	BEGIN TRY
		exec [sp_i_Refresh_Cust_Area_Route] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Cust_Area_Route] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH


    --以下是 vendor 部分 

	BEGIN TRY
		exec [sp_i_Refresh_Vendor_Prefix] @reportid 
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Vendor_Prefix] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH


	BEGIN TRY
		exec [sp_i_Refresh_Vendor_Prefix_hour] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Vendor_Prefix_hour] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH


	BEGIN TRY
		exec [sp_i_Refresh_Vendor_Area] @reportid   
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Vendor_Area] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH

	BEGIN TRY
		exec [sp_i_Refresh_Vendor_Area_hour] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Vendor_Area_hour] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH
	
	
	BEGIN TRY
		exec [sp_i_Refresh_Vendor_Area_Route] @reportid
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Vendor_Area_Route] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH
	
    
	update merarpt set FinishProcessedTime = getdate() where reportid = @reportid
END

注意里面有若干处调用其它存储过程， just like

	BEGIN TRY
		exec [sp_i_Refresh_Vendor_Prefix] @reportid 
	END TRY
	BEGIN CATCH
		insert capturelog 
		(content) values ('[sp_i_Refresh_Vendor_Prefix] Warning Code:' + cast (@@error as nvarchar) + ' Warning Lines: ' + Cast (ERROR_LINE() as nvarchar) + ' Warning Msg: '+ERROR_MESSAGE())
	END CATCH

3 : 然后c#程序运行的很慢，但奇怪的是，如果在sql server management studio 里单独运行这个存储过程，速度又很快，

4 ：找不到问题的原因，后来是把所有在存储过程被调用的存储过程的代码都取出来，直接写在主存储过程里，而不再间接调用，这样问题得到了解决，但是，其中原因何在，还不清楚，暂时记在这里。

转载于:https://www.cnblogs.com/lthxk-yl/p/4059528.html