title: C#: Passing DataTable to SQL Server very slow
body: <p>I have C# code that takes a DataTable returned from a SELECT on database A and passes it as a table-valued parameter to a stored procedure on database B via Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet method. There are about 200K rows in the table. In our test environment, where A and B are on the same server, it just takes a few seconds to execute the stored procedure. But in production, the ExecuteDataSet call is taking almost 30 minutes. Examination of the database logs shows that almost all that time is consumed between the start of the ExecuteDataSet call and the start of the stored procedure.</p>
  
  <p>In test, both databases are on a single server. In production A and B are located on different servers.</p>
  
  <p>What could be causing this extreme delay and what can be done about it? This happens only when running against the production servers. It has been run in multiple test environments with no problem.</p>
  
  <p><strong>Update 2016/04/08</strong></p>
  
  <p>The performance is reasonable if the table-valued parameter is passed as <em>IEnumerable&lt;SqlDataRecord&gt;</em> instead of <em>DataTable</em>. It takes 7 seconds to pass 50,000 records to using <em>SqlDataRecord</em>, but 400 seconds using <em>DataTable</em>. The problem is server-dependent. The same volume of data is passed to another server using <em>DataTable</em> with no problem.</p>