not very fast for large sql

[veryrandomname]

IN List Parse Benchmark - v0.1.7

fyi, the current tokenizer/parser are slow b/c they copy strings again and again.
this is by no way an expecation for you to fix this. just calling it out here.

Size: 1000000 items
Warmup: 1 iteration(s)
Iterations: 10
SQL size: 9888917 bytes
Summary:
Mean: 647.414 ms
Min: 627.917 ms
Max: 660.274 ms
Benchmark code:

use std::hint::black_box;
use std::time::Instant;
fn main() {
    let size = 1_000_000;
    let warmup = 1;
    let iterations = 10;
    println!("IN List Parse Benchmark - v0.1.7");
    println!("=================================");
    println!("Size: {} items", size);
    println!("Warmup: {} iteration(s)", warmup);
    println!("Iterations: {}", iterations);
    println!();
    let values: Vec<String> = (0..size).map(|i| format!("'{i}'")).collect();
    let sql = format!("SELECT * FROM t WHERE x IN ({})", values.join(", "));
    println!("SQL size: {} bytes", sql.len());
    println!();
    let dialect = polyglot_sql::dialects::Dialect::get(polyglot_sql::dialects::DialectType::Generic);
    for _ in 0..warmup {
        let _ = dialect.parse(&sql);
    }
    let mut times = Vec::with_capacity(iterations);
    for i in 0..iterations {
        let start = Instant::now();
        let result = black_box(dialect.parse(black_box(&sql)));
        let elapsed = start.elapsed();
        times.push(elapsed.as_secs_f64() * 1000.0);
        
        if let Ok(stmts) = result {
            println!("Run {:2}: {:.3} ms ({} statements)", 
                i + 1, elapsed.as_secs_f64() * 1000.0, stmts.len());
        }
    }
    println!();
    let mean: f64 = times.iter().sum::<f64>() / times.len() as f64;
    let min = times.iter().cloned().fold(f64::INFINITY, f64::min);
    let max = times.iter().cloned().fold(f64::NEG_INFINITY, f64::max);
    
    println!("Summary:");
    println!("  Mean: {:.3} ms", mean);
    println!("  Min:  {:.3} ms", min);
    println!("  Max:  {:.3} ms", max);
}

[tobilg]

Thanks for the issue! I had a look, and honestly I think a 1 million IN list is maybe not the most relevant use case I'm targeting with polyglot. I'm not even sure that around 600ms is "slow" in a common sense, but I'll have a go in trying to optimize this.

[veryrandomname]

Yeah I totally get that.
If you’re curious, databend has their own tokenizer which is faster https://www.databend.com/blog/category-engineering/2025-09-10-query-parser/

The parser itself is still pretty bad though, as it copies strings around relentlessly irrc.

[tobilg]

Feel free to open a PR. Thanks!

[veryrandomname]

I’m working on a PR. Will take some time.

[MartinSahlen]

sqlparser-rs had the same issue, i.e a lot of unneccesary allocations. I think it would be worth looking at that, but they were able to improve performance quite nicely. Would probably we easy work for an LLM!

[tobilg]

probably easy work for an LLM!

Spoiler alert: It’s not. Had both Claude Code & Codex doing multiple different attempts to optimize this, and nothing was significantly faster that justified the complexity etc. that came with it.

I‘m curious if you find an approach that is viable though!

[MartinSahlen]

Ok, noted - I dont know if it's exactly the same but I had some success using Gemini 3 pro high on antigravity with a similar refactor (removing owned / allocated strings and only using lifetime bound references, but it was perhaps simpler). But back to the case. I see there has been similar discussions in issues now closed.

• Runs out of memory for large sql. #27
• Fails to actually format a query with many clauses. #31

And there is some discussion about what is really a valid use case. I guess my two cents on this is, after working with real data over the years from multiple companies using BigQuery and all kinds of tools, is that you will see really crazy stuff and these edge cases will at times define the architecture. We use sqlparser-rs for some use cases and we have needed to bump the stack size, and implement a lot of advanced guards when traversing an AST (a combination of timeout + number of node visits), to accomodate massive parse trees but also have reasonable fallbacks to avoid 1 bad query making the system unstable. There are a lot of caveats when you run at scale, and rust gives memory safety guarantees, but certainly doesnt give OOM or CPU / thread starvation guarantees. But this goes probably too deep for this topic!

I think if this is to be a production grade library, there needs to be a decent amount of levers to play with, since in the real world, we are dealing with tools and people that may generate the hairiest SQL you ever see, potentially generating massive where clauses, nested subqueries and all types of evil stuff. Over the years, we have used python, antlr, java, kotlin, hand-rolled and libraries and I find that the p99 of "everything" related to a library - pure performance/speed, syntax support, PR merge cadence and responsivity is all incredibly important. Now, i'm not saying that you should change the direction or goal of this library but just sharing my two cents on this. It's already looking pretty great.

[MartinSahlen]

A small note, when it comes to pure speed - if there is already ample memory available perhaps we will not see a huge time speedup of doing a refactor on reducing the allocations. But did you look at the memory profile also re this

Spoiler alert: It’s not. Had both Claude Code & Codex doing multiple different attempts to optimize this, and nothing was significantly faster that justified the complexity etc. that came with it.

