C # Client calls SQL Server stored procedure slow problem

The 1:c# code is

                        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 (Canewrepo                        RT)}); File.appendalltext (Strcurrentpath + @ "\test.txt", "Now on 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: The stored procedure being called, the original code is:

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) + ' W Arning 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 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 C  Apturelog (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--The following is the vendor section BEGIN tryexec [Sp_i_r Efresh_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: ' +erro R_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: ' + Cas T (Error_line () as nvarchar) + ' Warning Msg: ' +error_message ()) END catchbegin tryexec [Sp_i_refresh_vendor_area] @reporti D END Trybegin Catchinsert Capturelog (content) values (' [Sp_i_refresh_vendor_area] Warning Code: ' + cast (@ @error as nvarchar) + ' Warning Lines: ' + cast (E Rror_line () as nvarchar) + ' Warning Msg: ' +error_message ()) END catchbegin tryexec [Sp_i_refresh_vendor_area_hour] @repor Tidend trybegin Catchinsert capturelog (content) VALUES (' [Sp_i_refresh_vendor_area_hour] Warning Code: ' + cast (@ @error a s nvarchar) + ' Warning Lines: ' + Cast (error_line () as nvarchar) + ' Warning Msg: ' +error_message ()) END Catchbegin Tryex EC [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 = @reportidE ND

Note that there are several places to call other stored procedures, 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: Then the C # program runs slowly, but oddly enough, if you run this stored procedure separately in SQL Server Management Studio, it's fast,

4: Can not find the cause of the problem, and then all the stored procedure is called the code of the stored procedure is taken out, directly written in the main stored procedure, and no longer indirectly called, so the problem is resolved, but, why, it is unclear, temporarily recorded here.

C # Client calls SQL Server stored procedure slow problem