代码使超时 [英] Code Keeps Timing Out

问题描述

因此，由于某种原因，我们已经获得了这段代码，这些代码会不断超时.它不是正在运行的存储过程，因为它可以正常运行.另外，如果我们从c#代码中删除参数，则代码将运行.该参数不断中断(导致其超时)，我们无法找出原因.

So, we've got this set of code that, for some reason, keeps timing out. It's not the stored procedure that it's running, because that runs fine. Also, if we remove the parameter from the c# code, the code runs. The parameter keeps breaking (causing it to time out) and we can't figure out why.

c#:

public static PTWViewList GetList(int studynumber) 
        {
            PTWViewList tempList = new PTWViewList();
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.cnARDB))
            {
                string spName = "ardb.PTWViewSelect";
                SqlCommand myCommand = new SqlCommand(spName, myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@study", studynumber); 

                myConnection.Open();
                using (NullableDataReader myReader = new NullableDataReader(myCommand.ExecuteReader())) /*this is where the code times out*/
                {
                    tempList = new PTWViewList();
                    while (myReader.Read())
                    {
                        tempList.Add(FillDataRecord(myReader));
                    }
                    myReader.Close();
                }
            }

            tempList.ListCount = tempList.Count;
            return tempList;
        }

存储过程:

CREATE PROCEDURE [ardb].[PTWViewSelect] 
    @studynumber int = NULL,
    @quoteid uniqueidentifier = NULL,
    @lineitemid uniqueidentifier = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        [Study]
        ,[LineItemID]
        ,[QuoteID]
        ,[Total]
        ,[COOP]
        ,[VendorCost]
        ,[CustCost]
        ,[LineItemNumber]
        ,[StudyTypeCode]
        ,[GroupLeader]
        ,[PTWDate]
        ,[PONumber]
        ,[POStatus]
        ,[StudyDirector]
        ,[SL_DESC_L]
        ,[SL_Code]
        ,ProjectDescription
        ,CreatedBy
        ,chARProcess
        ,CODate
    FROM
        [ARDB].[dbo].[PTWView]
    WHERE
        (@studynumber is null or StudyNumber=@studynumber)
        AND (@quoteid is null or QuoteID=@quoteid)
        AND (@lineitemid is null or LineItemID = @lineitemid)
END

推荐答案

将arithabort设置为关闭将使sp花费45秒，而不是将其设置为1.将其重新设置为1.我更新了存储过程以将其设置为on ，应用程序中没有任何更改.将其更改为关闭，无变化.然后，我删除了更新，然后该应用正常运行.

setting arithabort off made the sp take 45 seconds as opposed to 1. setting it back on changed it back to 1. I updated the stored procedure to set it on, no change in the app. Changed it to off, no change. I then removed the update and then the app worked fine.

我相信发生的事情是更新存储过程导致其重新编译，从而解决了该问题.我对此不是100％肯定.

I believe what happened is that updating the stored procedure caused it to recompile, fixing the issue. I'm not 100% sure on this though.

这篇关于代码使超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！