Optimizing SQLite Query Performance with Large ORDER BY on High-Column Tables

High-Cardinality JOINs and Missing Indexes Leading to Slow Sorting Operations in Complex Query

Issue Overview: Slow ORDER BY Performance in Multi-Table Join with Large Dataset

The core challenge involves a SQLite query joining two wide tables (134-column CMD_TRADES and 118-column MCX_SECURITY_EXCH_MAP) that returns 200,000-400,000 records, experiencing 2.7x slower execution when using ORDER BY (16 seconds vs 6 seconds without sorting). The query structure features:

1. A Common Table Expression (CTE) calculating maximum serial numbers
2. Multi-column JOIN conditions across both tables
3. Complex CASE statements and calculated fields
4. Sorting by UNIX timestamp and order number

Key performance characteristics:

• Primary tables contain 100+ columns each
• CTE uses GROUP BY on two columns with MAX() aggregation
• Final sort operates on non-indexed columns from largest table
• MCX_SECURITY_EXCH_MAP lacks primary key definition
• System uses spinning HDD instead of SSD

The 10-second penalty from sorting 200K records indicates fundamental issues with:

• Join efficiency between massive tables
• Index coverage for aggregation and sorting
• Temporary storage handling during query execution
• Physical hardware constraints

Root Causes: Index Gaps, Sort Overhead, and Structural Limitations

1. Inefficient MAX() Aggregation in CTE

The MaxSerialNo CTE performs:

SELECT ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO, 
       MAX(ORD_SERIAL_NO) AS Max_Serial_No
FROM cmd_trades
WHERE ORD_STATUS = 'T' AND ord_trans_Code NOT IN (8005)
GROUP BY ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO

Missing elements impacting performance:

• No composite index covering filtered columns (ORD_STATUS, ord_trans_Code) with grouped columns
• MAX() calculation requires full scan of qualifying groups
• Wide table structure forces index/key lookups for unindexed columns

2. Non-Covered Sorting Operation

The ORDER BY clause:

ORDER BY a.ORD_LAST_UPDT_TIME_UNIX DESC, a.ord_order_no

Suffers from:

• No descending index on ORD_LAST_UPDT_TIME_UNIX
• Combined sort keys not present in any index
• Sorting 200K+ records in memory with potential disk spillage

3. MCX_SECURITY_EXCH_MAP Join Bottleneck

The JOIN condition:

JOIN MCX_SECURITY_EXCH_MAP c 
  ON c.MSM_INSTR_IDENTIFIER = a.ORD_SEM_SMST_SECURITY_ID

Has critical issues:

• MCX_SECURITY_EXCH_MAP lacks primary key
• No index on MSM_INSTR_IDENTIFIER (join key)
• 118-column width amplifies I/O costs

4. Hardware Limitations

• 4th Gen i5 processor (released 2013-2015) with limited single-thread performance
• 5400 RPM HDD with ~100 IOPS vs SSD’s 50,000+ IOPS
• 12GB RAM may force disk-based sorting for large datasets

Resolution Strategy: Index Optimization, Query Restructuring, and System Tuning

Phase 1: Index Architecture Overhaul

1.1 CTE-Specific Composite Index

CREATE INDEX idx_trades_cte_filter 
ON cmd_trades (
  ORD_STATUS, 
  ord_trans_Code, 
  ORD_TRD_EXCH_TRADE_NO, 
  ORD_ORDER_NO, 
  ORD_SERIAL_NO DESC
);

• Filters first (ORD_STATUS, ord_trans_Code)
• Groups next (ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO)
• Includes MAX() target (ORD_SERIAL_NO) in descending order
• Enables index-only scan for CTE

1.2 Covering Index for Sorting

CREATE INDEX idx_trades_sorting 
ON cmd_trades (
  ORD_LAST_UPDT_TIME_UNIX DESC, 
  ord_order_no, 
  ORD_TRD_EXCH_TRADE_NO, 
  ORD_ORDER_NO, 
  ORD_SERIAL_NO
);

• Matches exact ORDER BY sequence
• Includes JOIN keys to enable index-only navigation
• DESC keyword matches sort direction

1.3 MCX Table Join Optimization

CREATE INDEX idx_mcx_security_map_identifier
ON MCX_SECURITY_EXCH_MAP(MSM_INSTR_IDENTIFIER);

• Enables hash join instead of nested loop
• Consider covering index if frequently accessed columns exist

Phase 2: Query Restructuring Techniques

2.1 Materialize CTE Results

