Stop Compiling SQL at Runtime: How Design-Time Compilation Saves Time & Costs
Dirk VermeirenCTO
Introduction
Is your ELT tool compiling SQL statements every time they run? If so, you’re wasting valuable time, compute resources, and cloud consumption credits. Many ELT tools rely on runtime compilation, dynamically generating SQL just before execution. While this offers flexibility, it also introduces unnecessary performance overhead, slowing down processes and increasing costs.
VaultSpeed takes a design-time compilation approach. SQL is pre-compiled before execution, eliminating unnecessary processing and ensuring ELT jobs run as efficiently as possible. The impact? Faster execution, lower resource consumption, and reduced costs.
The hidden cost of runtime compilation
Design-time compilation pulls code compilation forward to the design phase, happening only 1–30 times per year when the environment changes (new source release, version update, etc.).
Runtime compilation recompiles SQL every execution at runtime, which can range from 365 times per year (daily runs) to 500,000+ times per year (near real-time runs). The same wasteful process is repeated every time:
Compile transformation logic
Generate SQL dynamically
Optimize and compile SQL before execution
Finally, execute the SQL within the data platform (Snowflake, Databricks, Microsoft Fabric, Google Bigquery, Redshift,…)
The image above compares Design-Time Compilation (bottom) vs. Runtime Compilation.
The actual compute happens on the data platform anyway via query pushdown, so why waste resources compiling the same SQL over and over again? With design-time compilation, VaultSpeed generates SQL procedures before execution and stores them in the database. This means they execute instantly, without the burden of repeated compilation. Runtime variables are instantiated within the data platform.
How much faster is pre-compiled SQL?
Internal testing and customer benchmarks show that pre-compiled SQL eliminates the overhead of runtime compilation, leading to:
Near-instant execution since the SQL is already optimized and ready to run
Lower compute costs by avoiding redundant processing
Greater scalability for large-scale ELT workloads
Even though the per-query difference might be just a couple of seconds, for enterprises handling massive data volumes and frequent ELT jobs, these inefficiencies add up quickly. Avoiding them means cutting processing times by several hours, depending on the scale.
How much cheaper is pre-compiled SQL?
Most runtime compilation ELT platforms charge per executionsince each run incurs a compilation cost. This means that the more frequently your transformations run, the higher your costs scale over time—even though the transformation logic itself hasn’t changed. VaultSpeed takes a different approach:
You only pay for code compilation at design time—not for runtime execution. Once compiled, your transformation logic runsas many times as needed without incurring additional compilation costs. This results in lower cloud spend, predictable costs, and better scalability, especially for organizations running many and frequent ELT jobs.
Conclusion
Switching to pre-compiled SQLwith VaultSpeed eliminates overhead, speeds up data processing, and lowers costs—giving you faster insights at a reduced cloud spend.
So, are you still compiling SQL at runtime? If so, it’s time to rethink your approach.
