I have C # code that returns the DataTable returned from SELECT in database A and passes it as a parameter stored in the table to the stored procedure in database B through the Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet method. The table has about 200 thousand rows. In our test environment, where A and B are on the same server, it takes only a few seconds to execute the stored procedure. But during the production process, calling ExecuteDataSet takes almost 30 minutes. Examination of database logs shows that almost all this time is consumed between the start of the ExecuteDataSet call and the start of the stored procedure.
In the test, both databases are on the same 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 only happens when working with production servers. It runs in several test environments with no problems.
Update 2016/04/08
Performance is reasonable if the table-value parameter is accepted as IEnumerable <SqlDataRecord> instead of DataTable. It takes 7 seconds to transfer 50,000 records using SqlDataRecord, but 400 seconds using DataTable. The problem is with the server. The same amount of data is transferred to another server using the DataTable without any problems.
source
share