CREATE TEMPORARY TABLE TempMaxSerialNo AS
SELECT ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO, 
       MAX(ORD_SERIAL_NO) AS Max_Serial_No
FROM cmd_trades
WHERE ORD_STATUS = 'T' AND ord_trans_Code NOT IN (8005)
GROUP BY ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO;

ANALYZE TempMaxSerialNo;

• Precompute aggregation
• ANALYZE generates statistics for query planner

2.2 Partitioned Sorting with LIMIT-OFFSET

SELECT ... 
ORDER BY a.ORD_LAST_UPDT_TIME_UNIX DESC, a.ord_order_no
LIMIT :page_size OFFSET :page_offset;

• Client-side pagination to reduce single-sort overhead
• Requires application changes

2.3 Column Pruning and Projection Optimization
Replace:

SELECT c.MSM_PROD_MONTH AS ProdMonth, 
       ORD_SPREAD_PRICE, ... (134 columns)

With explicit column list instead of * and remove unused columns

2.4 CASE Statement Materialization
Precompute in temp table:

CREATE TEMP TABLE ProcessedTrades AS
SELECT 
  ...,
  CASE WHEN OPTION_TYPE = 'XX' THEN ' ' ELSE OPTION_TYPE END AS OPTION_TYPE,
  ...
FROM ...;

Phase 3: SQLite Configuration Tuning

3.1 Memory Configuration

PRAGMA temp_store = MEMORY; -- Keep temps in RAM
PRAGMA cache_size = -20000; -- Allocate 20GB RAM (adjust based on system)
PRAGMA mmap_size = 1073741824; -- 1GB memory mapping

3.2 Journal Mode and Synchronous Settings

PRAGMA journal_mode = MEMORY; -- For temporary tables
PRAGMA synchronous = OFF; -- During query execution only

3.3 Parallel Processing with Worker Threads

PRAGMA threads = 4; -- Match CPU core count

Phase 4: Hardware-Aware Optimizations

4.1 SSD Migration Strategy

• Clone database to SSD with 4K sector alignment
• Test query on SSD before full migration

4.2 RAM Disk Configuration
Mount tmpfs for temporary storage:

sudo mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk

Set SQLite temp store:

PRAGMA temp_store_directory = '/mnt/ramdisk';

4.3 File System Optimization

• Use EXT4 with noatime,nodiratime mount options
• Allocate 25% free space for SQLite growth

Phase 5: Advanced Execution Plan Engineering

5.1 Forcing Index Usage

SELECT /*+ INDEX(cmd_trades idx_trades_sorting) */ ...
FROM cmd_trades AS a ...

5.2 Subquery Flattening
Rewrite CTE as derived table:

SELECT ...
FROM (
  SELECT ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO, 
         MAX(ORD_SERIAL_NO) AS Max_Serial_No
  FROM cmd_trades
  WHERE ORD_STATUS = 'T' AND ord_trans_Code NOT IN (8005)
  GROUP BY ORD_TRD_EXCH_TRADE_NO, ORD_ORDER_NO
) AS maxNo ...

5.3 Statistical Profiling

sqlite3 database.db '.timer on' \
  'EXPLAIN QUERY PLAN SELECT ...' > plan.txt

Analyze:

• SCAN vs SEARCH operations
• Temporary b-tree usage
• Join order efficiency

Phase 6: Alternative Storage Architectures

6.1 Columnar Data Extraction
Export sorted data to CSV:

.once output.csv
SELECT ... ORDER BY ...;

Use external tools for sorting (e.g., GNU sort)

6.2 Read-Only Database Configuration

PRAGMA query_only = ON; -- Prevent accidental writes
PRAGMA locking_mode = EXCLUSIVE;

6.3 Database Sharding by Time Range
Split cmd_trades into monthly databases:

ATTACH 'trades_202301.db' AS trades_jan;

Phase 7: Maintenance and Monitoring

7.1 Index Fragmentation Analysis

SELECT name, stat FROM sqlite_stat1 
WHERE tbl_name = 'cmd_trades';

7.2 Auto-Vacuum Configuration

PRAGMA auto_vacuum = INCREMENTAL;
VACUUM;

7.3 Query Plan Cache Inspection

SELECT sql, execution_count 
FROM sqlite_stat4;

Final Performance Validation

Expected improvements after full implementation:

Continuous monitoring with:

PRAGMA compile_options; -- Verify enabled features
SELECT * FROM pragma_function_list; -- Check math functions

This comprehensive approach addresses all layers from disk I/O to query structure, providing both immediate relief and long-term optimization pathways.