SqlParameter和ExecuteNonQuery导致不可重复的超时

问题描述

我最近继承了一些偶尔会出现超时问题的代码.我最熟悉ORM，因此无法确定此代码中是否有错误.如果没有超时，它会在几秒钟内起作用.超时大约需要一分钟.

I recently inherited some code that is having occasional time-out issues. I am mostly familiar with ORM's, so I am having trouble determining if anything is wrong in this code. When it does not time out, it works in a couple of seconds. Time-outs take about a minute.

这是代码:

Dim sql As String = "UPDATE VendorInfo SET " & _
           "AbsInkUpdateStatus=@AbsInkUpdateStatus, AccountingNotes=@AccountingNotes, " & _
           "AccountNumber=@AccountNumber, Address_Ship=@Address_Ship, " & _
           "Address0=@Address0, Address1=@Address1, Address2=@Address2, ArtEmail=@ArtEmail, " & _
           "ArtRequirements=@ArtRequirements, City=@City, " & _
           "City_Ship=@City_Ship, CreditLimit=@CreditLimit, DisplayName=@DisplayName, " & _
           "EarlyBy=@EarlyBy, EarlyConfirm=@EarlyConfirm, " & _
           "EarlyHas=@EarlyHas, EarlyNet=@EarlyNet, " & _
           "EarlyNotes=@EarlyNotes, EarlyOn=@EarlyOn, " & _
           "EarlyPercent=@EarlyPercent, Email=@Email, " & _
           "Fax=@Fax, PContactName=@PContactName, " & _
           "Phone=@Phone, Pricing=@Pricing, " & _
           "PromosBy=@PromosBy, PromosConfirm=@PromosConfirm, " & _
           "PromosHas=@PromosHas, PromosNotes=@PromosNotes, " & _
           "PromosOn=@PromosOn, QBName=@QBName, QBTerms=@QBTerms, RebateBy=@RebateBy, " & _
           "RebateConfirm=@RebateConfirm, RebateHas=@RebateHas, " & _
           "RebateNotes=@RebateNotes, RebateOn=@RebateOn, " & _
           "RushPolicy=@RushPolicy, SampleBy=@SampleBy, " & _
           "SampleConfirm=@SampleConfirm, SampleHas=@SampleHas, " & _
           "SampleNotes=@SampleNotes, SampleOn=@SampleOn, " & _
           "SamplePolicy=@SamplePolicy, ShippingBy=@ShippingBy, " & _
           "ShippingConfirm=@ShippingConfirm, ShippingHas=@ShippingHas, " & _
           "ShippingNotes=@ShippingNotes, ShippingOn=@ShippingOn, " & _
           "ShipTo=@ShipTo, [SMP FAX]=@SMP_FAX, " & _
           "SpecialBy=@SpecialBy, SpecialConfirm=@SpecialConfirm, " & _
           "SpecialHas=@SpecialHas, SpecialInstructions=@SpecialInstructions, " & _
           "SpecialNotes=@SpecialNotes, SpecialOn=@SpecialOn, " & _
           "SpecialPricingShort=@SpecialPricingShort, State=@State, " & _
           "State_Ship=@State_Ship, TermsBy=@TermsBy, " & _
           "TermsConfirm=@TermsConfirm, TermsHas=@TermsHas, " & _
           "TermsNotes=@TermsNotes, TermsOn=@TermsOn, " & _
           "TypicalShippingCost=@TypicalShippingCost, UpdateStatus=@UpdateStatus, " & _
           "VendorName=@VendorName, VendorStrength=@VendorStrength, " & _
           "WebPage=@WebPage, ZipCode=@ZipCode, EmailPO=@EmailPO, SendPO=@SendPO, " & _
           "ZipCode_Ship=@ZipCode_Ship WHERE ID=@ID"
        Dim p() As SqlParameter = New SqlParameter(74) {}
        p(0) = MakeSQLParam("@AbsInkUpdateStatus", SqlDbType.VarChar, 50, SS(ht("AbsInkUpdateStatus")))
        p(1) = MakeSQLParam("@AccountingNotes", SqlDbType.VarChar, 500, SS(ht("AccountingNotes")))
        p(2) = MakeSQLParam("@AccountNumber", SqlDbType.NVarChar, 35, SS(ht("AccountNumber")))
        p(3) = MakeSQLParam("@Address_Ship", SqlDbType.NVarChar, 50, SS(ht("Address_Ship")))
        p(68) = MakeSQLParam("@Address0", SqlDbType.VarChar, 100, SS(ht("Address0")))
        p(4) = MakeSQLParam("@Address1", SqlDbType.NVarChar, 50, SS(ht("Address1")))
        p(69) = MakeSQLParam("@Address2", SqlDbType.VarChar, 50, SS(ht("Address2")))
        p(5) = MakeSQLParam("@ArtEmail", SqlDbType.NVarChar, 100, SS(ht("ArtEmail")))
        p(6) = MakeSQLParam("@ArtRequirements", SqlDbType.NText, SS(ht("ArtRequirements")))
        p(7) = MakeSQLParam("@City", SqlDbType.NVarChar, 50, SS(ht("City")))
        p(8) = MakeSQLParam("@City_Ship", SqlDbType.NVarChar, 50, SS(ht("City_Ship")))
        p(72) = MakeSQLParam("@CreditLimit", SqlDbType.Float, ht("CreditLimit"))
        If ((Not ht.ContainsKey("EmailPO")) OrElse IsDBNull(ht("EmailPO")) OrElse String.IsNullOrEmpty(ht("EmailPO"))) Then
            p(73) = MakeSQLParam("@EmailPO", SqlDbType.Bit, DBNull.Value)
        Else
            p(73) = MakeSQLParam("@EmailPO", SqlDbType.Bit, Convert.ToBoolean(ht("EmailPO")))
        End If
        If ((Not ht.ContainsKey("SendPO")) OrElse IsDBNull(ht("SendPO")) OrElse String.IsNullOrEmpty(ht("SendPO"))) Then
            p(74) = MakeSQLParam("@SendPO", SqlDbType.NVarChar, 255, DBNull.Value)
        Else
            p(74) = MakeSQLParam("@SendPO", SqlDbType.NVarChar, 255, SS(ht("SendPO")))
        End If
        p(9) = MakeSQLParam("@DisplayName", SqlDbType.NVarChar, 255, SS(ht("DisplayName")))
        p(10) = MakeSQLParam("@EarlyBy", SqlDbType.VarChar, 5, SS(ht("EarlyBy")))
        p(11) = MakeSQLParam("@EarlyConfirm", SqlDbType.VarChar, 50, SS(ht("EarlyConfirm")))
        p(12) = MakeSQLParam("@EarlyHas", SqlDbType.VarChar, 10, SS(ht("EarlyHas")))
        p(13) = MakeSQLParam("@EarlyNet", SqlDbType.VarChar, 10, SS(ht("EarlyNet")))
        p(14) = MakeSQLParam("@EarlyNotes", SqlDbType.VarChar, 125, SS(ht("EarlyNotes")))
        p(15) = MakeSQLParam("@EarlyOn", SqlDbType.DateTime, DtoDB(ht("EarlyOn")))
        p(16) = MakeSQLParam("@EarlyPercent", SqlDbType.Decimal, DBS(ht("EarlyPercent"), True))
        p(17) = MakeSQLParam("@Email", SqlDbType.NVarChar, 75, SS(ht("Email")))
        p(18) = MakeSQLParam("@Fax", SqlDbType.NVarChar, 50, SS(ht("Fax")))
        p(19) = MakeSQLParam("@ID", SqlDbType.Int, ht("ID"))
        p(20) = MakeSQLParam("@PContactName", SqlDbType.NVarChar, 50, SS(ht("PContactName")))
        p(21) = MakeSQLParam("@Phone", SqlDbType.NVarChar, 50, SS(ht("Phone")))
        p(22) = MakeSQLParam("@Pricing", SqlDbType.NVarChar, 255, SS(ht("Pricing")))
        p(23) = MakeSQLParam("@PromosBy", SqlDbType.VarChar, 5, SS(ht("PromosBy")))
        p(24) = MakeSQLParam("@PromosConfirm", SqlDbType.VarChar, 50, SS(ht("PromosConfirm")))
        p(25) = MakeSQLParam("@PromosHas", SqlDbType.VarChar, 10, SS(ht("PromosHas")))
        p(26) = MakeSQLParam("@PromosNotes", SqlDbType.VarChar, 125, SS(ht("PromosNotes")))
        p(27) = MakeSQLParam("@PromosOn", SqlDbType.DateTime, DtoDB(ht("PromosOn")))
        p(70) = MakeSQLParam("@QBName", SqlDbType.VarChar, 41, SS(ht("QBName")))
        p(71) = MakeSQLParam("@QBTerms", SqlDbType.VarChar, 31, SS(ht("QBTerms")))
        p(28) = MakeSQLParam("@RebateBy", SqlDbType.VarChar, 5, SS(ht("RebateBy")))
        p(29) = MakeSQLParam("@RebateConfirm", SqlDbType.VarChar, 50, SS(ht("RebateConfirm")))
        p(30) = MakeSQLParam("@RebateHas", SqlDbType.VarChar, 10, SS(ht("RebateHas")))
        p(31) = MakeSQLParam("@RebateNotes", SqlDbType.VarChar, 125, SS(ht("RebateNotes")))
        p(32) = MakeSQLParam("@RebateOn", SqlDbType.DateTime, DtoDB(ht("RebateOn")))
        p(33) = MakeSQLParam("@RushPolicy", SqlDbType.NVarChar, 200, SS(ht("RushPolicy")))
        p(34) = MakeSQLParam("@SampleBy", SqlDbType.VarChar, 5, SS(ht("SampleBy")))
        p(35) = MakeSQLParam("@SampleConfirm", SqlDbType.VarChar, 50, SS(ht("SampleConfirm")))
        p(36) = MakeSQLParam("@SampleHas", SqlDbType.VarChar, 10, SS(ht("SampleHas")))
        p(37) = MakeSQLParam("@SampleNotes", SqlDbType.VarChar, 125, SS(ht("SampleNotes")))
        p(38) = MakeSQLParam("@SampleOn", SqlDbType.DateTime, DtoDB(ht("SampleOn")))
        p(39) = MakeSQLParam("@SamplePolicy", SqlDbType.NVarChar, 255, SS(ht("SamplePolicy")))
        p(40) = MakeSQLParam("@ShippingBy", SqlDbType.VarChar, 5, SS(ht("ShippingBy")))
        p(41) = MakeSQLParam("@ShippingConfirm", SqlDbType.VarChar, 50, SS(ht("ShippingConfirm")))
        p(42) = MakeSQLParam("@ShippingHas", SqlDbType.VarChar, 10, SS(ht("ShippingHas")))
        p(43) = MakeSQLParam("@ShippingNotes", SqlDbType.VarChar, 125, SS(ht("ShippingNotes")))
        p(44) = MakeSQLParam("@ShippingOn", SqlDbType.DateTime, DtoDB(ht("ShippingOn")))
        p(45) = MakeSQLParam("@ShipTo", SqlDbType.NVarChar, 100, SS(ht("ShipTo")))
        p(46) = MakeSQLParam("@SMP_FAX", SqlDbType.NVarChar, 50, SS(ht("SMP FAX")))
        p(47) = MakeSQLParam("@SpecialBy", SqlDbType.VarChar, 5, SS(ht("SpecialBy")))
        p(48) = MakeSQLParam("@SpecialConfirm", SqlDbType.VarChar, 50, SS(ht("SpecialConfirm")))
        p(49) = MakeSQLParam("@SpecialHas", SqlDbType.VarChar, 10, SS(ht("SpecialHas")))
        p(50) = MakeSQLParam("@SpecialInstructions", SqlDbType.Text, SS(ht("SpecialInstructions")))
        p(51) = MakeSQLParam("@SpecialNotes", SqlDbType.VarChar, 255, SS(ht("SpecialNotes")))
        p(52) = MakeSQLParam("@SpecialOn", SqlDbType.DateTime, DtoDB(ht("SpecialOn")))
        p(53) = MakeSQLParam("@SpecialPricingShort", SqlDbType.VarChar, 80, SS(ht("SpecialPricingShort")))
        p(54) = MakeSQLParam("@State", SqlDbType.NVarChar, 25, SS(ht("State")))
        p(55) = MakeSQLParam("@State_Ship", SqlDbType.NVarChar, 25, SS(ht("State_Ship")))
        p(56) = MakeSQLParam("@TermsBy", SqlDbType.VarChar, 5, SS(ht("TermsBy")))
        p(57) = MakeSQLParam("@TermsConfirm", SqlDbType.VarChar, 50, SS(ht("TermsConfirm")))
        p(58) = MakeSQLParam("@TermsHas", SqlDbType.VarChar, 10, SS(ht("TermsHas")))
        p(59) = MakeSQLParam("@TermsNotes", SqlDbType.VarChar, 125, SS(ht("TermsNotes")))
        p(60) = MakeSQLParam("@TermsOn", SqlDbType.DateTime, DtoDB(ht("TermsOn")))
        p(61) = MakeSQLParam("@TypicalShippingCost", SqlDbType.NText, SS(ht("TypicalShippingCost")))
        p(62) = MakeSQLParam("@UpdateStatus", SqlDbType.VarChar, 50, SS(ht("UpdateStatus")))
        p(63) = MakeSQLParam("@VendorName", SqlDbType.NVarChar, 100, SS(ht("VendorName")))
        p(64) = MakeSQLParam("@VendorStrength", SqlDbType.NVarChar, 100, SS(ht("VendorStrength")))
        p(65) = MakeSQLParam("@WebPage", SqlDbType.NVarChar, 100, SS(ht("WebPage")))
        p(66) = MakeSQLParam("@ZipCode", SqlDbType.NVarChar, 15, SS(ht("ZipCode")))
        p(67) = MakeSQLParam("@ZipCode_Ship", SqlDbType.NVarChar, 15, SS(ht("ZipCode_Ship")))

        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection(ConfigurationManager.AppSettings("LeadsConnectionString"))
            SqlHelper.ExecuteNonQuery(con, CommandType.Text, sql, p)
        Catch ex As Exception
            Throw New Exception(ex.Message & " UPDATE failed for 'VendorInfo' " & ht.TableAsString, ex)
        Finally
            If (Not con Is Nothing) Then con.Close()
        End Try