[tobilg]

That's something that on the list, but honestly, I was busy fixing obvious errors the last two weeks since releasing it. I think you're absolutely right with everything you wrote, and I'd love to see some contributions regarding safeguards etc. I'm still pretty new to Rust myself, so I could really use every help or idea I can get. Thanks again!

[MartinSahlen]

All good! We have been considering different options, as we really like the sqlglot library in terms of features and functinonality, hitting this sweetspot of leniency and validation vs something like zetasql/googlesql which becomes too strict at times. But, I will die on this hill, python is simple not a suitable language for high performance stuff when the main / hot loop is happening in python. Even with outsourcing some of the stuff to c or rust, or even trying to pre-compile it (I know because we have tried it), you only ever get gains like 2x max, not the stuff we see from rust where often 10x comes easily.

When I saw this, I think it could be a great starting point. Over the coming time, we will do some more testing and validation and we could potentially also become more stable contributors.

When it comes to safeguards specifically, I think it's not as much about implementing everything - more about the right fundamentals and constructs for allowing safe / timely processing - i.e primitives for stopping traversal given certain conditions, so it might be just a matter of providing better examples.

[veryrandomname]

And there is some discussion about what is really a valid use case. I guess my two cents on this is, after working with real data over the years from multiple companies using BigQuery and all kinds of tools, is that you will see really crazy stuff and these edge cases will at times define the architecture. We use sqlparser-rs for some use cases and we have needed to bump the stack size, and implement a lot of advanced guards when traversing an AST (a combination of timeout + number of node visits), to accomodate massive parse trees but also have reasonable fallbacks to avoid 1 bad query making the system unstable. There are a lot of caveats when you run at scale, and rust gives memory safety guarantees, but certainly doesnt give OOM or CPU / thread starvation guarantees. But this goes probably too deep for this topic!

same experience from me. that's why it's a requirement for me to deal with these queries in an ok way. i have code on my machine that has like 50% test pass rate. will need to continue working on it... perf improvement for large queries is ~10X i can share the current state, but i don't know how without opening a PR. its not ready yet.

[veryrandomname]

i'm rewriting the parser in nom, replaced the tokenizer.. so it's a large change. don't know if you want that.

[MartinSahlen]

i'm rewriting the parser in nom, replaced the tokenizer.. so it's a large change. don't know if you want that.

Interesting, you're getting 10x improvements from nom? Sounds like a worthwhile approach. I guess this may not do much for the p50 and p95, but if it can move the p99 I think it would be we worth it in terms of production-readiness.

[veryrandomname]

The 10x comes from doing almost no copies. Tokens are references into the source sql. AST also tries to have no copies. If allocations are made they are done in an arena.

You’re right it does not do much for small queries.

[veryrandomname]

i realised transforming is even slower in polyglot. 20seconds for a 1million long in list. sqlparser-rs does it in 1s. so i would have to change basically the whole internal plumbing of this library. that's too much for me right now.

you should be able to see how slow it is in a basic example like this one

// src/main.rs
use std::error::Error;
use std::ops::ControlFlow;
use std::time::Instant;

use polyglot_sql::{traversal, parse, DialectType};
use sqlparser::ast::visit_expressions_mut;
use sqlparser::dialect::SQLiteDialect;
use sqlparser::parser::Parser;

fn build_query(size: usize) -> String {
    let values: String = (1..=size).map(|i| i.to_string()).collect::<Vec<_>>().join(", ");
    format!("SELECT * FROM benchmark_items WHERE id IN ({values})")
}

fn main() -> Result<(), Box<dyn Error>> {
    let size = 1_000_000usize;
    let sql = build_query(size);

    // Polyglot: parse + transform timings
    let polyglot_parse_start = Instant::now();
    let expressions = parse(&sql, DialectType::SQLite)?;
    let polyglot_parse = polyglot_parse_start.elapsed().as_secs_f64();

    let polyglot_transform_start = Instant::now();
    let _rewritten: Vec<_> = expressions
        .into_iter()
        .map(|expr| traversal::transform(expr, &|e| Ok(Some(e))))
        .collect::<Result<Vec<_>, _>>()?;
    let polyglot_transform = polyglot_transform_start.elapsed().as_secs_f64();

    println!(
        "polyglot parse={polyglot_parse:.6}s transform={polyglot_transform:.6}s"
    );

    // SQLParser-rs: parse + walk timings (in-place)
    let sqlparser_parse_start = Instant::now();
    let mut statements = Parser::parse_sql(&SQLiteDialect {}, &sql)?;
    let sqlparser_parse = sqlparser_parse_start.elapsed().as_secs_f64();

    let sqlparser_transform_start = Instant::now();
    for statement in &mut statements {
        let _flow = visit_expressions_mut(statement, |_expr| {
            // no-op in-place visitor, keeps work focused on traversal
            ControlFlow::<()>::Continue(())
        });
    }
    let sqlparser_transform = sqlparser_transform_start.elapsed().as_secs_f64();

    println!(
        "sqlparser parse={sqlparser_parse:.6}s transform={sqlparser_transform:.6}s"
    );

    Ok(())
}