Reading a large Oracle table with SqlDataProvider

Question

I have an Oracle table with 1.5 million rows of data in it that I need to process; however I keep getting an Out Of Memory exception. A minimal code example showing the issue is:

module MyModule

open System
open FSharp.Data.Sql

type SQL = SqlDataProvider<ConnectionString = myConnectionString, DatabaseVendor = Common.DatabaseProviderTypes.ORACLE>

let getdata() =
    let ctx = SQL.GetDataContext(SelectOperations.DatabaseSide)
    let qdata= query {
        for somerow in ctx.myschema.sometable do
        select (somerow)
    }
    qdata |> Seq.take 10 |> Seq.iter (printfn "%A")  // Fails with both the Seq.take and without

Is it possible to make the SqlDataProvider provide the rows in manageable chunks?

Answer 1

The Seq.take that you're calling here is already executing over materialized result - it's not part of the SQL query generator - it's pretty much like calling AsEnumerable() on IQueryable<> object. All SQL that will be constructed is based on what happens inside query { ... } brackets.

If you read the docs about F# SqlProvider, you'll see that there are special expressions, that can be used within the query statement:

let qdata = query {
  for somerow in ctx.myschema.sometable do
  sortBy (somerow.column)
  skip 30
  take 10
  select (somerow)
}