SS是安全字符串"函数，MakeSQLParam将方向设置为input，并确保不违反大小值.

SS is "safe string" function and MakeSQLParam sets the direction to input and makes sure the size value is not violated.

所以我的问题是:有人认为此代码有什么问题，可能会导致速度缓慢吗?还是有人知道我想检查的其他方法可以帮助解决此问题?

So my question is: does anyone see something wrong with this code that might cause sluggishness? Or does anyone know of anything else I might want to check that could help with this issue?

谢谢！

推荐答案

这很可能是由于参数嗅探所致，导致缓存了不合适的查询计划.

It is likely due to parameter sniffing, causing an inappropriate query plan to be cached.

由于参数太多，您可能每次都必须重新编译查询.

With so many parameters, you might have to recompile the query each time.

首先尝试重建索引并确保统计信息是最新的:(注意在生产环境中...)

First try rebuilding your indexes and ensuring statistics are up to date: (CAUTION in production environments...)

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

然后，检查查询计划以了解在SSMS中运行SQL的快速和慢速情况.如果没有烟枪，请检查可能持有锁的其他过程.

Then, examine the query plans for the fast and slow cases of running the SQL in SSMS. If there are no smoking guns, then examine other processes that might be holding locks.

[StackOverflow上已经回答了许多相关问题.]

[There are many related questions already answered on StackOverflow.]

这篇关于SqlParameter和ExecuteNonQuery导致不可重复的超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！