15

I have about 500K rows I need to return from my database (please don't ask why).

I will then need to save these results as XML (more URGH) and the ftp this file to somewhere magical.

I also need to transform the each row in the result set.

Right now, this is what I'm doing with say .. TOP 100 results:

  • using Dapper's Query<T> method, which throws the entire result set into memory
  • I then use AutoMapper to convert the database POCO to my FileResult POCO
  • Convert to XML
  • Then save this collection to the file system
  • Then FTP

This works fine for 100 rows, but I get an Out Of Memory exception with AutoMapper when trying to convert the 500K results to a new collection.

So, I was wondering if I could do this...

  • Stream data from DB using Dapper
  • For each row, automapper it
  • Convert to XML
  • Stream result to disk
  • <repeat for each row>
  • Now ftp that file to magic-land

I'm trying to stop throwing everything into RAM. My thinking is that if I can stream stuff, it's more memory efficient as I only work on a single result set of data.

halfer
  • 19,824
  • 17
  • 99
  • 186
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • Take a look here: http://stackoverflow.com/questions/12697509/what-does-the-buffered-parameter-do-in-dapper-dot-net – Rob Dec 09 '15 at 02:18

1 Answers1

12

using Dapper's Query<T> method, which throws the entire result set into memory

It is a good job, then, that one of the optional parameters is a bool that lets you choose whether to buffer or not ;p

Just add , buffer: false to your existing call to Query<T>.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • how does this change the code that executes this `Query / QueryAsync`? If it's not buffering, then how do i pull data out and process it row by row? or is there another trick? – Pure.Krome Dec 10 '15 at 06:27
  • 1
    @Pure.Krome in both cases, you *just `foreach` over the data* - it is an iterator block over the open reader. Note: if you don't want to use `foreach`, then you simply use `GetEnumerator()`, `MoveNext()` (checking the return value) and `Current` (remembering to dispose the enumerator at the end); `foreach(var x in y) {...}` is basically: `using(var iter = y.GetEnumerator()) { while(iter.MoveNext()) { var x = iter.Current; ... } }` – Marc Gravell Dec 10 '15 at 07:40
  • What is the suggestion now that Query and QueryAsync don't have a simple signature where you can add buffer: false? – Steve Jan 24 '20 at 00:27
  • 6
    @steve in the next "major" we're going to smash the API so that there is a List-T version (buffered) and an IAsyncEnumerable-T version (non-buffered). Right now, you may need to use the version that takes the command wrapper rather than just a string. – Marc Gravell Jan 24 '20 at 08:20