AWS Architecture Blog
Reducing Text2SQL latency with parameterized query templates
If your Text2SQL system takes 25-30 seconds to respond, user engagement drops significantly. For teams scaling beyond pilot projects, this latency gap between a working demo and a production-ready tool is the biggest barrier to adoption. Without caching, every question triggers a Large Language Model (LLM) call to generate SQL, and those calls introduce challenges: unpredictable response times, throttling limits, and token costs that grow linearly with traffic. Parameterized query templates provide an intelligent caching layer that in our production deployment, reduced end-to-end latency by 80% and cut token consumption by over 50%, turning a slow prototype into a responsive production system. In this post, we walk through the architecture behind this approach, covering the implementation details, performance results, and lessons learned from running a Text2SQL system in production.
When you move AI applications from pilot to production, you need solutions that scale under real traffic and perform consistently. Traditional caching strategies, storing expensive computations once and serving them many times, don’t translate directly to generative AI. End users rarely phrase the same question the same way, context varies between sessions, and outputs depend on small input variations. Yet the underlying principle (caching) still holds value. Rather than abandoning caching entirely, the key is finding the right abstraction layer where similar requests can share cached results.
Solution overview
We applied the solution described in the following section to a system where business users query operational databases using natural language. You ask questions like “What were total sales in Q3?” or “Show me top performing products this month?” and the system generates SQL queries, executes them against the database, and returns results in conversational format. The system translates natural language to SQL using Amazon Bedrock foundation models, while AWS Lambda orchestrates the workflow. You can see a basic overview of used architectural components in Diagram 1.
Figure 1 — Solution overview architecture
During the initial implementation phase, the approach with generating and executing SQL queries for user questions on the fly worked well. Response quality was high, and users found the interface intuitive. After these positive results, we started looking into scaling the solution for production traffic. Preserving accuracy was the main priority. Experiments with smaller, faster models didn’t provide a good trade-off between query quality and latency reduction. The accuracy degradation wasn’t acceptable for our system.
This led us to explore alternative approaches, and caching naturally came to mind. Caching user question and answer pairs is the most straightforward option, but it has a fundamental limitation: underlying data changes constantly. An answer about Q3 sales cached today becomes incorrect as soon as new transactions are recorded. The cache would need constant invalidation, undermining its purpose.
Caching the SQL query instead solves this problem. A query like:
SELECT SUM(revenue) FROM sales WHERE quarter = 'Q3'
always fetches fresh data when executed, regardless of when it was cached. Structured Query Language (SQL) captures the user’s intent in a structured, deterministic form that remains valid even as data evolves. It also happens to target the most time and token consuming step in the pipeline, since generating SQL queries requires sending full schema context and examples to a frontier model.
Analyzing the generated queries revealed an opportunity to go further. Many queries follow the same structure, different only in their filter values. A question about Q3 sales produces:
SELECT SUM(revenue) FROM sales WHERE quarter = 'Q3'
while Q2 sales produce:
SELECT SUM(revenue) FROM sales WHERE quarter = 'Q2'
The same pattern appeared across product lookups, date ranges, and category filters. This led to the templating approach: instead of caching complete queries, we generalize them into templates with placeholders. A single template now covers an entire family of questions:
SELECT SUM(revenue) FROM sales WHERE quarter='{quarter}'
Figure 2 — Templated SQL query cache hit
Templating solves the limited reusability of plain user question, but still leaves a challenge: how do you match an incoming question to the right template when users phrase things differently? “Show me Q3 sales” and “What were sales in Q3?” ask for the same data but share few words. Traditional string matching or keyword lookup would miss these connections. We address this by storing each template alongside a vector embedding of its original question. When a new question arrives, we compute its embedding and perform semantic similarity search against the cache. Because embeddings capture meaning rather than surface wording, both phrasings map to the same template with high confidence. If a match is found above a confidence threshold, we extract entities from the question using lightweight named entity recognition, fill the template placeholders, and execute the query directly, bypassing the LLM entirely. In Diagram 2, you can see the flow of a cache hit.
For questions without matching templates, the system falls back to full LLM generation. It then generalizes the newly generated query into a template, pairs it with the question’s embedding, and adds it to the cache. This creates a self-improving system where cache coverage grows organically as more query patterns are encountered.
Walkthrough – Text2SQL pipeline
The following sections describe each step of the template caching pipeline. Each user’s question flows through entity extraction, template retrieval, and SQL query execution. Cache misses trigger full LLM generation, with new queries feeding back into the cache. The following diagram shows the complete flow of a user question through the newly introduced caching layer.
Figure 3 — Text2SQL pipeline with template caching layer
1. Entity extraction
After a user submits a question, the system performs entity recognition to extract named entities and values. This step considers not only the current question but also conversation history, current date, and user preferences. This context helps resolve ambiguous references like “last month” or “my region”. Using a lightweight model like Amazon Nova 2 Lite or a custom-trained named entity recognition (NER) model, we identify entities such as dates (“Q3 2024”), names (“Product X”), categories (“electronics”), and numeric values (“top 10”). The system stores these extracted entities separately and uses them later to fill out template placeholders.
2. Template retrieval through semantic search
The system converts the user’s question into an embedding vector using the same embedding model used during cache population. This vector queries the template cache through semantic similarity search, returning the closest matching templates above a confidence threshold. The search matches based on the question’s intent and structure rather than exact wording, so “What were Q3 sales?” and “Show me revenue for third quarter” both match the same template despite different phrasing.
It’s important to note that the confidence threshold governs the cache retrieval layer’s precision-recall trade-off. Set it too high and the system rejects valid, differently worded questions, forcing it to build SQL from scratch. Set it too low and loosely related templates slip through, risking confident answers built on the wrong query. The right value is domain-dependent: narrow, well-templated domains tolerate stricter thresholds, while broad or sparsely covered ones need looser ones.
Rather than relying on a single threshold, we suggest monitoring retrievals in production, logging matched templates and their similarity scores, so we can see when valid questions are being rejected or unrelated templates are slipping through. When embedding similarity alone doesn’t give enough precision, we added a lightweight reranking step: first we retrieve a broader set of candidate templates with a looser threshold, then re-score them with a small LLM or a specialized reranker model to select the best match. This improves precision without sacrificing recall and still costs far less than generating SQL from scratch.
3. Template filling and query execution
When a matching template is found, the system maps extracted entities to template placeholders. If the template contains `{quarter}` and entity recognition extracted “Q3”, the system replaces the placeholder with the actual value. The system validates the filled SQL query for syntax correctness, then executes it directly against the database. This path bypasses the time and token intensive LLM call that generates the SQL query.
This design helps the system to protect against SQL injection on two levels. First, it validates each extracted entity against the expected format for its placeholder: a `{quarter}` must match a known set of values, a `{date}` must parse as a valid date, a numeric threshold must be a number. The system rejects values that do not pass validation before they ever reach the query. Second, the system fills the placeholders using parameterized database queries (prepared statements) rather than string interpolation, so the parameterized query mechanism treats entity values as data rather than executable SQL. This approach also catches entity-extraction errors, improving answer reliability beyond the security benefit.
For richer responses, the system can retrieve multiple top-K similar templates and execute them in parallel. This provides additional context and related information beyond the primary query, for example returning both: quarterly sales totals and a breakdown by product category. The parallel execution adds minimal latency while delivering more comprehensive answers.
4. Response generation and validation
After executing the query, the system sends results to a response generation model. This model has two jobs, both handled in a single call: judge whether the results answer the question, and, if they do, summarize them into a conversational response.
The sufficiency check is driven by instructions in the prompt. The system instructs the model to confirm that the results are non-empty, that they contain the fields the question asked about, and that they cover every part of the question rather than only some of it. For example, if a user asks for “Q3 sales by region” but the matched template returns only a Q3 total, the results are incomplete, and the model is instructed to flag them as insufficient instead of answering with partial data. The model returns this judgment as a structured signal alongside its response, so the pipeline can branch on it deterministically. This step helps verify that users receive accurate answers rather than partial or misleading information from imperfect template matches.
This task is fundamentally simpler than SQL generation: instead of writing structured code from natural language, the model only needs to read tabular data and either summarize it or declare it insufficient. Because the task is simple, a smaller, faster model like Claude Haiku 4.5 can handle it effectively.
On a cache hit, there is only a single lightweight LLM call, which improves both latency and cost thanks to the smaller model. On a cache miss, the model flags the template results as insufficient and the system falls back to full SQL generation before producing the answer, for three calls in total: the sufficiency check, the SQL generation, and the response. That is one call more than the uncached pipeline, so misses carry extra latency. The trade-off is favorable because the added call is the cheap sufficiency check rather than another expensive generation, and because at a healthy hit rate the savings on hits outweigh the penalty on misses.
5. Fallback to full generation
If no template matches the confidence threshold, or if the validation step determines that cached results are insufficient, the system falls back to the standard Text2SQL pipeline. The question, along with the full context, goes to the foundation model for SQL generation. The generated query executes against the database, and results return to the user. Importantly, this newly generated query doesn’t disappear. It enters the reinforcement loop.
6. Reinforcement loop for cache growth
After a successful fallback generation, the system evaluates whether the new query should join the template cache. If the query executed successfully and returned valid results, it becomes a candidate for templating. The system generalizes the query by replacing specific values with placeholders and computes the original question’s embedding. It then adds this new template-question pair to the vector store, expanding cache coverage. Over time, the cache grows organically to cover query patterns specific to your users’ actual needs.
Results and performance gains
The figures in this section come from our production deployment but treat them as an illustrative model rather than a fixed benchmark. Exact token counts and latencies depend on your schema size, prompt design, model choice, and query mix. What generalizes is the direction of the improvement, not the specific numbers.
The dominant cost and latency in a Text2SQL request come from a single step: generating the SQL query. That call sends the user question, conversation history, the database schema, few-shot examples, and domain guidance to a powerful LLM such as Anthropic Claude Sonnet, which is needed to produce reliable queries. In our deployment this prompt runs on the order of 60K input tokens for a few hundred output tokens, and takes roughly 15-20 seconds. Every other step: embedding, vector search, template filling, and query execution, is minor by comparison. Entity recognition, runs on a dedicated NER model hosted on Amazon SageMaker AI rather than an LLM, adding negligible cost and latency next to SQL generation. Optimizing the pipeline is therefore mostly about avoiding that one expensive call.
On a cache hit, the system skips SQL generation entirely. What remains is response summarization, turning the query results into a conversational answer, which runs on a small model with a small prompt (on the order of a couple thousand input tokens). Because summarization is needed on both, the cached and uncached paths, a cache hit does not remove tokens completely, but it eliminates the 60K-token generation call, cutting token consumption by roughly 90% on that request.
This 90% is the saving on a single cache hit. Overall cost depends on the average across all requests, since cache misses still incur the full generation cost. At the roughly 60% hit rate we observed in production, the blended reduction across all traffic comes out above 50%. Latency follows the same pattern. An uncached request spends 15-20 seconds on the SQL call, retries and error handling included, then a few more seconds on summarization, putting a typical request in the 25-30 second range. On a cache hit, retrieval, template filling, and execution finish well under a second, and the remaining time is almost entirely the summarization call. That brings the end-to-end cache-hit path under 5 seconds, roughly an 80% reduction, or about 6x faster. It also pinpoints where the residual latency comes from: not the cache lookup, but the one LLM call that still has to run.
These per-request gains only matter if cache hits are common. In our production system the hit rate reached about 60% after roughly two weeks of active use, though the achievable rate depends heavily on the domain and how repetitive the queries are. Cache misses run the full pipeline plus the small sufficiency check, so they cost marginally more than a purely uncached request, which means the net gain comes entirely from hits. As the reinforcement loop keeps adding templates, the hit rate climbs and both the cost and latency benefits continue to compound.
Conclusion
Scaling AI applications to production often requires rethinking traditional optimization strategies. In this post, we demonstrated how template-based caching addresses the latency and cost challenges of Text2SQL systems without sacrificing accuracy. By caching SQL query structures rather than complete responses and using semantic similarity to match user questions to templates, the system can bypass expensive LLM inference calls. The reinforcement loop ensures cache coverage grows organically based on actual usage patterns.
In practice, this means: 6x faster response times on cache hits, inference costs decrease proportionally to your cache hit rate, and accuracy remains high because templates are generated by the most capable models. The patterns we covered, such as semantic matching, output generalization, entity extraction, and continuous improvement loops, extend beyond Text2SQL to any AI system where similar requests should produce structurally similar outputs.
Further reading
Generating value from enterprise data: Best practices for Text2SQL and generative AI
Text-to-SQL solution powered by Amazon Bedrock
Amazon S3 Vectors: First cloud storage with native vector support at scale