CODEWITHSUNDEEP
mysqldatabasepostgresqlrdbms
Jaehyun Shin
Jaehyun Shin

Reputation: 1602

Why prepared statements are managed by each session?

Why prepared statements are managed by each session?

I am not sure all of databases handle prepared statements per session. but postgresql and mysql(maybe) do.

Why they are not shared between session? Is there any reason?

For example, There are many connections in connection pool and connections share same queries and also generate same prepared statements. Why database does not let connections share prepared statement?

Upvotes: 2

Views: 1105

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45750

Maintaining any shared cache is expensive - you should to protect cache against race condition, you should to clean cache, ... This is first argument. Second argument is more important - prepared statement is cached execution plan - but this plan is optimized for one vector of parameters. When you are using different parameter's vector, then plan can be suboptimal again this vector. There is a philosophy, so any user can uses different vectors and then needs different plans. Using session shared plan cache doesn't eliminate overhead from generating plans (although this overhead is for fast simple queries minimal), but eliminates overhead and race conditions related to management of shared data, and partially reduce risk of using suboptimal prepared plans.

Upvotes: 1

Related Questions