LukeP LukeP - 5 months ago 23
SQL Question

fastest way to compare two columns with different data types

I have two tables that I need to join over a linked server, but I have a problem with the source data that I am stuck with for now.

The column names that I need to join on are

account_number
and
member_number
respectively.

My issue is that
account_number
is a
varchar(10)
and is always padded with leading zeros, but
member_number
is a
varchar(12)
(don't ask why, the last 2 are never used) but is not padded with leading zeros.

If we say that
account_number
is in
A
and
member_number
is in
B
, I have come up with the following solutions:

SELECT * FROM
A INNER JOIN B
ON CAST(A.account_number AS BIGINT) = CAST(B.member_number AS BIGINT)


and

SELECT * FROM
A INNER JOIN B
ON A.account_number = RIGHT('0000000000'+B.member_number, 10)


The problem is that they are super slow!

It must be the fact that the functions are forcing table scans, but I'm not sure what else to do about this. Is there any way to do this comparison that is faster? Maybe with some variation of
like
or something?

Answer

The fastest way is to create a computed column so they are of the same types and then build an index on that column. Something like:

alter table b add account_number as ( RIGHT('0000000000'+B.member_number, 10) );

create index b_acount_number on b(account_number);

Then run the query as:

SELECT *
FROM A INNER JOIN
     B 
     ON A.account_number = b.account_number;

That is probably the fastest you can get.

Comments