4

I am using the below code to insert to a Postgres DB using tokio-postgres, is there any better option :

let members = &[obj] //obj is a struct
let mut params = Vec::<&(dyn ToSql + Sync)>::new();
let mut i = 1;
let mut qry:String = "insert into tablename(id,userid,usertype) values".to_string();
for column in members{
    if(i ==1){
        qry = format!("{} (${},${},${})",qry,i,i+1,i+2);
    }else{
        qry = format!("{}, (${},${},${})",qry,i,i+1,i+2);

    }
    params.push(&column.id);
    params.push(&column.userid);
    params.push(&column.usertype);
    i = i+3;
               
}
println!("qry : {}",qry);
let result = p.execute(&qry, &params[..]).await; //p is the pool manager
M.Nair
  • 243
  • 2
  • 9
  • I'm not sure whether or not `format!()` is smart enough to steal the value of the first argument if it's movable. At a minimum, I'd consider using concatenation instead of rebuilding the whole string each iteration. – cdhowie Mar 30 '22 at 23:43
  • Thanks I think format is doing String concatenation by creating a new string and leaving the original String untouched. But since I assign the return from format to the initial variable the original String memory should get released. Else I need to do String push, will check on that as well. Thanks for the suggestion. – M.Nair Mar 31 '22 at 16:37
  • 1
    Right, the original string is dropped, which is the wasteful part (along with copying the discarded string into a new string). There's a lot of allocation/deallocation and copying for no benefit over just concatenating onto your existing string. Note also that `String` implements [`std::fmt::Write`](https://doc.rust-lang.org/std/fmt/trait.Write.html), so you should be able to use [`write!`](https://doc.rust-lang.org/std/macro.write.html) on the string to append a format to it. For example: `write!(&mut qry, " (${})", i)?` – cdhowie Mar 31 '22 at 19:13
  • Ok great yes that's a good point. that will definitely speed things up. Will try that. – M.Nair Mar 31 '22 at 20:55
  • as @cdhowie suggested changed from format! to ```let mut buf = String::new(); buf.write_fmt(format_args!("(${},${},${})",i,i+1,i+2));``` – M.Nair Mar 31 '22 at 21:16

1 Answers1

7

No:

You can marginally improve it by using iterators:

use itertools::Itertools; // For tuples() and format_with()

let params: Vec<_> = members
    .iter()
    .flat_map(|row| [&row.id as &(dyn ToSql + Sync), &row.userid, &row.usertype])
    .collect();
let query = format!(
    "insert into tablename(id, userid, usertype) values {}",
    (0..params.len())
        .tuples()
        .format_with(", ", |(i, j, k), f| {
            f(&format_args!("(${i}, ${j}, ${k})"))
        }),
);

However I don't really think that's better.

Chayim Friedman
  • 47,971
  • 5
  • 48
  • 77
  • This is a fine approach and can insert a few 100k rows per second, but one should probably limit the amount of total query parameters to something sane. The actual limit may be 32767, but from my experience, the row throughput difference between inserting a few hundred rows and a few thousand at once is low. So for large arrays, I'd prepare one query with e.g. 512 rows and another for the tail. – Caesar Apr 01 '22 at 10:46