16 ms·
> parsing and planing the same statements over and over...What programming language even interpreted...reparses and binds on every hit? Yeah, I agree. But I
by paulddraper 7y ago
> parsing and planing the same statements over and over...What programming language even interpreted...reparses and binds on every hit?
Yeah, I agree.
But I typically don't see it as a big problem because (1) queries are short so parsing is nothing (2) in some cases you'll want query plans to depend on parameter values, which hurts any cache rate (3) personally I heavily batch operations, e.g. a big insert is simply:
INSERT INTO example
SELECT * FROM unnest($?::int[], $?::text[])
P.S. AWS Aurora PostgreSQL has a plugin with global query plan caching and management. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
- SigmundA 7y agoBatch inserts are not the issue in MSSQL typically you would use bulk insert instead if you really wanted performance, although plan caching helps still helps. Where you get big wins in plan caching are more complex selects that are run repeatedly from an application on multiple connections usually from a pool of say 100. These selects with many joins take significant time to parse and plan but execute very fast. This is pretty typical in a typical business app that is read heavy calling common selects over and over, like an entity find or read with security checks.