3 ms·

> 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 b

> 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

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.