6 ms·

optimizer can't inspect the value? pretty dumb optimizer, then.

• wvenable 6mo ago
  Running the optimizer for every execution of the same query is... not very optimal.
  • SigmundA 6mo ago
    It can run it for a range of values: https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/p... Also the simpler and maybe better approach is just make the decision every time as an operation in the plan, attempt the cast if it fails then scan and cast a many times the other way, if it succeeds then use the index, this isn't hard and adds one extra cast attempt on the slow path otherwise it does what everyone has to do manually in their code like this article but transparently. The adaptive join operator does something much more complex: https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver17#adaptive https://learn.microsoft.com/en-us/sql/relational-databases/p...
    • wvenable 6mo ago
      I'm not sure it makes sense to add more checks and another operation to every single query just for the case where the user explicitly mislabels the types. You're going to slow down everything everywhere (slightly) for a pretty obscure case. I suspect, in the long term, this would be a bad choice.
      • SigmundA 6mo ago
        The check is added if it sees a varchar column and nvarchar parameter predicate on it. It currently just does a scan in that situation which orders of magnitude more expensive with a cast for every row vs a single extra cast check on the single parameter value that may avoid all those other casts in a common situation. There is no planning overhead, it's already detecting the situation. The execution overhead is a single extra cast on top of the cast per row, so n+1 vs n with the potential to eliminate n with a very common charset.
• zabzonk 6mo ago
  It's not "the value", it's "the values".