All the Perl that's Practical to Extract and Report
use Perl Log In
jplindstrom (594)
(email not shown publicly)
Journal of jplindstrom (594)
SQL wisdom: don't mix datatypes in joins
Sybase allows a foreign key between two tables to be of almost-the-same datatype. So table a's PK can be NUMERIC(10,0) and table b's column a_id can be INT.
While that may sound practical in certain circumstances, it's a potential performance killer when doing joins on those tables because of the implicit casting.
We just discovered that this was the cause of a major slowdown in a frequent query. Fixing it, the query runtime went from 1.7s to 13ms. Yay!
For comparison, Oracle requires exactly the same datatype on both sides of a FK relationship. At first I thought this was overly fascist, but now it seems like a good thing.
So since Sybase allows potentially stupid mistakes like this I'm inclined to implement a sanity test for this in our build tool so it doesn't happen again. That and code reviews, at least for code from external consultants.
--Larry Wall on s/foo/bar/eieio in <10911@jpl-devvax.JPL.NASA.GOV>
I miss 'domains' in RDBMs (Score:2)
It supported domains, ie named datatypes. As part of the database design you would create a domain for every distinct datatype. Your columns would not be of type 'int' or 'numeric(20,2)' but 'a_identifier' or 'monthly_payment'.
Joins and other comparisons were nearly always between columns with the same domain - if not som