String Concatenation Optimization F #

Question

I am creating a MySql query that a package inserts 4096 records at a time. The actual insert is pretty fast, but a bottleneck generates a request. Any tips on optimizing this? String generation currently takes about 18 times longer than a query.

let builder = StringBuilder(524288) Printf.bprintf builder " INSERT INTO %s (`ID`, `Tag`, `Port`, `Excess`, `Return`, `StartDate`, `EndDate` ) values " x.evaluationstable evaluations |> Seq.iter(fun (e) -> Printf.bprintf builder " (%d, '%s', '%s', %A, %A, %A, %A), " e.ID e.Tag e.Port e.Excess e.Return (e.StartDate.ToString(x.datetimeformat)) (e.EndDate.ToString(x.datetimeformat)) ) 

Answer 1

Try using StringBuilder.AppendFormat instead of Printf.bprintf . When I made this change in my example of your question, I saw a huge increase in performance (~ 80x).

 evaluations |> Seq.iter (fun (e) -> builder.AppendFormat( " ({0}, '{1}', '{2}', {3}, {4}, {5}, {6}), ", e.ID, e.Tag, e.Port, e.Excess, e.Return, (e.StartDate.ToString("MM/dd/yyyy")), (e.EndDate.ToString("MM/dd/yyyy")) ) |> ignore ) 

Answer 2

I would try to avoid embedding data directly in SQL for starters. Use a sequence of prepared statements with parameters and set these parameters for values ​​(without formatting them). It is safer and likely to be much more effective.

If you can still do this in a batch, and not in several separate calls within just one transaction, I'm not sure.