Prepared statements and their surprising performance implications
1–10 of 37 posts
Re: Prepared statements and their surprising performance implications
#2Prepared 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 the most popular database mapping tool in NodeJS did not use Prepared Statements. Due to an accident of their compatibility matrix it was going to be tricky to achieve, and so they had kicked that can down the road several times.
But in the meantime they had done just heaps of work on input validation to prevent SQL injection attacks. They were confident they had no attack surface. Bunches of test cases to prove it.
It was sort of impressive but also really sad, because creating Prepared Statements with bound variables gets all of that for free, and they're much faster on some DBs. The main exception is for freeform text search, but we seem to have pushed that problem to external tools anyway (I'm not sure the two facts are unrelated...)
Re: Prepared statements and their surprising performance implications
#3Long 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 many queries could be in-flight at once. Or rather, how many unique queries. If you used Prepared Statements, it would be happy to run as many configurations in parallel as you wanted. We were using Prepared Statements, so that shouldn't have been a problem. Right? ... Right?
And then he showed me the log of queries.
Less than 2 years earlier (oh, you can already tell this is going somewhere, can't you), a newer team mate was trying to scrub all of the query string arithmetic from our code. Someone had settled on some sort of Builder pattern, but this guy was getting stuck. The guy who had instigated this had to tag out for reasons that I cannot recall, so he asked me to sub in. I got a little briefing, then we went through the problems and I sorted him out, and then I very carefully explained to him what we expected of this code.
If you're gonna bind variables, bind all of them. Solving part of a combinatorics problem isn't solving it, solving part of a SQL injection problem isn't solving it. Bind. Every. Variable. Got it? Got it. You good? I'm good.
And then, dear friends, I made a fateful mistake: I went back to my other tasks and fires, and the other engineer oversaw the rest of the work and the code review.
18+ months later, I'm staring at a bunch of nearly identical queries in the Oracle query cache that have half a dozen bind variables and then a couple of distinct AND clause near the end. Plot twist: he was now my boss. The answer to 'why is our fancy hardware slow?' was, "because of you, Mike, because of you." Fuck a duck.
I guess maybe the reason he was so awful at giving orders is that he never learned how to follow them.
Re: Prepared statements and their surprising performance implications
#4I 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.
Re: Prepared statements and their surprising performance implications
#5Long 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…
I'm not sure i follow the punchline. If the queries need different where clauses then they aren't interchangeable. Or are you saying they were semanticly the same and only syntacticly different?
Re: Prepared statements and their surprising performance implications
#6Long 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…
I'm not sure i follow the punchline. If the queries need different where clauses then they aren't interchangeable. Or are you saying they were semanticly the same and only syntacticly different?
My read of GP's story is that the prepared queries varied by supplying different hard-coded parameters, in addition to some bound variables. For example, the WHERE clause might be "WHERE first=? AND last='Smith'". The 'first' argument is a bound variable, while the 'last' argument is part of the prepared query string.
Re: Prepared statements and their surprising performance implications
#7Long 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…
I'm not sure i follow the punchline. If the queries need different where clauses then they aren't interchangeable. Or are you saying they were semanticly the same and only syntacticly different?
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 columnBExpected:
SELECT * FROM things WHERE ... AND columnA>? AND columnBThese 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 happened in this case, if anyone is wondering.)
I don't know if there are any good ways to audit for these sorts of problems these days. At one point I joked that I was going to write my own database library that only accepted Prepared Statements and nothing else, but barring a full SQL query parser, I'm not entirely sure how I thought I was going to prevent someone from doing that.
Re: Prepared statements and their surprising performance implications
#8Earlier quoted context omitted.
I'm not sure i follow the punchline. If the queries need different where clauses then they aren't interchangeable. Or are you saying they were semanticly the same and only syntacticly different?
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 reasonable subset and have confidence that you would have false positives, but no false negatives.
Re: Prepared statements and their surprising performance implications
#9Oh my goodness, you're my saviour. I spent a whole day last week debugging what looked like a caching issue with Ecto + Postgres. When the application ran a query, it'd eventually slow down to 600ms.. when I ran it in psql, always 10ms. My solution was to convert the query to SQL and run it manually which bypassed the plan caching Ecto does.
THANK YOU. I will absolutely be testing this tomorrow.
Re: Prepared statements and their surprising performance implications
#10Long 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 different things), how they work depends mostly on your client library. For example, some libraries do rewrite placeholders with string pasting, so verify that. YMMV.
Source: I am a DBA.