CODEWITHSUNDEEP
c#sql-servertable-valued-parameters
kevin cline
kevin cline

Reputation: 2736

C#: Passing DataTable to SQL Server very slow

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.

In test, both databases are on a single server. In production A and B are located on different servers.

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.

Update 2016/04/08

The performance is reasonable if the table-valued parameter is passed as IEnumerable<SqlDataRecord> instead of DataTable. It takes 7 seconds to pass 50,000 records to using SqlDataRecord, but 400 seconds using DataTable. The problem is server-dependent. The same volume of data is passed to another server using DataTable with no problem.

Upvotes: 4

Views: 2477

Answers (1)

user9398455
user9398455

Reputation: 11

We encountered this issue as well. The updated finding from Kevin Cline helped us figure out why it would run fast on one server and incredibly slow on another:

The performance is reasonable if the table-valued parameter is passed as IEnumerable instead of DataTable. It takes 7 seconds to pass 50,000 records to using SqlDataRecord, but 400 seconds using DataTable. The problem is server-dependent. The same volume of data is passed to another server using DataTable with no problem.

We discovered that having the SQL Server setting ForceEncryption was the difference between a fast-performing server and a slow-performing server. If ForceEncryption is set to Yes then it performs poorly; if it is set to No then it performs well. This setting is found in SQL Server Configuration Manager -> SQL Server Network Configuration -> Right click Protocols -> SelectProperties -> Certificate tab -> pick your certificate -> flags tab.

So to work around this issue, you have two options. Either turn off ForceEncryption setting on your database (may not be advisable), or avoid using DataTables and go for the SqlDataRecords instead as Kevin Cline suggested.

Upvotes: 1

Related Questions