5 ms·

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

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.
    - jasonkester 17y ago
      You 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 ago
        i 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.

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.
  - jasonkester 17y ago
    You 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 ago
      i 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.