Home › API › SIMPLE_SQL_QUERY_MONITOR

SIMPLE_SQL_QUERY_MONITOR

Runtime N+1 query detection. Records executed queries and identifies patterns that suggest N+1 problems. Enable during development/testing to catch performance issues before production.

View Source

Development Tool

Query monitoring adds overhead. Enable it during development and testing to detect N+1 patterns, then disable in production. Use eager loading to fix detected issues.

Enable/Disable

db.enable_query_monitor

Start recording queries. All subsequent queries via db.query are tracked.

db.disable_query_monitor

Stop recording and clear all recorded data.

db.query_monitor: detachable SIMPLE_SQL_QUERY_MONITOR

Access the active monitor (Void if disabled).

Configuration

set_threshold (a_count: INTEGER)

Set warning threshold. A query pattern executed more than this many times triggers a warning. Default is 5.

threshold: INTEGER

Current warning threshold.

Recording

record_query (a_sql: READABLE_STRING_8)

Record a query execution. Called automatically by db.query when monitoring is enabled.

reset

Clear all recorded queries and warnings. Use between test cases.

Analysis

has_warnings: BOOLEAN

Were any N+1 patterns detected?

warnings: ARRAYED_LIST [TUPLE [pattern: STRING_8; exec_count: INTEGER]]

List of detected N+1 patterns with execution counts.

report: STRING_8

Human-readable report of all detected patterns.

query_count: INTEGER

Total number of queries recorded.

Examples

Basic Usage

do
    -- Enable monitoring
    db.enable_query_monitor

    -- Run your code that might have N+1 issues
    load_documents_with_folders

    -- Check for problems
    if attached db.query_monitor as monitor then
        if monitor.has_warnings then
            print ("N+1 WARNING:%N")
            print (monitor.report)
        else
            print ("No N+1 patterns detected. " + monitor.query_count.out + " queries total.%N")
        end
    end

    -- Disable when done
    db.disable_query_monitor
end

In Test Cases

class TEST_DOCUMENT_LOADING

feature -- Tests

    test_no_n_plus_1_when_loading_documents
        local
            docs: SIMPLE_SQL_RESULT
        do
            db.enable_query_monitor
            if attached db.query_monitor as mon then
                mon.set_threshold (3)  -- Stricter for tests
            end

            -- Operation under test
            docs := document_service.load_all_with_folders

            -- Assert no N+1
            if attached db.query_monitor as mon then
                assert ("no N+1 queries", not mon.has_warnings)
                if mon.has_warnings then
                    print (mon.report)
                end
            end

            db.disable_query_monitor
        end

end

Detecting the Problem

-- This code has an N+1 problem:
across db.select_from ("documents").execute as doc loop
    -- Query per document!
    folder := db.select_from ("folders")
        .where ("id = ?", doc.integer_value ("folder_id"))
        .execute.first
end

-- Query monitor output:
-- N+1 Query Warning Report
-- ========================
-- Pattern: SELECT * FROM folders WHERE id = ?
--   Executed: 100 times (threshold: 5)
-- 
-- Total patterns with warnings: 1

After Fixing with Eager Loading

-- Fixed version:
result := db.eager_loader
    .from_table ("documents")
    .include ("folder", "folders", "folder_id", "id")
    .execute

across result.main_rows as doc loop
    folder := result.first_related_for ("folder", doc.integer_64_value ("id"))
end

-- Query monitor output:
-- No N+1 patterns detected. 2 queries total.

Adjusting Threshold

if attached db.query_monitor as mon then
    -- Default threshold is 5
    -- Increase for batch operations that legitimately repeat
    mon.set_threshold (20)

    -- Or decrease for stricter checking
    mon.set_threshold (3)
end

Pattern Normalization

The monitor normalizes queries to detect patterns. SELECT * FROM users WHERE id = 1 and SELECT * FROM users WHERE id = 2 are recognized as the same pattern.

Resetting Between Operations

-- Monitor multiple operations separately
if attached db.query_monitor as mon then
    -- First operation
    operation_a
    print ("Operation A: " + mon.query_count.out + " queries%N")

    -- Reset for second operation
    mon.reset

    -- Second operation
    operation_b
    print ("Operation B: " + mon.query_count.out + " queries%N")
end