SqlParameter and ExecuteNonQuery causing unrepeata

Question

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.

Here is the code:

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 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?

Thanks!

Answer 1

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

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.

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

Answer 2

Is this running against SQL Server? You probably are hitting a deadlock when it times out. You'll want to maybe monitor what transactions are running when you encounter a timeout, and/or try running the statement manually in Management Studio and see if you run into a deadlock.

If you have any Agent jobs running that access the same tables, or maybe other processes that might access the same tables, that could be your problem.