title: What would be the best way to improve calcul performance in a big data.table?
body: <p>In a single <code>data.table</code>, I have many calculs to perform. Simple, but combining many configurations : creating X variables from Y others, making groups based on X different variables, etc...</p>
  <p>Step by step, I manage to perform all the calculations I need (with my knowledge in data.table), but my real challenge is called PERFORMANCE. My <code>data.table</code> contains millions of lines, and the calculations are made on dozens and dozens of columns.</p>
  <p>What I would like to know :</p>
  <ul>
  <li>Is there a better way to write this code to improve performance ?</li>
  <li>Some of my options do not work (1.3 and 2.2, tag with KO) : good approach ? How to write it ?</li>
  <li>My <code>microbenchmark</code> seems to show me that the best option depends on the number of lines ? Right ?</li>
  </ul>
  <p>Here is my code with a reprex :</p>
  <pre><code>library(data.table)
  library(stringr)
  library(microbenchmark)
  
  n.row &lt;- 1e5
  
  foo &lt;- data.table(id = 101:(101+n.row-1), 
                    crit = rep(c('fr', 'ca', 'al', 'se', 'is'), 5),
                    val_1 = round(runif(n.row, 0.5, 50), digits = 2),
                    val_2 = round(runif(n.row, 1, 20), digits = 0),
                    val_3 = round(runif(n.row, 1, 5), digits = 0),
                    eff = 28500,
                    num = sample(0:1,n.row, replace = TRUE),
                    num_2 = round(runif(n.row, 1, 10), digits = 1),
                    num_17 = round(runif(n.row, 1, 10), digits = 1),
                    num_69 = round(runif(n.row, 0, 1), digits = 2),
                    num_5 = round(runif(n.row, 10, 20), digits = 0),
                    cof = round(runif(n.row, 0.1, 2), digits = 5),
                    ToDo = rep(1, n.row),
                    grp_1 = sample(LETTERS[c(1,3)], n.row, replace = TRUE))
  
  
  foo[, c(&quot;grp_2&quot;, &quot;grp_3&quot;) := {
    grp_2 = fcase(grp_1 %in% LETTERS[c(1)], sample(LETTERS[c(5,8,9)], n.row, replace = TRUE),
                  grp_1 %in% LETTERS[c(3)], sample(LETTERS[c(14,16)], n.row, replace = TRUE))
    
    grp_3 = fcase(grp_1 %in% LETTERS[c(1)], sample(LETTERS[c(20:23)], n.row, replace = TRUE),
                  grp_1 %in% LETTERS[c(3)], sample(LETTERS[c(24:26)], n.row, replace = TRUE))
    
    list(grp_2, grp_3)
  }]
  
  # Calcul sd and qa
  foo[, sd := (val_1 * cof)]
  foo[num == 1, qa := (val_2 * cof)]
  foo[num != 1, qa := (val_3 * cof)]
  
  foo1 &lt;- copy(foo)
  foo2 &lt;- copy(foo)
  foo3 &lt;- copy(foo)
  
  # calcul of qa_X 
  var.calc &lt;- names(foo)[str_which(names(foo), &quot;^num.\\d+$&quot;)]
  
  # 1.1
  for (j in var.calc){
    foo1[, paste0(&quot;qa_&quot;, str_extract(j, &quot;\\d+$&quot;)) := qa * get(j)]
  }
  
  # 1.2
  setDT(foo2)[, paste0(&quot;qa_&quot;, str_extract(var.calc, &quot;\\d+$&quot;)) := lapply(.SD, function(x) x * qa), .SDcols = var.calc ]
  
  # 1.3 KO
  for (j in var.calc){ set(foo3, paste0(&quot;qa_&quot;, str_extract(j, &quot;\\d+$&quot;)) := qa * get(j)) }
  
  # comparaison
  mbm &lt;- microbenchmark(
    Test.for = for (j in var.calc){ foo1[, paste0(&quot;qa_&quot;, str_extract(j, &quot;\\d+$&quot;)) := qa * get(j)] },
    Test.set = setDT(foo2)[, paste0(&quot;qa_&quot;, str_extract(var.calc, &quot;\\d+$&quot;)) := lapply(.SD, function(x) x * qa), .SDcols = var.calc ],
    times = 10
  )
  
  mbm
  
  # calcul by groups
  var.grp &lt;- names(foo)[grepl(&quot;^grp.\\d+$&quot;, names(foo))]
  
  # 2.1
  for (j in var.grp) {
    foo1[, paste0(&quot;s.sd.&quot;, j) := sum(sd, na.rm = TRUE), by = get(j)]
    foo1[, paste0(&quot;s.qa.&quot;, j) := sum(qa, na.rm = TRUE), by = get(j)]
  }
  
  # 2.2 KO 
  setDT(foo2)[, paste0(&quot;s.sd.&quot;, var.grp) := lapply(.SD, function(x) sum(x)), .SDcols = var.calc, by = .SD ]
  </code></pre>
  <p>Many thanks for helping or suggestions.</p>
  <p>(If I have to split my request, I will).</p>