3 ms·

how does a sql engine cache [results and plans given] queries? i would naively assume that there are two caches (ignoring compiled queries - see comment below)

how does a sql engine cache [results and plans given] queries? i would naively assume that there are two caches (ignoring compiled queries - see comment below). one, up front, is a literal cache: repeat a query text exactly and bang, you get the previous results. but then it would make sense for a second cache based on the structure of the ast after parsing a query. if the structure is the same, but some parameters have changed, then it should be possible to re-use much of a previous [plan]. if i understand the article correctly, it's saying that this doesn't happen. the only way to get this is to use a "compiled query" that you then call with a variety of parameters. is that right? if so, why? seems like my second level cache wouldn't be that hard to implement... [edit: i realise this wouldn't avoid the needless passing from linq to text and back to ast, and i agree with the general tone of the article that you need to justify worrying about optimisations - i'm just curious about how sql engines work] [edit2: clarified by explicitly mentioning plans]

raganwald 17y ago
What happens is this: There is something called a "query plan." For trivial queries, it is the same as the query. But for more complex queries it works out how to use indices, joins, and so forth. It's kind of a lower-level query language. Figuring out the query plan can be costly for complex queries. The database maintains a cache of query plans, so when you give it the same query, you get the query plan back right away and the database runs it. There are data caches for other reasons, but the problem Jeff is describing is that the C# VM must interpret all the steps of building a SQL query in text every time it's run. The database may have the query plan cached, but even though the code never changes, building the SQL query string happens every time.
- andrewcooke 17y ago
  i thought he was saying more than that. i thought that he was saying that a new plan is always constructed. what i was saying is that a new plan only needs to be constructed for "sufficiently different" queries, where "sufficiently different" can be something much more stringent than the hash of the literal text. if we're only worrying about the parsing time (as opposed to the time to build and optimize a plan) then it's even more pointless to worry about this (i'm assuming parsing is significantly faster than actually building a plan). [edit: i'm confused who is who here; have swapped person twice, hope this is correct]
  - raganwald 17y ago
    I think the database is smarter than a simple literal text for query plans. I think it reverse-engineers literals so that if you query WHERE t.initials = 'ac' and then later query WHERE t.initials = 'rb', it re-uses the cached query plan. But the point of the article is that the problem is not in the database, it's over in the CLR which is spending a lot of time composing the SQL text that gets sent to the database.
    - andrewcooke 17y ago
      ah, ok. thanks.
    - raganwald 17y ago
      UPDATE: In Oracle, when you use literals any change to the text results in another hard parse on the database side. However, you can use bind variables instead of literals and it can re-use the query plan even when you are searching for different values. http://www.akadia.com/services/ora_bind_variables.html http://www.akadia.com/services/ora_bind_variables.html The OP is talking about LINQ. I assume that its Oracle implementation is smart enough to use bind variables, but I am not going to look that up right now...
      - andrewcooke 17y ago
        the article gives an example of how to do compiled queries. which strongly implies that "ordinary" linq queries are not compiled as you seem to expect.
    - jasonkester 17y ago
      In the real world, this probably doesn't matter. Literals always go in as parameters, so the query text itself stays the same. (usually it's simply "exec User_GetBySandalCount @sandalCount", 4)
      - andrewcooke 17y ago
        not if linq is constructing the queries, according to the article, which i think is what we were discussing.
- rubyrescue 17y ago
  I know the article is about a MS stack, but this is not true for MySQL. It's text based so even an extra space causes a miss on the query cache.
  - raganwald 17y ago
    What happens in MySQL when the queries are character-for character identical except for literals, e.g WHERE t.foo = 'bar' vs. WHERE t.foo = 'blitz'? update: NVM! http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cac...
    - andrewcooke 17y ago
      From the conclusions: Only identical queries may be serviced from the cache. This includes spacing, text case, etc. Which is disappointing. I will go look at Postgres... and it seems to cache only at a lower level (indices, tables etc).
      - Periodic 17y ago
        If you cache on the level of SQL-statement strings you can skip the transformation into the AST. It's very cheap to quickly hash the string and look it up. If you have a site that is going to use the same queries over and over (e.g. for front-page data or popular categories) then the query will likely be exactly the same and the string hash is cheaper. If you had to build an AST for every single query you're just adding an extra layer of complexity that won't gain you much. The only reason I can think of for a query with different literals in the conditionals to have a different plan would be if a histogram of frequencies was actually kept such that different literals created large differences in result sizes which then get fed into a join and affect the join logic. But that's going to be pretty rare and is a level of optimization that is unnecessary. It's also not going to make a huge difference unless the query is very complex, because the plans for simple queries are... simple.
        andrewcooke 17y ago

