November 4, 2008 at 3:26 pm
I have a join between 2 tables. They are joined on a field RWA_ID which is CHAR in one table and DECIMAL in the other. Both tables have about 1.6 million records. Is there a performance consideration in which way I convert them in the join ?? Currently I'm doing this:
[font="Courier New"]INSERT INTO #CIFHold
SELECT c.call_id, c.RWA_ID, l.ProducerID, l.StateCd
FROM Call_Detail c
JOIN Leads l on convert(char(20), c.RWA_ID) = l.RWA_ID
WHERE c.Call_ID = @Call_ID[/font]
November 5, 2008 at 2:57 am
I couldn't be sure without testing somewhat but my gut instinct tells me that SQL will compare two decimals quicker (eg where Var1 = Var2) than two chars hence I would covert char to decimal.
November 5, 2008 at 8:45 am
Interesting question.
You may have to test both approaches. you not only have the comparison time, you also have the convert time. Converting a char to a decimal will require the engine to do a validation, while converting from decimal to char will not.
Tell us what you find out.
If you do this often, consider setting up a second column on the table with the character column, and use a trigger to populate it.
The more you are prepared, the less you need it.
November 5, 2008 at 3:14 pm
Yes, it is done quite a bit. I've just inherited some databases that have the ID field in the 2 different formats in various tables ..... screwy. The 2nd field is an interesting idea
November 6, 2008 at 7:48 am
If you cannot use a second column, when you do the match, have the numeric value on the left side of the where conditional:
where mynumeric = myformatlogic(my string value)
If it is the other way around, the optimizer may just go for a full scan, which it might anyway.
The more you are prepared, the less you need it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply