Query Parsing Pipeline: Soft vs Hard Parse

Definition
Query parsing transforms a text query into an executable plan through lexical analysis (breaking text into tokens), syntax analysis (building a tree structure), semantic validation (checking tables and columns exist), and optimization (choosing the fastest execution path).
The Parsing Pipeline
Consider this query: SELECT name, email FROM users WHERE status = 'active' AND created_at > '2024-01-01'. Lexical analysis breaks this into tokens: keywords (SELECT, FROM, WHERE), identifiers (name, email, users, status, created_at), operators (=, >, AND), and literals ('active', '2024-01-01'). Syntax analysis builds an Abstract Syntax Tree (AST), a hierarchical structure where the root is SELECT, with child nodes for columns (name, email), table (users), and filter conditions. Semantic analysis validates that the users table exists, that name, email, status, and created_at are valid columns, and that the date comparison is type compatible.
Soft Parse vs Hard Parse
The critical performance distinction is between soft parse and hard parse. When you execute SELECT * FROM orders WHERE user_id = 12345, the database normalizes the query text, computes a hash, and checks the plan cache. A soft parse finds a cached plan and returns in 0.1 to 0.5 milliseconds. A hard parse requires full parsing and optimization, taking 5 to 50 milliseconds for complex queries. In OLTP (Online Transaction Processing) systems handling 50,000 queries per second, hard parsing 5% of queries wastes significant CPU.
Parameterized Queries
Instead of embedding literals directly like SELECT * FROM orders WHERE user_id = 12345, use parameters: SELECT * FROM orders WHERE user_id = ? or SELECT * FROM orders WHERE user_id = $1. All queries with the same structure share one cached plan. A system executing 10,000 variations of the same query parses once and reuses 9,999 times, reducing parse overhead by 99.99%. This also prevents SQL injection: malicious input like '; DROP TABLE users; -- becomes a literal string value rather than executable SQL.

💡 Key Takeaways

✓Query parsing transforms SQL through lexical analysis (tokenizing SELECT, FROM, WHERE), syntax analysis (building AST), semantic validation (checking tables/columns exist), and optimization

✓Soft parse retrieves cached plan in 0.1 to 0.5 milliseconds; hard parse requires full optimization taking 5 to 50 milliseconds for complex queries with multiple joins

✓Parameterized queries like SELECT * FROM orders WHERE user_id = $1 share one cached plan across all parameter values, reducing parse overhead by 99.99%

✓In OLTP systems at 50,000 QPS, hard parsing 5% of queries wastes significant CPU; prepared statements maximize soft parse hits

✓Parameterized queries prevent SQL injection by treating user input as literal values, not executable SQL structure

📌 Interview Tips

1Show the difference between literal and parameterized: WHERE user_id = 12345 versus WHERE user_id = $1. Explain that the parameterized version shares one cached plan across all user IDs.

2Walk through AST construction: SELECT name FROM users WHERE active = true becomes a tree with SELECT at root, column node (name), table node (users), and filter node (active = true).

3Explain SQL injection prevention: malicious input like '; DROP TABLE users; --' becomes a literal string value when parameterized, not executable SQL.

← Back to Query Parsing & Optimization Overview