How to Optimize SQL Parsing Performance

The three highest-impact optimizations when parsing SQL at scale with General SQL Parser are: reuse parser instances instead of constructing a new TGSqlParser per statement, split very large scripts with getrawsqlstatements() before building full parse trees, and parallelize across threads with one parser instance per thread. This guide shows each technique with verified APIs, plus how to measure where time is going with GSP's built-in phase timers.

Reuse Parser Instances

Constructing a TGSqlParser is cheap, but the first parse() for a vendor loads that dialect's parse tables. Keeping one instance alive per vendor amortizes that warm-up and avoids repeated allocation. Reuse is fully supported: each parse() call clears the previous error list, and assigning new sqltext replaces the input.

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;

import java.util.Arrays;
import java.util.List;

public class ReuseParser {
    public static void main(String[] args) {
        List<String> sqls = Arrays.asList(
            "SELECT * FROM orders WHERE id = 1",
            "UPDATE orders SET status = 'shipped' WHERE id = 1",
            "DELETE FROM order_staging WHERE id = 1");

        // One instance, many parses — do NOT create a parser per statement
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);

        int ok = 0;
        for (String sql : sqls) {
            parser.sqltext = sql;
            if (parser.parse() == 0) {
                ok++;
                // extract what you need from parser.getSqlstatements() HERE,
                // before the next parse() replaces the statement list
            }
        }
        System.out.println(ok + "/" + sqls.size() + " parsed");
    }
}

Two rules for safe reuse:

1. Consume results between calls. The statement list belongs to the parser; copy out the data you need (table names, errors, statement text) before parsing the next input.
2. One vendor per instance. Keep a separate parser per EDbVendor rather than reconfiguring — switching dialects forfeits the loaded-tables benefit.

Split Large Scripts Before Full Parsing

For multi-megabyte migration scripts or DDL dumps, you often don't need a full AST of everything at once. getrawsqlstatements() splits a script into individual statements without syntax checking or building parse trees — it is much faster than parse() and dramatically reduces peak memory:

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;

public class SplitThenParse {
    public static void main(String[] args) {
        TGSqlParser splitter = new TGSqlParser(EDbVendor.dbvmssql);
        splitter.sqltext = readHugeScript(); // e.g. 100k statements

        if (splitter.getrawsqlstatements() != 0) {   // split only — no parse trees
            System.err.println(splitter.getErrormessage());
            return;
        }

        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvmssql);
        for (int i = 0; i < splitter.getSqlstatements().size(); i++) {
            TCustomSqlStatement raw = splitter.getSqlstatements().get(i);

            // Optionally filter by raw text before paying for a full parse
            String text = raw.toString();
            if (!text.toUpperCase().contains("INSERT")) continue;

            parser.sqltext = text;
            if (parser.parse() == 0) {
                // full AST for just this one statement
            }
        }
    }

    static String readHugeScript() { /* load from file */ return ""; }
}

This "split, filter, then parse" pipeline bounds memory to one statement's AST at a time and lets you skip statements you don't care about (comments, GRANTs, huge INSERT VALUES batches) before doing full work. Batch separators like SQL Server's GO are understood by the splitter — see Handle SQL Server T-SQL.

Tokenizing without parsing

If you only need tokens — for search, masking, or statistics — tokenizeSqltext() is cheaper still. It fills getSourcetokenlist() (a TSourceTokenList) with classified tokens and never runs the grammar:

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = "SELECT id, name FROM users WHERE id = 1";
parser.tokenizeSqltext();
System.out.println("tokens: " + parser.getSourcetokenlist().size()); // 18

Parallel Parsing: One Parser Per Thread

TGSqlParser instances are not thread-safe — never share one instance across threads. The supported pattern is one instance per thread, which scales linearly because parsing is CPU-bound with no shared state:

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;

import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;

public class ParallelParsing {
    // Each worker thread gets (and keeps) its own parser instance
    private static final ThreadLocal<TGSqlParser> PARSER =
        ThreadLocal.withInitial(() -> new TGSqlParser(EDbVendor.dbvoracle));

    public static void main(String[] args) throws Exception {
        List<String> files = loadSqlFiles();
        AtomicInteger failures = new AtomicInteger();

        ExecutorService pool = Executors.newFixedThreadPool(
            Runtime.getRuntime().availableProcessors());

        for (String sql : files) {
            pool.submit(() -> {
                TGSqlParser parser = PARSER.get();   // thread-confined instance
                parser.sqltext = sql;
                if (parser.parse() != 0) {
                    failures.incrementAndGet();
                }
                // extract results here, inside the task
            });
        }
        pool.shutdown();
        pool.awaitTermination(1, TimeUnit.HOURS);
        System.out.println("failures: " + failures.get());
    }

    static List<String> loadSqlFiles() { /* ... */ return java.util.Collections.emptyList(); }
}

Partition work at the file or statement level (use the splitting technique above to fan a single huge script out across workers). For one-off needs inside a parallel pipeline, the static helpers TGSqlParser.parseSubquery(vendor, sql) and TGSqlParser.parseExpression(vendor, sql) are documented thread-safe.

Measure Before Tuning

GSP has built-in phase timers, so you can see whether time goes to statement splitting, grammar parsing, or semantic analysis (table/column linking):

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.setEnableTimeLogging(true);
parser.sqltext = "SELECT a, b FROM t1 JOIN t2 ON t1.id = t2.id";
parser.parse();

System.out.println("split:    " + parser.getRawSqlStatementsTime() + " ms");
System.out.println("parse:    " + parser.getParsingTime() + " ms");
System.out.println("semantic: " + parser.getSemanticAnalysisTime() + " ms");
System.out.println("total:    " + parser.getTotalTime() + " ms");

Call resetTimeCounters() between measurement batches — the counters accumulate across parse() calls, which is convenient for measuring throughput over many statements.

Memory Notes

• The AST references the full token list. Holding parsed statements alive holds their source tokens too. Extract lightweight results (names, positions, strings) and let the statement objects go.
• Peak memory follows the largest single input, not the total workload — which is why splitting a huge script and parsing statement-by-statement is the main memory lever.
• Generated INSERT dumps (millions of literal rows) are the classic memory hazard. Detect them cheaply after getrawsqlstatements() (statement text length is available without parsing) and handle them with the tokenizer instead of a full parse.
• If you see OutOfMemoryError on legitimate workloads, raise the JVM heap (-Xmx) — parsing is allocation-heavy but short-lived, so generational GC handles it well.

Checklist

1. Reuse one TGSqlParser per vendor per thread.
2. Split big scripts with getrawsqlstatements(); parse statements selectively.
3. Use tokenizeSqltext() when tokens are enough.
4. Parallelize with thread-confined parser instances.
5. Turn on setEnableTimeLogging(true) and measure before optimizing further.

Related Pages

• Error Handling — per-statement validation built on the same loop
• Performance Considerations — architecture background
• Handle SQL Server T-SQL — batch (GO) splitting specifics
• Quick Start — installation and first parse