All Forums Database
mpatil 5 posts Joined 11/11
10 Apr 2012
Datatype mismatch in Joining Columns

Hello all,

Is there any performance issue if I use columns to join and both the column's datatypes are not same.

For ex. Joining float column with Integer column.

10 Apr 2012

Yes, There will be performance issue because of implicity datatype conversion.

mpatil 5 posts Joined 11/11
12 Apr 2012

Thanks Hariprasath for replying..

Could you please elaborate on this ?

ulrich 816 posts Joined 09/09
13 Apr 2012

The internal representation of float and integer is different. 

So a plain bit comparison - and this is how it always end up - would be always false even if the values stored are the same. Therefore TD will convert one of the columns to the other format to be able to check if the values stored are the same. You should see this in the explain as well.

This is an additional calculation which need resources and therefore might impact query performance. 

This will be espacially true if both columns are PIs on the two tables which are joined. If both PIs would have the same data type (data type class) a join would not need a redistribution and a merge join could be done. But if the data types are different at least one table would need to be redistributed after the implicit conversion.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mpatil 5 posts Joined 11/11
15 Apr 2012

Thank you for explaination.

It really helps.. :)

You must sign in to leave a comment.