copy_dm_to() generates unchunked INSERT … VALUES on MSSQL since 1.1.0

[Layalchristine24]

Summary

copy_dm_to() hangs against SQL Server (FreeTDS) in dm 1.1.0+ when used as a staging step before dm_rows_append(). Same pipeline completes on dm 1.0.4 (slowly but reliably).

Likely cause: the 1.1.0 change to have copy_dm_to() create key constraints on the database (#1887, #2022). Our destination tables already have PK/UK/FK constraints defined, so the auto-setup probably races on SQL Server metadata locks.

Versions

dm version	result
1.0.4	works (slow)
1.1.1	hangs
1.1.2	hangs

(1.1.0 not retested. 1.1.1 was only an igraph-fallback fix on top of 1.1.0, so 1.1.0 is the suspect.)

Environment

• R 4.6.0, macOS x86_64
• FreeTDS ODBC driver, Microsoft SQL Server
• Destination tables have clustered compound PKs, FKs with ON DELETE CASCADE, some with NONCLUSTERED INDEXes

Pipeline

append_data <- function(con, dm_table, data) {
  my_dm <- dm::dm_from_con(con)
  table_name <- as.name(dm_table)
  staged <- dm::copy_dm_to(con, dm::dm(!!table_name := data))  # hangs here on 1.1.x
  dm::dm_rows_append(my_dm, staged, in_place = TRUE)
}

Called for three tables (~150k, ~137k, ~83k rows) sequentially. On 1.0.4 the whole sequence completes in ~14 min. On 1.1.x, copy_dm_to START is logged, then nothing.

Concrete log:

[10:13:16] Appending 3 dm table(s): table1 (149423 rows), table2 (136941 rows), table3 (82762 rows)
[10:13:16] append_data START: table1 (149423 rows)
[10:13:17]   copy_dm_to START: uploading 149423 rows...
[10:19:17]   copy_dm_to done (359.7s)
[10:19:17]   dm_rows_append START...
[10:19:30]   dm_rows_append done (12.8s)
[10:19:30] append_data START: table2 (136941 rows)
[10:19:31]   copy_dm_to START: uploading 136941 rows...
[10:24:31]   copy_dm_to done (299.3s)
[10:24:31]   dm_rows_append START...
[10:25:19]   dm_rows_append done (47.9s)
[10:25:19] append_data START: table3 (82762 rows)
[10:25:20]   copy_dm_to START: uploading 82762 rows...
[10:26:32]   copy_dm_to done (71.6s)
[10:26:32]   dm_rows_append START...
[10:26:52]   dm_rows_append done (20.3s)

Workaround

pak::pak("cynkra/dm@v1.0.4")
# or: remotes::install_version("dm", version = "1.0.4")

Question

Is there a way (in 1.1.x or planned) to skip constraint creation in copy_dm_to() when the destination already exists with the right schema? Our destination table already has all its constraints defined (via DDL elsewhere). copy_dm_to() only needs to stage the rows in a throwaway temp table so dm_rows_append() can copy them into the real destination. Setting up constraints during the staging step isn't needed in this use case.

Happy to gather a fuller trace if useful.

Thank you!

[Layalchristine24]

After diffing R/db-interface.R between v1.0.4 and v1.1.2, the most consequential change in copy_dm_to() isn't the new constraint creation — it's that the data-loading phase no longer routes through db_append_table().

In 1.0.4 the populate step was:

pwalk(
  queries[c("name", "remote_name")],
  ticker_populate(~ db_append_table(dest_con, .y, dm[[.x]], progress))
)

…and db_append_table() has an is_mssql() branch that does explicit chunked inserts (chunk_size <- 1000L, walk over chunks, sqlAppendTable per chunk).

In 1.1.2 the populate step is:

walk(load, ~ DBI::dbExecute(dest_con, .x, immediate = TRUE))

…where load <- unlist(sql$load) comes from dm_sql(). db_append_table() isn't called from copy_dm_to() at all anymore, so its MSSQL chunking logic doesn't apply to the new pipeline.

So the question moves to: what does dm_sql()$load look like for SQL Server, for ~150k rows? If it's one statement, FreeTDS will choke. If it's many tiny statements, throughput collapses vs the 1000-row batches the old code used.

set_key_constraints = FALSE may help, but for a single-table staging dm with no keys (our use case via dm_rows_append), zeroing the keys is already a no-op — so the workaround might not address what's actually slow. We'll capture dm_sql()$load output and report back.

_{Diff analysis assisted by Claude Code.}

[Layalchristine24]

Captured dm_sql()$load on dm against SQL Server (FreeTDS) for the dm_rows_append staging pattern (single-table key-less dm).

5-row test: one statement, looks like

INSERT INTO "#t_1_..." ("a", "b")
SELECT
  TRY_CAST(TRY_CAST("a" AS NUMERIC) AS INT) AS "a",
  TRY_CAST("b" AS VARCHAR(MAX)) AS "b"
FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')) AS drvd("a", "b")

2,500-row test:

> length(sql_out$load)
[1] 1
> nchar(sql_out$load$t[[1]])
[1] 41578

Still one statement, with all 2,500 rows inlined as a SQL Server table value constructor. The SQL text alone is ~41 KB. Linearly extrapolated to the 149k-row table that triggered the original report, that's ~2.5 MB of SQL text in a single statement.

That's the regression: dm_sql() does not chunk the data for SQL Server. In 1.0.4 the populate phase routed through db_append_table() → DBI::sqlAppendTable() with chunk_size <- 1000L, producing ~150 parameterised INSERT … VALUES (?, ?) statements with ODBC binding the values separately. That completed our 149k-row, 3-table sequence in ~14 min wall time on FreeTDS.

In 1.1.2, the same pipeline tries to push one ~2.5 MB inline-literals SQL string through FreeTDS in one shot, with SQL Server having to parse it before executing anything. Independent of that, SQL Server documents a 1,000-row limit on VALUES / TVCs in INSERT context, which the 2,500-row test already exceeds in principle.

Notes on the other 1.1.0 changes:

• $post$uk, $post$fk, $post$indexes are all empty named lists for a single-table key-less staging dm. So set_key_constraints = FALSE is a no-op for this use case — the regression is not in constraint creation.
• The if (is_mssql(dest)) temporary <- FALSE override at R/db-interface.R~145 means the #-prefixed temp-table name (generated with temporary = TRUE) is then used by the rest of the pipeline as if it were a permanent table. Probably not the immediate cause of the hang, but worth a separate look.

Probable fix direction: restore an MSSQL-specific chunked path for the load phase, either by routing through db_append_table() again or by having dm_sql() emit multiple chunked INSERT statements (≤1,000 rows per TVC) for MSSQL backends.

_{Diagnostic capture assisted by Claude Code.}