Query is slow even when joining condition columns have indexes on them in SQL Server

Question

I have a query as follows :

select distinct
    b.INSTANCE_ID, b.itemnumber, 
    isnull((a.ITEM_REV + ' ' + a.ITEM_CN), 'Introductory') itemrev, 
    b.bom_itemnumber, b.bom_rev  
into 
    parts_bom_stg2
from 
    PARTS_BOMD a
inner join 
    parts_bom_stg b on a.ITEM_NUMBER = b.itemnumber
                    and b.itemrev = a.ITEM_REV;

Its estimated execution plan is:

Indexes are on item_number and item_rev columns of both tables.

I can not ditch the distinct in the query. I guess its lagging because of this distinct. Does anyone know how I can optimize it. Both tables have almost 5 million rows.

Table structure of table Parts_BOMD:

CREATE TABLE [dbo].[PARTS_BOMD]
(
    [BOM_ID] [varchar](4000) NULL,
    [ITEM_REV] [varchar](80) NULL,
    [ITEM_CN] [varchar](4000) NULL,
    [ITEM_NUMBER] [varchar](80) NULL,
    [ITEMNUMBER] [varchar](4000) NULL,
    [FINDNUM] [varchar](4000) NULL,
    [QTY] [varchar](4000) NULL,
    [ITEMDESCRIPTION] [varchar](4000) NULL,
    [ITEMREV] [varchar](4000) NULL,
    [itemSize] [varchar](4000) NULL,
    [REFDES] [varchar](4000) NULL,
    [BOMText02] [varchar](4000) NULL,
    [itemList21] [varchar](4000) NULL,
    [SUMMARYCOMPLIANCE] [varchar](4000) NULL,
    [BOMMULTITEXT30] [varchar](4000) NULL,
    [BOMNotes] [varchar](4000) NULL,
    [itemList10] [varchar](4000) NULL,
    [BOMList01] [varchar](4000) NULL,
    [BOMList03] [varchar](4000) NULL,
    [BOMList02] [varchar](4000) NULL,
    [itemText22] [varchar](4000) NULL,
    [itemText23] [varchar](4000) NULL,
    [itemLifecyclePhase] [varchar](4000) NULL,
    [ITEMP2MULTILIST05] [varchar](4000) NULL,
    [itemText15] [varchar](4000) NULL,
    [RNUM] [varchar](4000) NULL
)

Structure of Parts_bom_stg:

CREATE TABLE [dbo].[parts_bom_stg]
(
    [INSTANCE_ID] [bigint] NOT NULL,
    [itemnumber] [varchar](80) NULL,
    [itemrev] [varchar](80) NULL,
    [bom_itemnumber] [varchar](max) NULL,
    [bom_rev] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Answer 1

Well this is what I did. Its a bad way around but it did solve my problem:

declare @sql3 varchar(1000)
declare @sql4 varchar(1000)
declare @sql5 varchar(1000)
begin

DECLARE drop_constraint CURSOR FOR  
select distinct itemnumber, itemrev from parts_bom_stg
--print @sql3

OPEN drop_constraint  
FETCH NEXT FROM drop_constraint INTO @sql3,@sql5

WHILE @@FETCH_STATUS = 0          
 BEGIN      

 insert into parts_bom_stg2
select DISTINCT b.INSTANCE_ID,b.itemnumber, isnull((a.ITEM_REV + ' '+ a.ITEM_CN),'Introductory') itemrev, b.bom_itemnumber, b.bom_rev  

from PARTS_BOMD a
inner join parts_bom_stg b
on a.ITEM_NUMBER = b.itemnumber
and b.itemrev = a.ITEM_REV
where b.itemnumber = @sql3
and b.itemrev = @sql5

Print @sql3 + '     ' + @sql5
FETCH NEXT FROM drop_constraint INTO @sql3,@sql5

       -- open cursor to recreate references  
        --cursor in which references are stored     
      end  
     CLOSE drop_constraint          
 DEALLOCATE drop_constraint 
end
GO

This took almost an hour. But it was way lesser than before. The query took 2.5 hours and it still did not work.

Thank you everyone for your support. Really appreciated! :)

Answer 2

if you can't change table structure or data type then atleast explain why you can't ditch distinct. what is your putput with or without distinct,then it will be clear. Why you will select all rows from both the table.where you will show ?

Why don't you use paging ?

Also ITEM_NUMBER and itemrev are nullable in both table,so you can put extra condtion in where clause.

"where ITEM_NUMBER is not null and itemrev is not null"

If you can'd do this and that then you can re-write your query and give a try.

--create table  parts_bom_stg2 ()
insert into parts_bom_stg2
select 
    b.INSTANCE_ID, b.itemnumber, 
    isnull((a.ITEM_REV + ' ' + a.ITEM_CN), 'Introductory') itemrev, 
    b.bom_itemnumber, b.bom_rev  
from 
    PARTS_BOMD a
inner join 
    parts_bom_stg b on a.ITEM_NUMBER = b.itemnumber
 and b.itemrev = a.ITEM_REV
where not exists(select INSTANCE_ID from parts_bom_stg2 c 
where c.INSTANCE_ID=b.INSTANCE_ID and b.itemnumber=c.itemnumber [write the where condition properly here])