Join on different data types

Question

Post reply

Join on different data types

devorlask

SSC Veteran

Points: 249
More actions
June 11, 2014 at 6:54 am

#308073

Long story short, we have a query that joins column A int which is an int onto column B with contains only int's but was created as a varchar and can't be changed to an int at the moment.
casting column a as a varchar in the ON of the join to left join seems to void the index altogether and the query just runs for every.
We are talking a few hundred million rows of data in each table.
temp solution is select into a #Hash table as correct data type and index then use the #Hash table in the join.
Wondering if anyone has come across this before and has a good solution? aware the obvious answer is change the model for the right data type but that's a call i can't make and isn't going to happen.
Been googling but can't find a solution.
cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246980 More actions · Answer 1

Add a computed persisted int column, add index and join on that?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

CREATE TABLE #temp (colint INT, colvarchar VARCHAR(10), colcomputed AS CAST(colvarchar AS INT))

INSERT INTO #temp (colint, colvarchar) SELECT 1, '10'

SELECT * FROM #temp

CREATE INDEX ix_colcomputed ON #temp (colcomputed)

SELECT colcomputed FROM #temp WHERE colcomputed = 10

Heh Phil you're too quick mate!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DouglasH SSCommitted Points: 1669 More actions · Answer 3

DouglasH

SSCommitted

Points: 1669

June 11, 2014 at 7:16 am

#1720733

Indexed view - or is that nuts?

sql-lover SSCoach Points: 18530 More actions · Answer 4

ChrisM@Work (6/11/2014)
CREATE TABLE #temp (colint INT, colvarchar VARCHAR(10), colcomputed AS CAST(colvarchar AS INT)) INSERT INTO #temp (colint, colvarchar) SELECT 1, '10' SELECT * FROM #temp CREATE INDEX ix_colcomputed ON #temp (colcomputed)
SELECT colcomputed FROM #temp WHERE colcomputed = 10
Heh Phil you're too quick mate!

I've never faced this problem at work (yet), but this is a very elegant solution. Not sure about performance, but nice indeed.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

sql-lover (6/11/2014)
ChrisM@Work (6/11/2014)
CREATE TABLE #temp (colint INT, colvarchar VARCHAR(10), colcomputed AS CAST(colvarchar AS INT)) INSERT INTO #temp (colint, colvarchar) SELECT 1, '10' SELECT * FROM #temp CREATE INDEX ix_colcomputed ON #temp (colcomputed)
SELECT colcomputed FROM #temp WHERE colcomputed = 10
Heh Phil you're too quick mate!
I've never faced this problem at work (yet), but this is a very elegant solution. Not sure about performance, but nice indeed.

The plan shows an index seek 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246980 More actions · Answer 6

ChrisM@Work (6/11/2014)
CREATE TABLE #temp (colint INT, colvarchar VARCHAR(10), colcomputed AS CAST(colvarchar AS INT)) INSERT INTO #temp (colint, colvarchar) SELECT 1, '10' SELECT * FROM #temp CREATE INDEX ix_colcomputed ON #temp (colcomputed)
SELECT colcomputed FROM #temp WHERE colcomputed = 10
Heh Phil you're too quick mate!

Haha. I provided only words, you provided the nuts & bolts!

But wouldn't persisted be better for such a large amount of data?

create table #temp

(

colint int

,colvarchar varchar(10)

,colcomputed as cast(colvarchar as int) persisted

)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel