title: What is the best way to load huge result set in memory?
body: <p>I am trying to load 2 huge resultsets(source and target) coming from different RDBMS but the problem with which i am struggling is getting those 2 huge result set in memory.</p>
  
  <p>Considering below are the queries to pull data from source and target:</p>
  
  <p>Sql Server -
  <code>select Id as LinkedColumn,CompareColumn from Source order by LinkedColumn</code></p>
  
  <p>Oracle -
  <code>select Id as LinkedColumn,CompareColumn from Target order by LinkedColumn</code></p>
  
  <p><strong>Records in Source :</strong> 12377200 </p>
  
  <p><strong>Records in Target :</strong> 12266800</p>
  
  <p>Following are the approaches i have tried with some statistics:</p>
  
  <p>1) <strong>open data reader approach for reading source and target data</strong>:</p>
  
  <pre><code>Total jobs running in parallel = 3
  
  Time taken by Job1 = 01:47:25
  
  Time taken by Job1 = 01:47:25
  
  Time taken by Job1 = 01:48:32
  
  There is no index on Id Column.
  </code></pre>
  
  <p>Major time is spent here:
  <code>var dr = command.ExecuteReader();</code></p>
  
  <p>Problems: 
  There are timeout issues also for which i have to kept <code>commandtimeout</code> to <code>0(infinity)</code> and it is bad.</p>
  
  <p>2) <strong>Chunk by chunk reading approach for reading source and target data:</strong> </p>
  
  <pre><code>   Total jobs = 1
     Chunk size : 100000
     Time Taken : 02:02:48
     There is no index on Id Column.
  </code></pre>
  
  <p>3) <strong>Chunk by chunk reading approach for reading source and target data:</strong></p>
  
  <pre><code>   Total jobs = 1
     Chunk size : 100000
     Time Taken : 00:39:40
     Index is present on Id column.
  </code></pre>
  
  <p>4) <strong>open data reader approach for reading source and target data:</strong></p>
  
  <pre><code>   Total jobs = 1
     Index : Yes
     Time: 00:01:43
  </code></pre>
  
  <p>5) <strong>open data reader approach for reading source and target data:</strong></p>
  
  <pre><code>   Total jobs running in parallel = 3
     Index : Yes
     Time: 00:25:12
  </code></pre>
  
  <p>I observed that while having an index on LinkedColumn does improve performance,  the problem is we are dealing with a 3rd party RDBMS table which might not have an index.</p>
  
  <p>We would like to keep database server as free as possible so data reader approach doesn't seem like a good idea because there will be lots of jobs running in parallel which will put so much pressure on database server which we don't want.</p>
  
  <p>Hence we want to fetch records in the resource memory from source to target and do 1 - 1 records comparison to keep the database server free.</p>
  
  <p><strong>Note:</strong> I want to do this in my c# application and don't want to use SSIS or Linked Server.</p>
  
  <p><strong>Update:</strong> </p>
  
  <pre><code>Source Sql Query Execution time in sql server management studio: 00:01:41
  
  Target Sql Query Execution time in sql server management studio:00:01:40
  </code></pre>
  
  <p>What will be the best way to read huge result set in memory?</p>
  
  <p><strong>Code:</strong> </p>
  
  <pre><code>static void Main(string[] args)
          {   
              // Running 3 jobs in parallel
               //Task&lt;string&gt;[] taskArray = { Task&lt;string&gt;.Factory.StartNew(() =&gt; Compare()),
          //Task&lt;string&gt;.Factory.StartNew(() =&gt; Compare()),
          //Task&lt;string&gt;.Factory.StartNew(() =&gt; Compare())
          //};
              Compare();//Run single job
              Console.ReadKey();
          }
  public static string Compare()
          {
              Stopwatch stopwatch = new Stopwatch();
              stopwatch.Start();
              var srcConnection = new SqlConnection("Source Connection String");
              srcConnection.Open();
              var command1 = new SqlCommand("select Id as LinkedColumn,CompareColumn from Source order by LinkedColumn", srcConnection);
              var tgtConnection = new SqlConnection("Target Connection String");
              tgtConnection.Open();
              var command2 = new SqlCommand("select Id as LinkedColumn,CompareColumn from Target order by LinkedColumn", tgtConnection);
              var drA = GetReader(command1);
              var drB = GetReader(command2);
              stopwatch.Stop();
              string a = stopwatch.Elapsed.ToString(@"d\.hh\:mm\:ss");
              Console.WriteLine(a);
              return a;
          }
        private static IDataReader GetReader(SqlCommand command)
          {
              command.CommandTimeout = 0;
              return command.ExecuteReader();//Culprit
          }
  </code></pre>