Slow .SD[.N] compare to last(.SD) with groupby

[matthewgson]

I noticed there is significant speed difference between
DT[,.SD[.N], by=col] and
DT[,.last(.SD), by=col]

for relatively large data (10 * 1.8M rows).

DT[,.SD[1],by=col] and DT[,first(.SD), by=col] did not show difference in performance, however.

[shrektan]

I don't know the root reason why uses a subset of .SD works a lot slower, but "FAQ 2.1 How can I avoid writing a really long j expression? You’ve said that I should use the column names, but I’ve got a lot of columns" doesn't recommend use a subset of .SD:

So please don’t do, for example, DT[ , sum(.SD[["sales"]]), by = grp]. That works but is inefficient and inelegant. DT[ , sum(sales), by = grp] is what was intended, and it could be 100s of times faster. If you use all of the data in .SD for each group (such as in DT[ , lapply(.SD, sum), by = grp]) then that’s very good usage of .SD. If you’re using several but not all of the columns, you can combine .SD with .SDcols; see ?data.table.

[MichaelChirico]

I'm not sure I agree w that FAQ... in this case I think we have GForce optimization for .SD[1]. i thought we have it for .SD[.N] too...

…

On Mon, Nov 16, 2020, 9:30 PM Xianying Tan ***@***.***> wrote: I don't know the root reason why uses a subset of .SD works a lot slower, but "FAQ 2.1 How can I avoid writing a really long j expression? You’ve said that I should use the column names, but I’ve got a lot of columns <https://cran.r-project.org/web/packages/data.table/vignettes/datatable-faq.html#how-can-i-avoid-writing-a-really-long-j-expression-youve-said-that-i-should-use-the-column-names-but-ive-got-a-lot-of-columns.>" doesn't recommend use a subset of .SD: So please don’t do, for example, DT[ , sum(.SD[["sales"]]), by = grp]. That works but is inefficient and inelegant. DT[ , sum(sales), by = grp] is what was intended, and it could be 100s of times faster. If you use all of the data in .SD for each group (such as in DT[ , lapply(.SD, sum), by = grp]) then that’s very good usage of .SD. If you’re using several but not all of the columns, you can combine .SD with .SDcols; see ?data.table. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#4809 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB2BA5N2TKRTEVPIHT67F2DSQHN37ANCNFSM4TX2XOZQ> .

[ColeMiller1]

See also #735.

I just do not think we have optimized a name in .SD[...]. While .N is safe, I'm not sure other names would be safe.

Related, what is the system information @matthewgson ? I know 1.12.8 was slow due to greedy threads but I thought Jan's fixes significantly helped in 1.13.0.

[matthewgson]

@ColeMiller1
My R version is 4.0.2 on MacOS and currently using data.table version 1.13.2.
My apologies that I wasn't able to include replicable data.

$platform
[1] "x86_64-apple-darwin17.0"

$arch
[1] "x86_64"

$os
[1] "darwin17.0"

$system
[1] "x86_64, darwin17.0"

$version.string
[1] "R version 4.0.2 (2020-06-22)"

$nickname
[1] "Taking Off Again"

packageVersion("data.table")
[1] ‘1.13.2’

[jangorecki]

What is missing in this report is verbose output which would give clear info if gforce is being used. AFAIR it should have been working for .SD[.N].

[MichaelChirico]

Jan it doesn't (I thought so too) -- it gets lapply optimized to list(.SD[[1]][.N], ...) but g[(x, i) only works for fixed i (like x[1] or x[2]). see the PR

…

On Tue, Nov 17, 2020, 2:52 AM Jan Gorecki ***@***.***> wrote: What is missing in this report is verbose output which would give clear info if gforce is being used. AFAIR it should have been working for .SD[.N]. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#4809 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB2BA5OMWJBIOMZPTQMKLMTSQITULANCNFSM4TX2XOZQ> .