Clever Geek Handbook

FSharp.Data.SqlProvider is slow

I have a simple DB with 4 tables. The table Results

has 18 columns. 3 of them are foreign keys. I am trying to get the number of all results (about 800k) with this code:

#I @"..\packages\SQLProvider.1.1.3\lib"
#r "FSharp.Data.SqlProvider.dll" 
open FSharp.Data.Sql


let [<Literal>] ConnectionStringmdf = @"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=C:\Users\Me\Desktop\myDb.mdf;Integrated Security=True;Connect Timeout=10"    

type Sqlmdf = SqlDataProvider< 
              ConnectionString =   ConnectionStringmdf,
              DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER,
              IndividualsAmount = 1000,
              UseOptionTypes = true, 
              CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL
              >
let dbm = Sqlmdf.GetDataContext()

printfn "Results count:\t %i" (dbm.Dbo.Results |>  Seq.length )

It takes about 40 seconds to get the number of records in one table.

Why is it so slow? What am I doing wrong?

+3


source to share


2 answers


The types returned by SqlDataProvider implement IQueryable, which means you can write a query expression or use Queryable.Count 

open System.Linq

dbm.Dbo.Results |> Queryable.Count


or

query { for it in dbm.Dbo.Results do
        count
      }
+4


source


You just have to query the table directly and return the result to the server. For example, I get 8M row count:

type dbSchema = SqlDataConnection<connectionString1>

let dbx = dbSchema.GetDataContext()
dbx.DataContext.ObjectTrackingEnabled <- false
dbx.DataContext.CommandTimeout <- 60
let table1 = dbx.MyTable

table1.Count()
//val it : int = 7189765

You can also include it in your request.



Here's a version of the query that (unless sqlprovider does the invoice) should work with a different TP as well. Again, the speed is almost instantaneous.

query { for row in table1 do 
        select row
        count
        }

I've tested the same SqlDataProvider

with similar results. Open the namespace System.Linq

to access the extension function .Count()

, if needed.

+3


source






More articles:


All Articles