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: 245813 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 245813

June 11, 2014 at 7:00 am

#1720727

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 245813 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

)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.