2

From a windows application I load data for offline processing. I do this using a SqlDataReader and bulkcopy. See code below

But one of my tables contains about 10.000 rows. When on the same network as the database server the proces takes about 2 seconds. Connected through a VPN Connection and a broadband router it takes about 70 seconds. This is due to latency in the network caused by the way the SqlDataReader delivers the result row by row.

Is there a faster way of retrieving the data than the SqlDataReader when there are many records.

    public void bulkCopyTable(string getDataStoredProcedure, string destinationTable)
    {
        using (SqlConnection MasterConnection = new SqlConnection(MasterServerDbConnectionString))
        {
            MasterConnection.Open();

            using (SqlConnection connection = new SqlConnection(localDbConnectionString))
            {
                connection.Open();

                SqlCommand cmdMasterData = new SqlCommand(getDataStoredProcedure, MasterConnection);
                cmdMasterData.CommandType = CommandType.StoredProcedure;

                SqlParameter paramMasterData1 = new SqlParameter("@getAll", SqlDbType.Bit);
        paramMasterData1.Value = 1;
                cmdMasterData.Parameters.Add(paramMasterData1);

                cmdMasterData.CommandTimeout = 60;

                using (SqlDataReader dataReaderMasterData = cmdMasterData.ExecuteReader())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(AutoProgConnection))
                    {
                        bulkCopy.DestinationTableName = destinationTable;

                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(dataReaderMasterData);
                        }
                        catch (Exception ex)
                        {
                            ...
                        }
                    }                        
                }
            }
        }
    }
jarlh
  • 42,561
  • 8
  • 45
  • 63
Thue
  • 21
  • 3
  • 2
    I guess you could try to bulk write to a file on the server, and then move that file across the network, and then load the file into your application. Personally however, I'd rather use resources improving the network than inventing such a solution. – Allan S. Hansen Jan 11 '16 at 12:44
  • Check this thread [How does fetching data from SQL Server to SqlDataReader work?](http://stackoverflow.com/questions/6033451/how-does-fetching-data-from-sql-server-to-sqldatareader-work). Try playing with the parameter mentioned there. – Ivan Stoev Jan 11 '16 at 12:55
  • I don't think that the reader just gets one row at a time from the server. It seems more like the server starts to stream the result ti the client: http://stackoverflow.com/questions/23467482/how-does-sqldatareader-handle-really-large-queries – jgauffin Jan 11 '16 at 13:39
  • http://www.codeproject.com/Articles/420217/DataSet-vs-DataReader. Datareader fetches the record from database and stores in the network buffer and gives whenever requests. DataReader releasese the records as query executes and do not wait for the entire query to execute. I will try and fiddle with the packet size to see if that has an effect. – Thue Jan 11 '16 at 14:38
  • Can you compare execution time of reading the source data in something like SSMS over the VPN? Does it take a similar amount of time if you just execute the query? Is there any way to filter the data pull to only "changed" records or something rather that repull the entire source table every time? – Peter Tirrell Jan 11 '16 at 15:31
  • I have now tried changing my connectionstring to include PacketSize=6144, but that does not have any effect. I have also tried using SSMS, and here the result is the same. 2 seconds without VPN and 70 seconds with VPN. Next attemp wil be to check whether a Dataset is loaded different than a SqlDataReader. – Thue Jan 12 '16 at 08:15

1 Answers1

0

There are several ways of retrieving data. Unfortunately they all seem to be using the Datareader internally causing latency when there are manye records. As proposed by Allan i will transfer the data in another way. I will create a webservice that either returns a DataSet or a serilization of the data in a compressed format.

Thue
  • 21
  • 3