Using Nessos.Stream to reduce the time to get and write query output to a `.CSV`

Question

In the past, I have written queries that just get the entirety of the query, store the results in memory, then feed the whote sequence to a .CSV type provider. A query example:

let results = 
    query {
        for row in db.ThisRow do 
        select row
        }
        |> Seq.toList

As I understand it, the Seq.toList part forces the query to run while keeping the output from the query itself as a Seq would be lazy. If the number of the query results are small, this isn't a big deal. If, however, the number of results is large (e.g., >1Mil rows), I end up with a System.OutOfMemoryException. As a result, a friend suggested in look into using the Nessos.Stream library.

My goal is to pull a row from the query, do some operations to that row, then write that single row to a .CSV and do that iteratively for each row with all rows ultimately in the same .CSV file.

So, I try

open Nessos.Stream

type StringInt = {
    String: string option
    Int: int
    }

type StringIntCsvType = CsvProvider<Sample = "item_number, num", 
                                    Schema = "item_number (string option), num (int)", 
                                    HasHeaders = true>
let buildRowFromObject (obj: StringInt) = StringIntCsvType.Row(obj.String, 
                                                               obj.Int)

let results = 
    query {
        for row in db.ThisRow do 
        select row
        }
        |> Stream.ofSeq
        |> Stream.groupBy (fun row -> row.ITEMNUMBER)
        |> Stream.map (fun (itemString, seq) -> (itemString, (seq |> Seq.length)))
        |> Stream.map (fun (str, num) -> {String = Some str; 
                                          Int = num})
        |> Stream.map buildRowFromObject
        |> Stream.toSeq

let ThisCsv= new StringIntCsvType(results)
let ThisCsvLoc = "pathToFileLocation"
let ThisCsv.Save(ThisCsvLoc)

Although this works inasmuch as I'm getting all of the rows I've queried from the database, this still isn't doing what I want it do and I'm not sure how to make that happen. Also, for larger queries, I still end up with an System.OutOfMemoryException.

I think I should be able to insert

|> Stream.map (fun x -> new StringIntCsvType(x))

under the |> Stream.map buildRowFromObject line, but the x is errored out with

Type Constraint Mismatch.  The type
    CsvProvider<...>.Row
is not compatible with type
    Collections.Generic.IEnumerable<CsvProvider<...>.Row>

Even if I wasn't given this error, I think adding that line would create a new .CSV for every row, and that is decidedly NOT want I want.

How I can write a query, operate on each different piece of the query, and write each row of the query into the same .CSV file? Am I even close with the code above?

Answer 1

You don't necesary need the intermediate StringInt record type. And you already have your CSV sequence from buildRowFromObject, you can directly write that to a file. While I'm also a big fan of Nessos Streams I'm not sure it adds that much value for you here (I might be wrong and maybe you have some more complicated map/reduce operations in the pipeline). In this case, before exploring other solutions, I would go for the simple one first.

First, make sure your FSI is set to 64-bit and you're also compiling to 64-bit in the exe. Second, as @krontogiannis comments, your operation appears to be counting items in some group. I don't think you want to actually iterate through each group so why not do the Count on the DB side. You will get back a sequence of tuples (I hope), where you have some ID and the countumber. You can feed that directly to the CSVTypeprovider. So:

type StringIntCsvType = CsvProvider<Sample = "item_number, num", 
                                    Schema = "item_number (string), num (int)", 
                                    HasHeaders = true>

let buildRowFromObject (row:string * int) = StringIntCsvType.Row(row)

let qry = query {
            for row in tbl1 do
            groupBy row.ItemNumber into g
            select (g.Key,g.Count())
            }

let csvout = qry |> Seq.map  buildRowFromObject
(new StringIntCsvType(csvout)).Save(@"C:\tmp\test.csv")

This writes about 7 million rows in 9 secs into a ca. 100MB csv file.

In a more complex applications you might factor out the writing to file into something like this:

let writeFile csvout (path:string) =
    use csvtype = new  StringIntCsvType(csvout)
    csvtype.Save(path)
writeFile csvout @"C:\tmp\test2.csv"

There is also a comment in the manual about working with a large number of rows, not sure how relevant it is for you but you can set the CacheRows=false when instantiating the CsvProvider.

Edit

Some if this might not be relevant but here it is:

#load @"..\..\FSLAB\packages\FsLab\FsLab.fsx"

#r "System.Data.dll"
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"
#r @"..\packages\Streams.0.4.1\lib\net45\Streams.dll"
#r @"..\packages\FileHelpers.3.1.5\lib\net45\FileHelpers.dll"

open System
open System.Diagnostics
open System.IO
open System.Collections
open System.Collections.Generic
open System.IO.Compression

open System.Data
open System.Data.Linq
open System.Linq
open Microsoft.FSharp.Data.TypeProviders
open FSharp.Linq
open FSharp.Data
open Nessos.Streams
open FileHelpers

[<Literal>]
let connectionString2 = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\...\Documents\test.mdf;Connection Timeout = 60"

type dbSchema = SqlDataConnection<connectionString2,StoredProcedures = true>


type StringIntCsvType = CsvProvider<Sample = "item_number, num", 
                                    Schema = "item_number (string), num (int)", 
                                    HasHeaders = true,CacheRows=false>



let getDbx() = 
    let dbx = dbSchema.GetDataContext()
    dbx.DataContext.ObjectTrackingEnabled <- false  // could impact memory consumption but so far has little effect on time
    dbx.DataContext.CommandTimeout <- 90
    dbx

let dbx = getDbx()

let tbl1 = dbx.MyTable