4 ms·
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
by 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 agoi 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 agoI 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 agoah, ok. thanks.
- raganwald 17y agoUPDATE: 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 agothe 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 agoIn 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 agonot if linq is constructing the queries, according to the article, which i think is what we were discussing.
- jasonkester 17y agoYou started this thread by asking about SQL Server itself (and the way it handles query plans in particular). That's what we've been talking about ever since. Not Linq. You might want to go back and read the responses to your question again to make sure you've got your context right.
- andrewcooke 17y agoi see your point, and it was wrong of me to describe the context in that way. on the other hand, your previous statement was qualified with "in the real world", and to some extent the "real world" includes people using linq. having said that, i agree that the consensus - if i haven't misunderstood something else - seems to be that sql engines don't do what i expected because it's assumed that people will use compiled statements. i don't use linq myself so am having to infer a lot from the article, but at some point i plan to check django and sqlalchemy to see when they use precompiled statements and when not.
- rubyrescue 17y agoI 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 agoWhat 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 agoFrom 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 agoIf 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