Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jplindstrom (594)

jplindstrom
  (email not shown publicly)

Journal of jplindstrom (594)

Friday April 01, 2005
07:02 AM

SQL wisdom: don't mix datatypes in joins

[ #23971 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Back in the real good old days when I started to work with RDBMses ('86 to be precise) I cut my teeth on Digitals RDB database (later bought by Oracle as part of the great DEC sell-out).

    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