How to merge list of plans without batching

[Risord]

Hi

I am creating SQL query which insert potentially thousands of rows. Issue is that there is max length of parameters in query: yiisoft/yii2#10371

If actions doesn't return anything you can just combine bathing and non batching for loops:

plan {
    let chunks =
        data
        |> List.chunkBySize 100
    
    for chunk in chunks do
        for item in batch chunk do
            do! doSomething item
}

On the other hand you need to return data so concurrentList should(?) be used:

Plan.concurrentList
    [ for d in data ->
        doSomething d
    ]

But this batches all the work and may be fail for too big query. So if there would be Plan.nonConcurrentList I could do something like this:

let chunks =
    spots
    |> List.chunkBySize 100

let plans =
    chunks
    |> List.map (fun chunk -> 
        Plan.concurrentList
            [ for d in chunk ->
                doSomething d
            ]
        )

Plan.nonConcurrentList plans

Any hints?

[rkosafo]

In this case, what is the output of Plan.nonConcurrentList?

[Risord]

I tough it could to be just equal to Plan.concurrentList : List<Plan<'a>> -> Plan<List<'a>>
Example from last post was a bit imprecise. Hopefully this is better:

let doAsChunks (doPlan : 'a -> Plan<'b>) (data : List<'a>) : Plan<List<'b>> =
    let chunks : List<List<'a>> =
        data
        |> List.chunkBySize 100
    
    let chunkedPlans : List<Plan<List<'b>>> =
        chunks
        |> List.map (fun chunk -> 
            Plan.concurrentList
                [ for d in chunk ->
                    doPlan d
                ]
            )
    
    chunkedPlans
    |> Plan.nonConcurrentList // List<Plan<'x>> -> Plan<List<'x>> just like Plan.concurrentList
    |> Plan.map (List.collect id) //Hopefully this won't mess anything?

So any tips how to implement Plan.nonConcurrentList?

Thanks!

[rkosafo]

Ok. After going through Plan's code, below is the sample I could come up with. From the console output, it seems to run sequentially. Do run the code to see if it is what you are looking for. Perhaps, @rspeele can confirm if this is a good approach.

 let test () =
  let doSomething x = Plan.ret x
  let spots = [ 1 .. 100 ]
  let chunks = spots |> List.chunkBySize 10
  let plans =
    chunks
    |> List.map (fun chunk -> 
        Plan.concurrentList
          [ for d in chunk -> doSomething d ])
  let rec nonConcurrent xs =
    match xs with
    | [] -> Plan.ret []
    | x :: xs' ->
      let run xOut =
        printfn "In run. xOut is: %A" xOut
        nonConcurrent xs' |> Plan.map (fun xsOut -> xOut :: xsOut)  
      Plan.bind x run
  nonConcurrent plans
  |> Plan.map (List.collect id)
  |> Execution.execute Execution.ExecutionConfig.Default
  |> fun x -> printfn "Result: %A" x.Result

[rspeele]

Hi,

The definition for nonConcurrent proposed by @rkosafo should work.

However, before you jump to using that, have you tested concurrentList with a large number of queries?

When it builds a query batch, Rezoom.SQL won't add a new query to the batch if it would cause the total number of parameters to exceed the limit for the SqlConnection implementation. So it should be fine to create huge batches with concurrentList -- they'll be transparently split into a series of smaller batches as needed. If not, it's a bug in Rezoom.SQL, not Rezoom. Now, this can't help with cases where > 2100 parameters are used in a single query (e.g. select * where Id in @parameterList with parameterList being huge), but you shouldn't have to worry when batching together many queries that are each within the limit.

[Risord]

Thanks @rkosafo it works perfectly!

@rspeele I have got error with large in-statements but also with inserts. Columns may vary but issue remains. All inserts are currently in this form:

    insert into T
    row
        A = @a,
        B = @b,
        C = @c;
    select scope_identity() as Id; //Table have also auto-increment primary key called 'Id'