Prepared statements and their surprising performance implications • Hacker News Board

Re: Prepared statements and their surprising performance implications

#21

Post by cneu » Mon, Feb 24, 2020, 9:32 AM UTC

adrianmsmith wrote: post #12

toast0 wrote: post #8

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…

Oracle, at least, has/had the problem that it can't use the values of parameters to affect the execution plan. So sometimes it's good to write in the values in the SQL statement. If you write "select .. where account_id=?" then the query plan will probably be the same for any account_id, e.g. use the index, so that's fine, working as intended. However, if you write "... where active=?" and active can only be true or…

That's not entirely correct. A hard parse causes the optimizer to do bind variable peeking, i.e. it chooses the execution plan based on the bind variable value.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/optim...

Re: Prepared statements and their surprising performance implications

#22

Post by hinkley » Mon, Feb 24, 2020, 9:37 AM UTC

redis_mlc wrote: post #10

hinkley wrote: post #3

Long ago, we brought in a consultant to tell us why we couldn't max out our very fancy Oracle 9i database hardware. Nothing we did could get us much above 50% saturation on the disk array, CPU, or networking. We were stumped. I don't know where Oracle is on this issue these days, but in that version there was a query cache for solved queries. And for a query to execute, it had to be in that cache. That dictated how m…

> Nothing we did could get us much above 50% saturation on the disk array, CPU, or networking. What you wrote basically makes no sense unless you are doing continuous table scans (ask any DBA and see how puzzled they look.) Production MySQL and Postgres master databases that I administer typically have uptime loads of 0.1 to 0.2 on Linux, processing 5,000 qps. Regarding placeholders and bind parameters (those are dif…

If you are getting a lower qps out of a machine than you expect and none of the hardware subsystems are saturated then something is wrong, and it’s not table scans. A table scan would be maxing iops for disk or memory or both, right?

When was 9i released? Are you qualified to compare 15 year old hardware running a 15 year old database on a 15 year old OS to what you have going on now?

(Hell, I’m not even sure it was Intel hardware. Odds are reasonably good that was on an Ultrasparc, since that’s what our customers were running)

Re: Prepared statements and their surprising performance implications

#23

Post by sho » Mon, Feb 24, 2020, 10:07 AM UTC

angry_octet wrote: post #16

angry_octet wrote: post #4

I appreciate the use of the re-worked Pride and Predjudice quote, especially as modern social networking seems just as airheaded and deadly serious as early 19th century polite society. We in tech are the majordomos of the stately manion, the scullery maids and chimney sweeps, while all look up at Dukes Bezos, Gates, Bloomberg and Zuck.

Y'all are humourless philistines.

> Y'all are humourless philistines.

For what do we live, but to make sport for our neighbours, and laugh at them in our turn?

But seriously, I think the intersection of software nerds and readers of Pride and Prejudice is probably fairly small. I appreciated it too, but can understand that 99% of people here probably have no idea what you're talking about. For the curious, the quote in question is It is a truth universally acknowledged...

Re: Prepared statements and their surprising performance implications

#24

Post by pif » Mon, Feb 24, 2020, 10:16 AM UTC

cneu wrote: post #21

adrianmsmith wrote: post #12

Earlier quoted context omitted.

Oracle, at least, has/had the problem that it can't use the values of parameters to affect the execution plan. So sometimes it's good to write in the values in the SQL statement. If you write "select .. where account_id=?" then the query plan will probably be the same for any account_id, e.g. use the index, so that's fine, working as intended. However, if you write "... where active=?" and active can only be true or…

That's not entirely correct. A hard parse causes the optimizer to do bind variable peeking, i.e. it chooses the execution plan based on the bind variable value. https://docs.oracle.com/cd/E11882_01/server.112/e41573/optim...

From the same source:

When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.

Re: Prepared statements and their surprising performance implications

#25

Post by SigmundA » Mon, Feb 24, 2020, 1:09 PM UTC

hinkley wrote: post #2

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 do this recently and only had prepared statements and can't share plans across connections.

Re: Prepared statements and their surprising performance implications

#26

Post by throwdbaaway » Mon, Feb 24, 2020, 1:47 PM UTC

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, thus requiring the rather lousy query plan cache.

Re: Prepared statements and their surprising performance implications

#27

Post by smoyer » Mon, Feb 24, 2020, 2:27 PM UTC

TNDR (Too Narrow - Didn't Read): I have a really wide monitor and this site's code is horrible to read as it's compressed to a very narrow strip (less than 600px) of text down the middle of the browser window. I tried ... I really did and it seems like the content might be worth reading.

Re: Prepared statements and their surprising performance implications

#28

Post by blunte » Mon, Feb 24, 2020, 3:52 PM UTC

Cthulhu_ wrote: post #18

blunte wrote: post #11

Great insight! And this situation illustrates why it's useful to understand a bit (especially understand how to test and explore) deeper than just the (not)ORM.

I'm getting a feeling of dread now. I'm using an ORM at the moment (Facebook's 'ent' in Go) but I don't know what the underlying queries and performance are like. I guess I'll just build my application and review the queries / performance cost further down the line. He said hopefully.

The point is that at some point, it does become necessary to learn the complexities beneath the interface. However, you don't have to learn it until you _have_ to learn it. Just be prepared to spend some quality time when you reach that wall.

Re: Prepared statements and their surprising performance implications

#29

Post by hinkley » Mon, Feb 24, 2020, 4:14 PM UTC

toast0 wrote: post #8

hinkley wrote: post #7

Earlier quoted context omitted.

dantillberg has the right of it. We were doing some complicated fitness tests on our data to return appropriate rows to the user. Actual: SELECT * FROM things WHERE ... AND columnA>? AND columnB Expected: SELECT * FROM things WHERE ... AND columnA>? AND columnB These sorts of mistakes tend to sneak back into projects that previously had this handled, so you do have to watch for regressions. (But that's not what happe…

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 mind:

One of the ways we reduce people’s reliance on string concatenation is by providing interpolation. And I think that at least in some languages you can substitute your own logic for interpolation. You might be able to encourage bind variables that way.

    SELECT * FROM users 
        WHERE last_name=${surname} AND age>${legalDrinkingAge(state)} 
        ORDER BY birthdate ASC

Re: Prepared statements and their surprising performance implications

#30

Post by anarazel » Mon, Feb 24, 2020, 6:56 PM UTC

throwdbaaway wrote: post #26

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 significant:

pgbench -M simple -P1 -n -S -T 10 -j72 -c72

...

tps = 585582.911949 (excluding connections establishing)

vs

pgbench -M prepared -P1 -n -S -T 10 -j72 -c72

...

tps = 1010152.078646 (excluding connections establishing)

For more complicated queries the difference can be considerably bigger.