Databases
StackOverflow2013
Postsdbo
POSqlCommand calling a SQL Server stored procedure times out
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSqlCommand calling a SQL Server stored procedure times out
    primarykey
    data
    text
    <p>I've got a conversion utility that basically copies values from one table to another. It's worked great for a while, but I've run into a strange issue with one customer. They got through 1.5 million records with the utility but now it is completely halted.</p> <p>When calling a stored procedure from VB.Net, it just hangs until the SqlCommand times out. Calling the same sproc from Management Studio executes instantly. My VB.Net code for the SqlCommand is below (<code>insertConn</code> is defined and opened earlier, <code>dr</code> is a SqlDataReader that has been populated in a previous step from completely different SqlConnection and SqlCommand instances):</p> <pre><code>Dim conn As New SqlConnection("connection string here") Dim insertConn As New SqlConnection("connection string here") Dim dr As SqlDataReader = Nothing Dim readCommand As New SqlCommand("my query here", conn) conn.Open() insertConn.Open() ... dr = readCommand.ExecuteReader() ... While dr.Read() Using insertCommand = New SqlCommand("dmDocumentFieldInsert", insertConn) insertCommand.CommandType = CommandType.StoredProcedure insertCommand.Parameters.AddWithValue("@DocumentKey", dr("DocumentKey")) insertCommand.Parameters.AddWithValue("@FieldId", "TITLE") insertCommand.Parameters.AddWithValue("@FieldValue", dr("DocumentTitle")) insertCommand.ExecuteNonQuery() End Using End While </code></pre> <p>I've tried restarting SQL Server to clear any locks, recompiling the sproc, increasing the SqlCommand and SqlConnection timeouts all to no avail. </p> <p>I checked the data that is getting added to the parameters and it's valid data...if I manually call the sproc with the same data it works fine.</p> <p>I originally was not using the <code>Using</code> block but changed that to see if there was some resource issue that wasn't getting disposed/closed. Memory usage of the utility hovers at just around 5MB, so there don't appear to be any memory issues.</p> <p>Does anyone have suggestions on what to try next for a solution?</p> <p><strong>EDIT</strong> Added loop and init code per comment requests</p> <p><strong>EDIT</strong> I updated statistics and rebuilt the table indexes, no change.</p> <p><strong>EDIT</strong> There are three indexes on the table that the data is getting copied to (dmDocumentField). If I disable all three indexes then the sproc executes perfectly, albeit much slower than when the index is present. If I enable any one of them, then the utility gets through a couple hundred records at most then dies with the same timeout on the sproc. Deleting and recreating the index has no effect. Table structure and indexes are as follows: </p> <pre><code>CREATE TABLE [dbo].[dmDocumentField]( [FieldKey] [bigint] IDENTITY(1,1) NOT NULL, [DocumentKey] [char](36) NOT NULL, [FieldId] [varchar](10) NOT NULL, [FieldValue] [varchar](255) NOT NULL, CONSTRAINT [PK_dmDocumentField] PRIMARY KEY NONCLUSTERED ( [FieldKey] ASC, [DocumentKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] </code></pre> <p>Indexes (besides the PK): </p> <pre><code>CREATE NONCLUSTERED INDEX [dmDocumentField_DocumentKey] ON [dbo].[dmDocumentField] ( [DocumentKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] </code></pre> <p>.</p> <pre><code>CREATE NONCLUSTERED INDEX [dmDocumentField_DocumentKey_IFieldId_IFieldValue] ON [dbo].[dmDocumentField] ( [DocumentKey] ASC ) INCLUDE ( [FieldId], [FieldValue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKettch19
    1. USKettch19
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSqlCommand calling a SQL Server stored procedure times out
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSqlCommand calling a SQL Server stored procedure times out
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI should also mention, the utility is in VB.Net 4 and the SQL Server in question is SQL Server 2008 R2 Standard.
      singulars
      1. POSqlCommand calling a SQL Server stored procedure times out
      1. USKettch19
    2. COAnything unusual in the SQL profiler?
      singulars
      1. POSqlCommand calling a SQL Server stored procedure times out
      1. USDennis Traub
    3. COYou could try using the data access methods in [Enterprise Library](http://msdn.microsoft.com/en-us/library/ff648951.aspx).
      singulars
      1. POSqlCommand calling a SQL Server stored procedure times out
      1. USDarth Continent
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload