How does a PreparedStatement's plan cache actually save work compared to concatenated SQL, and what's the trade-off — parameter sniffing — that comes with it?

A PreparedStatement sends the SQL text once, with placeholders, and the database parses it into an execution plan; every later execution with different bound values reuses that same plan instead of parsing and planning from scratch. Concatenated SQL ("price < " + value) produces a different string on every call, so nothing can be cached or reused — a thousand different literal values means a thousand parses. The trade-off: a cached plan is chosen for whichever value ran first (or an average case) and then reused for everyone, so a plan that's fast for a common value can be badly wrong for a rare, skewed one — the phrase for this is parameter sniffing.