Life of a Sql Query
numeracy.co
Life of a Sql Query
1–9 of 9 posts
Re: Life of a Sql Query
#2I'm a bit sad this article didn't go into the life cycle of prepared queries with bound arguments at all, as that gets a lot more complicated and (given the way we should all be writing queries) is much more applicable.
Re: Life of a Sql Query
#3I'm a bit sad this article didn't go into the life cycle of prepared queries with bound arguments at all, as that gets a lot more complicated and (given the way we should all be writing queries) is much more applicable.
Is it a lot more complicated? Conceptually, prepared statements are ASTs with "holes" in them, right?
Re: Life of a Sql Query
#4I'm a bit sad this article didn't go into the life cycle of prepared queries with bound arguments at all, as that gets a lot more complicated and (given the way we should all be writing queries) is much more applicable.
Is it a lot more complicated? Conceptually, prepared statements are ASTs with "holes" in them, right?
Yes, and some people use prepared statements as one offs where a prepared statement will be registered and then some values will be sent to fulfill those "holes" and then we're done with the query, but prepared statements are reusable and pre-cacheable so it'd be interesting to know how communication from an application to a DBMS notes which statement it is talking about and what parts of the query plan a DBMS will reuse when re-executing the query.
It's quite interesting stuff, but because it's almost all details about the weeds a lot of abstract overviews of how DBMSes process queries skip over it as "implementation details" this particular article goes pretty deep into those details for regular queries so it'd be neat to see those details for prepared statements.
Re: Life of a Sql Query
#5This is clearly a primer on SQL and meant for those looking for an introduction. If you're one of those people, then you would also be interested in this deck on the evolution of SQL. https://www.slideshare.net/MarkusWinand/modern-sql
Re: Life of a Sql Query
#6Earlier quoted context omitted.
Is it a lot more complicated? Conceptually, prepared statements are ASTs with "holes" in them, right?
Yes, and some people use prepared statements as one offs where a prepared statement will be registered and then some values will be sent to fulfill those "holes" and then we're done with the query, but prepared statements are reusable and pre-cacheable so it'd be interesting to know how communication from an application to a DBMS notes which statement it is talking about and what parts of the query plan a DBMS will r…
Hi author here. I actually just learned this from you. https://www.postgresql.org/docs/9.6/sql-prepare.html will be my lunchtime reading. Thanks!
Re: Life of a Sql Query
#7I'm a bit sad this article didn't go into the life cycle of prepared queries with bound arguments at all, as that gets a lot more complicated and (given the way we should all be writing queries) is much more applicable.
Is it a lot more complicated? Conceptually, prepared statements are ASTs with "holes" in them, right?
They can also have a cached query plan connected to them.
Re: Life of a Sql Query
#8Earlier quoted context omitted.
Yes, and some people use prepared statements as one offs where a prepared statement will be registered and then some values will be sent to fulfill those "holes" and then we're done with the query, but prepared statements are reusable and pre-cacheable so it'd be interesting to know how communication from an application to a DBMS notes which statement it is talking about and what parts of the query plan a DBMS will r…
Hi author here. I actually just learned this from you. https://www.postgresql.org/docs/9.6/sql-prepare.html will be my lunchtime reading. Thanks!
Yeah, parameter sniffing is also a concern and a thing you have to manage on the SQL Server side - it's led to probably the most venerable article on the topic in that community - http://www.sommarskog.se/query-plan-mysteries.html
Re: Life of a Sql Query
#9Earlier quoted context omitted.
Yes, and some people use prepared statements as one offs where a prepared statement will be registered and then some values will be sent to fulfill those "holes" and then we're done with the query, but prepared statements are reusable and pre-cacheable so it'd be interesting to know how communication from an application to a DBMS notes which statement it is talking about and what parts of the query plan a DBMS will r…
Hi author here. I actually just learned this from you. https://www.postgresql.org/docs/9.6/sql-prepare.html will be my lunchtime reading. Thanks!
Neato! If you investigate and find stuff I'd look forward to seeing your analysis, the work you did on standard statements is quite interesting.