Are Stored Procedures Still More Efficient Than Inline Statements on Modern RDBMS?

Historically, stored procedures were considered faster than inline statements due to factors such as pre-parsed SQL and reduced network latency. However, these advantages have waned in modern databases:

Pre-parsed SQL: While still beneficial, the performance gain is less noticeable on modern CPUs. However, for highly repetitive SQL statements, parsing overhead can accumulate.

Pre-generated Query Execution Plan: Modern optimizers cache query plans for individual SQL statements, greatly reducing the performance difference between stored procedures and ad hoc SQL. Optimizer path plans can also significantly speed up plan generation.

Reduced Network Latency: With fast Ethernet speeds, the latency benefits of stored procedures have become less significant, especially for small SQL statements.

Cache Benefits: Stored procedures can improve performance if the data is already cached on the DBMS and server-side transforms are performed. However, for applications without shared memory access to DBMS data, stored procedures still have an edge.

Parameterized/Prepared SQL: Parameterized SQL is a hybrid between stored procedures and ad hoc SQL. It uses parameters for query values and allows optimizers to cache query execution plans, offering similar performance benefits to stored procedures.

Ad Hoc SQL: Modern DBMS can "abstract" ad hoc SQL into parameterized versions, bridging the performance gap with stored procedures. With sophisticated optimizers, ad hoc SQL performance is often comparable to stored procedure performance for average use cases.

Conclusion:

In most cases, using stored procedures solely for performance reasons is likely premature optimization. For simple or moderate SQL workloads, parameterized or ad hoc SQL can provide comparable performance. Stored procedures may still be beneficial in specific scenarios, such as:

• Repetitive, computationally intensive SQL statements
• Moving procedural code into the DBMS to reduce network traffic
• Complex SQL requiring optimization hints