4 ms·

> Reason for most of these is the optimiser. Well, optimiser as currently implemented, tying only one plan to each uniqe SQL text. There is nothing, in princi

> Reason for most of these is the optimiser. Well, optimiser as currently implemented, tying only one plan to each uniqe SQL text. There is nothing, in principle, preventing an implementation that produces multiple plans based on the parameter values. This would be useful not just for "advanced" parametrization I mentioned above, but also for simpler cases such as when a parameter being NULL could be handled via a different access path compared to non-NULL. > if t1 and t2 are structurally identical, you should not usually be splitting them into 2 tables Depends on constraints. For example, you might have two structurally same tables that reference (or are referenced by) different tables.

throwaway_pdp09 6y ago
> ... nothing, ... preventing an implementation that produces multiple plans based on the parameter values I'm afraid there is - time and space. Optimising is a very expensive process that can take well over a second to optimise a complex query on MSSQL (and that's not because the best plan is always found; it has a timout after which the best plan so far found is returned. Complex queries will hit the timeout and a suboptimal plan will most likely be returned), and the compiled query plan can take megabytes. Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. > you might have two structurally same tables that reference (or are referenced by) different tables Umm. I'd say if their semantics were different enough that they be 2 distinct tables then their identicallity is coincidental. I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).
- branko_d 6y ago
  > I'm afraid there is - time and space. Sure, I'm well aware of that. I did say "in principle", after all. However, we are generating multiple plans already by changing the SQL text (by concatenating-in the table names, SELECT list, ORDER BY and so on). There is currently no good solution in SQL even for parametrizing different "shapes" of WHERE, so we use dynamic SQL for that, generating even more plans. There is some low-hanging fruit here. > Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. You don't have to do it in advance, though. Today, when you change the text of the SELECT list, you generate a new plan anyway. Parametrized SELECT list would be no different. > I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common). Most junction tables have essentially identical structure, but different foreign keys. This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object... But it doesn't really matter whether all fields are identical, only those that are actually used by the given SQL statement. In a hypothetical future SQL, you could implement your algorithm generically and then apply it on any table or view that is "compatible enough", even if it has some extra fields that your SQL statement doesn't use. In today's SQL, unfortunately, you are condemned to repeating the (essentially) same code.
  - throwaway_pdp09 6y ago
    Your point about generating query plans anyway if using dynamic sql is well taken. I can't argue - but see below. > There is some low-hanging fruit here. syntactically perhaps. But it is bloody expensive to generate new plans. It's quite reasonable for a query to take one or two or even three orders of magnitude more CPU for plan generation than for actually running it. Neither dynamic sql nor parameterisation will deal with that (that I can see). I think we're stuck with expensive repeated query planning. > Most junction tables have essentially identical structure Yes but they are used only to link 2 tables together (I call these join tables because they're just used for joining 2 tables). They aren't used in any other context > This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object... Maybe. I'd need to sit down with you and talk it over (not happening sadly!) I think parameterised SQL is necessary but also a wide open door to clueless programmers massively increasing the CPU load and I've seen what cluelessness can do to perforemance in an exists-right-now nonparameterised RDBMS. OTOH I've never liked the idea that a tool's power should be reduced to avoid numpties doing damage. You modify the numpty, not the tool.
    - branko_d 6y ago
      > I think we're stuck with expensive repeated query planning. I agree with that (sadly). I'd like to see more expressive SQL, but I'm not saying it will make the situation with planning any better. I'd argue it should not make it much worse either, if done right.

throwaway_pdp09 6y ago

> ... nothing, ... preventing an implementation that produces multiple plans based on the parameter values I'm afraid there is - time and space. Optimising is a very expensive process that can take well over a second to optimise a complex query on MSSQL (and that's not because the best plan is always found; it has a timout after which the best plan so far found is returned. Complex queries will hit the timeout and a suboptimal plan will most likely be returned), and the compiled query plan can take megabytes. Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. > you might have two structurally same tables that reference (or are referenced by) different tables Umm. I'd say if their semantics were different enough that they be 2 distinct tables then their identicallity is coincidental. I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common).

branko_d 6y ago
> I'm afraid there is - time and space. Sure, I'm well aware of that. I did say "in principle", after all. However, we are generating multiple plans already by changing the SQL text (by concatenating-in the table names, SELECT list, ORDER BY and so on). There is currently no good solution in SQL even for parametrizing different "shapes" of WHERE, so we use dynamic SQL for that, generating even more plans. There is some low-hanging fruit here. > Suppose you want to parameterise by fields selected. That's a combination, which involves factorial. tl;dr that grows fast. You don't have to do it in advance, though. Today, when you change the text of the SELECT list, you generate a new plan anyway. Parametrized SELECT list would be no different. > I'd have to see some actual cases before arguing further (edit: though I have come across this, so maybe, but it's not IME common). Most junction tables have essentially identical structure, but different foreign keys. This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object... But it doesn't really matter whether all fields are identical, only those that are actually used by the given SQL statement. In a hypothetical future SQL, you could implement your algorithm generically and then apply it on any table or view that is "compatible enough", even if it has some extra fields that your SQL statement doesn't use. In today's SQL, unfortunately, you are condemned to repeating the (essentially) same code.
- throwaway_pdp09 6y ago
  Your point about generating query plans anyway if using dynamic sql is well taken. I can't argue - but see below. > There is some low-hanging fruit here. syntactically perhaps. But it is bloody expensive to generate new plans. It's quite reasonable for a query to take one or two or even three orders of magnitude more CPU for plan generation than for actually running it. Neither dynamic sql nor parameterisation will deal with that (that I can see). I think we're stuck with expensive repeated query planning. > Most junction tables have essentially identical structure Yes but they are used only to link 2 tables together (I call these join tables because they're just used for joining 2 tables). They aren't used in any other context > This is also pretty common when implementing things like "tags" or "attributes" that apply to more than one kind of object... Maybe. I'd need to sit down with you and talk it over (not happening sadly!) I think parameterised SQL is necessary but also a wide open door to clueless programmers massively increasing the CPU load and I've seen what cluelessness can do to perforemance in an exists-right-now nonparameterised RDBMS. OTOH I've never liked the idea that a tool's power should be reduced to avoid numpties doing damage. You modify the numpty, not the tool.
  - branko_d 6y ago
    > I think we're stuck with expensive repeated query planning. I agree with that (sadly). I'd like to see more expressive SQL, but I'm not saying it will make the situation with planning any better. I'd argue it should not make it much worse either, if done right.