Using ANTLR parse cache in Babelfish

Starting with Babelfish versions 5.7.0 and 6.2.0, you can use a persistent ANTLR parse result cache for T-SQL stored routines (procedures and functions) to reduce first-execution latency.

With the procedure parse cache:

Performance: For large routines, ANTLR parsing can take up to several seconds. With the ANTLR parse cache, first-execution parse time is reduced to a few milliseconds. Subsequent executions in the same session are served from the in-session cache (sub-millisecond) and are unaffected.

Understanding the ANTLR parse cache

Babelfish must validate T-SQL syntax by parsing a routine's body the first time it runs in a session. This parse result is normally cached only for the lifetime of that session, so every new connection re-parses from scratch. The parse cache persists the parse result in the catalog so that new sessions can reuse it.

How it behaves:

Observing the parsing-time improvement

You can measure the effect using SET BABELFISH_STATISTICS PROFILE ON, which reports the Babelfish T-SQL Batch Parsing Time for each batch. First, create a large procedure with caching enabled to populate the persistent cache. Then compare the procedure's first execution in two separate new sessions: one with caching disabled, which ignores the persistent entry, and one with caching enabled, which reuses it. Alternatively, if caching was not enabled when the procedure was created, execute it in two separate new sessions with caching enabled: the first execution populates the persistent cache, and the second reuses it.

In the following example, first create the procedure with caching ON to populate the persistent cache:

-- New session 0: caching ON (at CREATE PROCEDURE time)
SELECT set_config('babelfishpg_tsql.enable_antlr_parse_cache', 'on', false);
GO
CREATE PROCEDURE dbo.updateInformation @region VARCHAR(50)
AS
BEGIN
    ...
END;
GO

Next, in a new session with caching OFF (the default), execute the procedure:

-- New session 1: caching OFF (default)
SET BABELFISH_STATISTICS PROFILE ON;
GO
EXEC dbo.updateInformation @region = 'EMEA';   -- first call in this session
GO

The statistics profile reports a high parsing time:

Babelfish T-SQL Batch Parsing Time: 20359.465 ms

Finally, in another new session with caching ON, execute the procedure:

-- New session 2: caching ON
SELECT set_config('babelfishpg_tsql.enable_antlr_parse_cache', 'on', false);
GO
SET BABELFISH_STATISTICS PROFILE ON;
GO
EXEC dbo.updateInformation @region = 'EMEA';   -- first call in this session
GO

The statistics profile reports a significantly lower parsing time:

Babelfish T-SQL Batch Parsing Time: 150.927 ms

The first execution in a new session drops from ~20 s of parsing to ~0.15 s once the cached parse tree is reused. Second and later executions within the same session are served from the in-session cache in both cases (sub-millisecond) and are unaffected by the GUC.

Enabling the cache for a session

The feature is controlled by a session-level GUC (default OFF):

-- Enable parse caching for the current session
SELECT set_config('babelfishpg_tsql.enable_antlr_parse_cache', 'on', false);
GO

-- Disable for the current session
SELECT set_config('babelfishpg_tsql.enable_antlr_parse_cache', 'off', false);
GO

Controlling the cache per routine

You can override the session GUC for an individual routine using sys.enable_antlr_parse_cache, which accepts the routine's object id:

-- Force caching ON for a specific routine (even if the session GUC is OFF)
SELECT sys.enable_antlr_parse_cache(sys.object_id('dbo.my_proc'), true);
GO

-- Force caching OFF for a specific routine — "kill switch"
-- (clears any cached data and blocks caching even if the session GUC is ON)
SELECT sys.enable_antlr_parse_cache(sys.object_id('dbo.my_proc'), false);
GO

-- Reset to default; the routine follows the session GUC
SELECT sys.enable_antlr_parse_cache(sys.object_id('dbo.my_proc'), NULL);
GO

Checking cache statistics

sys.antlr_parse_cache_stats() returns parse-cache activity for the current session:

SELECT * FROM sys.antlr_parse_cache_stats();
GO

The following table describes each column in the output.

Counters are per-session and reset on a new connection.

Example scenario

-- Enable caching for this session
SELECT set_config('babelfishpg_tsql.enable_antlr_parse_cache', 'on', false);
GO

CREATE PROCEDURE dbo.report_proc @region VARCHAR(50)
AS
BEGIN
    -- large multi-statement T-SQL body ...
    SELECT @region;
END;
GO

-- First execution in a brand-new session: served from the persistent cache (fast)
EXEC dbo.report_proc @region = 'APAC';
GO

-- Inspect cache activity for this session
SELECT * FROM sys.antlr_parse_cache_stats();
GO

-- Opt this routine out of caching regardless of the session setting
SELECT sys.enable_antlr_parse_cache(sys.object_id('dbo.report_proc'), false);
GO

Upgrade considerations

Important limitations