Timeout Expire on INSERT Query

Question

I am trying to insert a row in a table using simple INSERT Query in a transaction. It works fine in SQL Server but I am not able to insert the data using my business object.

I am calling a SELECT query using the Command as:

Using cm As New SqlCommand
    With cm
        .Connection = tr.Connection
        .Transaction = tr
        .CommandType = CommandType.Text
        .CommandText = Some Select Query
        .ExecuteScalar()
         '' Do something

        .CommandText = Insert Query
        .ExecuteNonQuery()
    End With
End Using

I am getting the Timeout period expired error at ".ExecuteNonQuery()" line.

Any other DML query is running perfectly fine at this point.

Can anyone tell me the reason?

Answer 1

Management Studio has it's connections set to not timeout, which could explain why it works there. You can do this in your code by setting your connection's .Timeout property to 0. However, this is not recommended unless you really mean it.

Instead, we need to figure out what's taking so long. Does the Management studio query take a long time to finish? Could it be waiting on a transaction commit? Can you profile to find out where the app is spending it's time?

Answer 2

It looks like your query is using a bad query plan. Try setting ARITHABORT ON and that should bypass the query plan:

Dim arithabortCmd As New SqlCommand("SET ARITHABORT ON", cn)
arithabortCmd.ExecuteNonQuery()

I would make sure the database indexes are updated by executing this SQL on the database:

-- Execute this to rebuild all for a given database. Replace the <databasename> after EXEC.
EXEC <databasename>..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'