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.
Prepared statements and their surprising performance implications
11–20 of 37 posts
Re: Prepared statements and their surprising performance implications
#12Earlier 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…
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 false and there are a few thousand active entries but tens of millions of inactive ones, the query plan might be quite different for active=true and active=false. (Active=true would use an index, but active=false would make more sense to just sequentially read the whole table, as most rows will be used anyway.)
So if you have very few distinct values, the selection of the value influences the execution plan, and the value doesn't come from an untrusted source, it can be better to not use query parameters.
That is to say, 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately.
Re: Prepared statements and their surprising performance implications
#13Earlier 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…
> 99% of the time you'll want to use query parameters, but rejecting queries which don't use them isn't a solution either, as you do need to avoid them sometimes, unfortunately
This is one of the problems that I have with most SQL databases: they try to do the magic for you and make it hard to see what's going on under the hood. Databases aren't magic, they are just indexes, and there are only so many ways of organizing them. Providing a bit more transparency into how the data is stored and retrieved allow you to write efficient queries without the guesswork.
Re: Prepared statements and their surprising performance implications
#14We experienced a similar orders of magnitude of performance at Hasura by using graphql plan caching and also postgres prepared statements at the same time.
This thread is quite timely as we recently published a post about this here: https://hasura.io/blog/fast-graphql-execution-with-query-cac...
Re: Prepared statements and their surprising performance implications
#15We experienced a similar orders of magnitude of performance at Hasura by using graphql plan caching and also postgres prepared statements at the same time. This thread is quite timely as we recently published a post about this here: https://hasura.io/blog/fast-graphql-execution-with-query-cac...
Making some query optimization expensive (ie parsing the query in GraphQL, parsing SQL statement, calculating resource with HTTP call) to make it flexible but then caching the results to recognize that even with flexible API any (sanely designed) application will have finite actual queries is oft-recurring pattern in software engineering.
I wonder when this kind of pattern starts being taught more extensively than a stupid singleton which is nothing more than a fancy name for a global variable.
Re: Prepared statements and their surprising performance implications
#16I 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.
Re: Prepared statements and their surprising performance implications
#17Long 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…
> 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.
I have worked with plenty production postgres instances that have much much higher loads. As in > 100 (on larger machines obviously). It's pretty easy to have workloads where the hot data set fits mostly into RAM - and if that's the case the read portion of that workload is usually bound by context switches and CPU processing time of the queries.
Far from every workload has enough queries to utilize a machine with plenty cores, but there's also a lot where that's trivial.
Re: Prepared statements and their surprising performance implications
#18Great 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.
Re: Prepared statements and their surprising performance implications
#19I have also been struggling lately with prepared statements and obscure query planning decisions. The developers of the mssql JDBC driver seem to be determined to force the use of prepexec in their driver, which causes high execution times for typical ORM-generated queries:
Re: Prepared statements and their surprising performance implications
#20Earlier 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…
In that case, would you not use a partial index, and kill two birds with one stone?
On that same project we got into putting hints into the queries. I’m sure if push came to shove you could do that for situations like this one and solve the problem. Less portable that way, of course.