raganwald 17y ago

What happens is this: There is something called a "query plan." For trivial queries, it is the same as the query. But for more complex queries it works out how to use indices, joins, and so forth. It's kind of a lower-level query language. Figuring out the query plan can be costly for complex queries. The database maintains a cache of query plans, so when you give it the same query, you get the query plan back right away and the database runs it. There are data caches for other reasons, but the problem Jeff is describing is that the C# VM must interpret all the steps of building a SQL query in text every time it's run. The database may have the query plan cached, but even though the code never changes, building the SQL query string happens every time.

andrewcooke 17y ago
i thought he was saying more than that. i thought that he was saying that a new plan is always constructed. what i was saying is that a new plan only needs to be constructed for "sufficiently different" queries, where "sufficiently different" can be something much more stringent than the hash of the literal text. if we're only worrying about the parsing time (as opposed to the time to build and optimize a plan) then it's even more pointless to worry about this (i'm assuming parsing is significantly faster than actually building a plan). [edit: i'm confused who is who here; have swapped person twice, hope this is correct]
- raganwald 17y ago
  I think the database is smarter than a simple literal text for query plans. I think it reverse-engineers literals so that if you query WHERE t.initials = 'ac' and then later query WHERE t.initials = 'rb', it re-uses the cached query plan. But the point of the article is that the problem is not in the database, it's over in the CLR which is spending a lot of time composing the SQL text that gets sent to the database.
  - andrewcooke 17y ago
    ah, ok. thanks.
  - raganwald 17y ago
    UPDATE: In Oracle, when you use literals any change to the text results in another hard parse on the database side. However, you can use bind variables instead of literals and it can re-use the query plan even when you are searching for different values. http://www.akadia.com/services/ora_bind_variables.html http://www.akadia.com/services/ora_bind_variables.html The OP is talking about LINQ. I assume that its Oracle implementation is smart enough to use bind variables, but I am not going to look that up right now...
    - andrewcooke 17y ago
      the article gives an example of how to do compiled queries. which strongly implies that "ordinary" linq queries are not compiled as you seem to expect.
  - jasonkester 17y ago
    In the real world, this probably doesn't matter. Literals always go in as parameters, so the query text itself stays the same. (usually it's simply "exec User_GetBySandalCount @sandalCount", 4)
    - andrewcooke 17y ago
      not if linq is constructing the queries, according to the article, which i think is what we were discussing.
rubyrescue 17y ago
I know the article is about a MS stack, but this is not true for MySQL. It's text based so even an extra space causes a miss on the query cache.
- raganwald 17y ago
  What happens in MySQL when the queries are character-for character identical except for literals, e.g WHERE t.foo = 'bar' vs. WHERE t.foo = 'blitz'? update: NVM! http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cac...
  - andrewcooke 17y ago
    From the conclusions: Only identical queries may be serviced from the cache. This includes spacing, text case, etc. Which is disappointing. I will go look at Postgres... and it seems to cache only at a lower level (indices, tables etc).
    - Periodic 17y ago
      If you cache on the level of SQL-statement strings you can skip the transformation into the AST. It's very cheap to quickly hash the string and look it up. If you have a site that is going to use the same queries over and over (e.g. for front-page data or popular categories) then the query will likely be exactly the same and the string hash is cheaper. If you had to build an AST for every single query you're just adding an extra layer of complexity that won't gain you much. The only reason I can think of for a query with different literals in the conditionals to have a different plan would be if a histogram of frequencies was actually kept such that different literals created large differences in result sizes which then get fed into a join and affect the join logic. But that's going to be pretty rare and is a level of optimization that is unnecessary. It's also not going to make a huge difference unless the query is very complex, because the plans for simple queries are... simple.
      - andrewcooke 17y ago