c#用戶端調用sql server 預存程序速度慢的問題

標籤：blog   io   os   ar   sp   div   on   2014   art   

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 intSET 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 tvpselect @reportid = max(reportid) from merarpt--refresh area report--exec MeraRpt_Refresh_Area --exec MeraRpt_Refresh_cr--Update Report By PrefixBEGIN TRYexec [sp_i_Refresh_Cust_Prefix] @reportidEND TRYBEGIN CATCHinsert 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 HourlyBEGIN TRYexec [sp_i_Refresh_Cust_Prefix_hour] @reportidEND TRYBEGIN CATCHinsert 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 AreaBEGIN TRYexec [sp_i_Refresh_Cust_Area] @reportidEND TRYBEGIN CATCHinsert 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 HourlyBEGIN TRYexec [sp_i_Refresh_Cust_Area_Hour] @reportidEND TRYBEGIN CATCHinsert 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 RouteBEGIN TRYexec [sp_i_Refresh_Cust_Area_Route] @reportidEND TRYBEGIN CATCHinsert 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 TRYexec [sp_i_Refresh_Vendor_Prefix] @reportid END TRYBEGIN CATCHinsert 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 CATCHBEGIN TRYexec [sp_i_Refresh_Vendor_Prefix_hour] @reportidEND TRYBEGIN CATCHinsert 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 CATCHBEGIN TRYexec [sp_i_Refresh_Vendor_Area] @reportid   END TRYBEGIN CATCHinsert 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 CATCHBEGIN TRYexec [sp_i_Refresh_Vendor_Area_hour] @reportidEND TRYBEGIN CATCHinsert 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 CATCHBEGIN TRYexec [sp_i_Refresh_Vendor_Area_Route] @reportidEND TRYBEGIN CATCHinsert 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 TRYexec [sp_i_Refresh_Vendor_Prefix] @reportid END TRYBEGIN CATCHinsert 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 ： 找不到問題的原因 ， 後來是把 所有在預存程序被調用的預存程序的代碼都取出來， 直接寫在 主預存程序裡， 而不再間接調用， 這樣問題得到瞭解決，但是，其中原因何在， 還不清楚， 暫時記在這裡。

 

c#用戶端調用sql server 預存程序速度慢的問題