Episode Details

Here’s the story behind that one button: a data science team trained a model, everything worked fine—until the dataset quietly doubled, and their R pipeline started crawling for hours. The problem wasn’t the algorithm, it was compute context: they were running in local compute, dragging every row out of SQL Server and across the network into laptop memory instead of pushing the script to run where the data lives. One switch to SQL compute context flipped the execution back into the server, kept data in place, and turned the crawl into a sprint—showing why “large data = SQL compute” is the rule of thumb for serious workloads.

THE INVISIBLE BOTTLENECK

Most teams blame slow pipelines on “bad code” or “complex models,” but the real drag often hides in an invisible bottleneck: where the compute actually happens. In local compute context, every row has to squeeze through your network and laptop RAM, so small test sets feel fine while real production data melts the clock. In this episode, we unpack how switching to SQL Server compute context keeps processing beside the data, why ETL into SQL is the prerequisite for real gains, and how to use a simple three‑step checklist (compute context, query shape, batch size) to find the true bottleneck before you waste weeks “optimizing” the wrong thing.

BATCH SIZE: POTION OF SPEED OR SLOWNESS

Once compute context is right, the next lever is batch size—your \rowsPerRead\\ setting—which behaves like a potion: dose it correctly and everything flies, misjudge it and performance staggers. We walk through how the default 50,000 rows can starve R when you scale to millions of rows, why wide tables and blob-heavy schemas demand smaller batches, and how to step-test from 50,000 to 500,000 to one million rows while watching runtime and memory usage. You’ll learn a practical tuning strategy that turns your pipeline from “constant waiting for the next chunk” into a steady flow where R stays busy without pushing SQL Server into paging.

THE QUERY THAT UNLOCKS PARALLEL WORLDS

The final performance unlock is query shape and parallelism: whether your SQL statement gives the optimizer enough structure to split work across multiple paths, or quietly forces everything through a single serial lane. Instead of blindly passing \table=\\ into \RxSqlServerData\\, we show how using \sqlQuery=\\ with a lean SELECT (no “SELECT *”, no junk columns R can’t handle) unlocks parallel plans, reduces memory waste, and cuts wall‑clock time without touching your R script. You’ll also hear how to use \@parallel = 1\\ in \sp_execute_external_script\\ or \numTasks\\ in RevoScaleR, why MAXDOP and resource governance still rule the final worker count, and how to validate your plan in Management Studio before you ever run the job through R.

WHAT YOU’LL LEARN

• When to choose local vs SQL Server compute context for R‑SQL pipelines.
• How compute context and data locality impact network I/O, memory, and runtime.

  Listen Now