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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_catcher_MeraRptCAV_Insert_detail]( @TVP dbo.MeraRpt_CAV_AI READONLY)ASBEGINdeclare @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 = @reportidEND 注意里面有若干处 调用其它存储过程 ， 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 ： 找不到问题的原因 ， 后来是把 所有在存储过程被调用的存储过程的代码都取出来， 直接写在 主存储过程里， 而不再间接调用， 这样问题得到了解决，但是，其中原因何在， 还不清楚， 暂时记在这里。