31–37 of 37 posts
Prepared Statements are a place where 'correct' and 'fast' intersect in a big way. If all databases would detect when you're done with them and close them automatically, they'd be just about perfect. But you can't have everything I suppose. A few programming languages have designed or re-designed their APIs to try to detect this at the library level, and I appreciate the efforts to do so. I was stunned to learn that…
Looks like ecto went too far by using named prepared statement by default? Should work just fine with unnamed prepared statement: https://github.com/elixir-ecto/postgrex/blob/master/README.m... Postgres planner is very fast, so there will be no noticeable performance difference by having to plan the same query again and again. This is unlike, say MSSQL, where a semi-complex query may take more than 0.5s to parse/plan…
> Postgres planner is very fast, so there will be no noticeable performance difference by having to plan the same query again and again. This is unlike, say MSSQL, where a semi-complex query may take more than 0.5s to parse/plan, thus requiring the rather lousy query plan cache. That's not true. For a simple read only oltp workload (postgres' pgbench -S), the difference between prepared and non-prepared is significan…
Anyway, as there is no way for a query plan cache to be one-size-fit-all, as shown by both TFA and some comments in this HN discussion, that slight overhead is more than acceptable IMO, especially when compared to MSSQL.
Earlier quoted context omitted.
> Postgres planner is very fast, so there will be no noticeable performance difference by having to plan the same query again and again. This is unlike, say MSSQL, where a semi-complex query may take more than 0.5s to parse/plan, thus requiring the rather lousy query plan cache. That's not true. For a simple read only oltp workload (postgres' pgbench -S), the difference between prepared and non-prepared is significan…
Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Anyway, as there is no way for a query plan cache to be one-size-fit-all, as shown by both TFA and some comments in this HN discussion, that slight overhead is more than acceptable IMO, especially when compared to MSSQL.
Those are worse, due to the increased number of protocol messages (parse/bind/execute vs exec).
tps = 491218.039129 (excluding connections establishing)
> that slight overhead is more than acceptable IMO, especially when compared to MSSQL.
I wouldn't call a 2x performance difference slight... That's also with a lot of context switching overhead - if you have a client using pipelined execution, the difference also gets bigger.
Earlier quoted context omitted.
Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Anyway, as there is no way for a query plan cache to be one-size-fit-all, as shown by both TFA and some comments in this HN discussion, that slight overhead is more than acceptable IMO, especially when compared to MSSQL.
> Thanks for the numbers. Can you run it for -M extended as well? That would be the case when using unnamed prepared statement. Those are worse, due to the increased number of protocol messages (parse/bind/execute vs exec). tps = 491218.039129 (excluding connections establishing) > that slight overhead is more than acceptable IMO, especially when compared to MSSQL. I wouldn't call a 2x performance difference slight..…
- could be more I/O bound than when just doing PK lookups with pgbench
- could be getting a wrong plan from the query plan cache if named prepared statement is used
Prepared Statements are a place where 'correct' and 'fast' intersect in a big way. If all databases would detect when you're done with them and close them automatically, they'd be just about perfect. But you can't have everything I suppose. A few programming languages have designed or re-designed their APIs to try to detect this at the library level, and I appreciate the efforts to do so. I was stunned to learn that…
Can you share which database mapping tool for NodeJS this is?
https://github.com/sequelize/sequelize/issues/11586
Good for them, but... Version 6?
It appears that the first person to propose an implementation did so in 2018, gave up and moved to other tools.
I’m just getting back to such things, I had looked at knex and bookshelf as well, I believe I picked the latter.
The side-loading logic for Ember isn’t really supported by any of these and I got bogged down trying to write those by hand. I’m about to pick that project up again and I think I will be using Svelte, and possibly Phoenix. I like JavaScript, but it turns out I just don’t like using it all day.
Earlier quoted context omitted.
You really want this to be an SQL mode that the server enforces. Give me a query where anything that could be a parameter wasn't and I'll fail the query, write a nasty log, and maybe disconnect your client. I think as an opinionated library, you could build a minimal parser, and fail queries that had values or didn't meet your parsing. I'm not super familiar with SQL these days, but I'd guess you could make a reasona…
The other way to fight things like this that however you want your users to behave should be the most straightforward way to use the code. If you library provides shortcuts to bad behavior, people will take them. I was just rereading you comments and started searching for SQL linters and thinking how hard that would be in practice due to all the ways the queries get muddled in imperative code and a thought came to mi…
Prepared Statements are a place where 'correct' and 'fast' intersect in a big way. If all databases would detect when you're done with them and close them automatically, they'd be just about perfect. But you can't have everything I suppose. A few programming languages have designed or re-designed their APIs to try to detect this at the library level, and I appreciate the efforts to do so. I was stunned to learn that…
In MSSQL prepared statements are not really used because its unnecessary. MSSQL hashes incoming SQL statements and looks for them in the plan cache and reuses automatically. It also looks at connections setting and does parameter sniffing to decided whether to use existing plans. You can still do prepared statements with MSSQL but its mainly there for backward compatibility. I was actually surprised to find PG didn't…