Stream reading over 10 million rows from a table in SQL Server

Question

What is the best strategy for reading millions of records from a table (in SQL Server 2012, BI instance) in streaming mode (e.g. in SQL Server Management Studio)?

I need to cache these entries locally (C # console application) for further processing.

Refresh - Sample code that works with SqlDataReader

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; using System.Threading; namespace ReadMillionsOfRows { class Program { static ManualResetEvent done = new ManualResetEvent(false); static void Main(string[] args) { Process(); done.WaitOne(); } public static async Task Process() { string connString = @"Server=;Database=;User Id=;Password=;Asynchronous Processing=True"; string sql = "Select * from tab_abc"; using (SqlConnection conn = new SqlConnection(connString)) { await conn.OpenAsync(); using (SqlCommand comm = new SqlCommand(sql)) { comm.Connection = conn; comm.CommandType = CommandType.Text; using (SqlDataReader reader = await comm.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { //process it here } } } } done.Set(); } } } 

Answer 1

Use SqlDataReader, it is only forward and fast. It will keep the link only to the record while it is in the reading area.

Answer 2

It depends on how your cache looks. If you are going to store everything in memory, and the DataSet is used as a cache, just read everything in the DataSet.

If not, use SqlDataReader , as suggested above, read the records one by one, storing them in your large cache.

However, note that there is already a very popular caching mechanism for large database tables — your database. With the right index configuration, the database is likely to outperform your cache.

Answer 3

You can use the Entity Framework and split the pages to choose with Take and Skip to retrieve rows by buffers. If you need to cache memory for such a large dataset, I would suggest using GC.GetTotalMemory to check if there is any free memory.