3 ms·

>footguns Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one. This was fine, unti

>footguns Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one. This was fine, until we added a where clause to a, through multiple joins, base table for both options. This tanked the performance >1000x.[1] If we just returned the value it had basically no impact. We tried solving it with using the result set as a base for a select where we did the filtering. This also resulted in the slow performance. In the end I solved it by wrapping the column in a function call, which solved it. And I still don't know why. My guess is that somehow without the function call, it optimizes it into one query, which results in basically the original case, while a function forces the evaluation of the subquery first. [1]Sub 1sec to over 15 minutes

AmericanChopper 6y ago
Imo, polymorphic associations are one of the key areas that the relational model in general struggles. You can do them in most RDBMS, but they’re always a bit janky. Even when you’re just modelling your schema, you really have to think quite hard about it, and you’ll really struggle to preserve simplicity.
- Akronymus 6y ago
  In this case it was a left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid) I guess that is such an uncommon case that it tripped up the optimizer completely. Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1] [1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.
  - AmericanChopper 6y ago
    > Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. We might have had a similar experience with this. The first time I stumbled across this problem though I was specifically trying to figure out “what is the relational way to implement polymorphism”, so I pretty much lucked into the a rather productive series of google searches.
    - Akronymus 6y ago
      It wasn't strictly polymorphism, but the term you wrote led me to a article[1] where it mentioned "alternative parent". This alone instantly made the problem more understandable for me. [1]http://duhallowgreygeek.com/polymorphic-association-bad-sql-smell/ http://duhallowgreygeek.com/polymorphic-association-bad-sql-...

AmericanChopper 6y ago

Imo, polymorphic associations are one of the key areas that the relational model in general struggles. You can do them in most RDBMS, but they’re always a bit janky. Even when you’re just modelling your schema, you really have to think quite hard about it, and you’ll really struggle to preserve simplicity.

Akronymus 6y ago
In this case it was a left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid) I guess that is such an uncommon case that it tripped up the optimizer completely. Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1] [1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.
- AmericanChopper 6y ago
  > Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it. We might have had a similar experience with this. The first time I stumbled across this problem though I was specifically trying to figure out “what is the relational way to implement polymorphism”, so I pretty much lucked into the a rather productive series of google searches.
  - Akronymus 6y ago
    It wasn't strictly polymorphism, but the term you wrote led me to a article[1] where it mentioned "alternative parent". This alone instantly made the problem more understandable for me. [1]http://duhallowgreygeek.com/polymorphic-association-bad-sql-smell/ http://duhallowgreygeek.com/polymorphic-association-bad-sql-...