3 ms·

>That applies to queries but not more general TSQL with branching logic, etc. That is not my understanding, all of the TSQL including procedural logic is "comp

>That applies to queries but not more general TSQL with branching logic, etc. That is not my understanding, all of the TSQL including procedural logic is "compiled" but it is not optimized because it is not a set based operation. However no parsing or binding needs to be done on it if reused. The entire TSQL statement including branching logic is run through the parser and algebrizer and turned into a binary form that the optimizer can work on then cached. >PostgreSQL further allows tuning of whether the query plan is specialized based on parameter values. SQL server has prepared statement but they are basically unnecessary there is no need to manually tell it to prepare and reuse as it will do so on its own, it mostly there for backward compatibility. SQL server will also optimize based on parameter values, lookup parameter sniffing, it can store multiple plans and may not use a stored plan based on parameters. It will also potentially auto parameterize sql statements that don't use parameters so they can be reused. Again I have used sql server going back to 6.5 when it didn't cache ad-hoc queries and you had to use sprocs to get caching. During that time almost every app went through sprocs for every call because of the overhead of parsing and planning, its was like a 2x speedup. Nowdays we have a lot more network memory and cpu, I still don't want my db server wasting it on parsing and planing the same statements over and over for no good reason. What programming language even interpreted doesn't keep some optimized ready to run version around for repeated calls and instead reparses and binds on every hit?

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 ago
  Batch 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.

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 ago
Batch 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.