Sql server efficiency--query taking too long for results

See more:

SQL

server

I have created a query that inserts records into a [MatchingRowsOutput-RU] Table by joining an [SWBOM] table and a [Walls Routing Table].
In the first part of the query its gathers all the records based on an = condition using an inner join. In the second part, it gathers all the non exact records. On the inner join for that, the IN condition had to be used which is really slowing down the retrieval. It takes about 1.5 min to run.

I would like to optimize this query for better result times. Being quite new to sql or programming for that matter, I am not familiar with what syntax I could use to replace this IN condition. I had to use this IN condition because it is expecting many records to be returned.

Any help is greatly appreciated.

Below is the code I have written so far.

What I have tried:

Insert into [SSIS-POC].dbo.[MatchingRowsOutput-RU]

    (cprj, PartNumber, SWITEM, QTY, mitm, opno, tano, cwoc, mcno, sutm, rutm, prte, mnoc, mcoc, olap, exin, subr, indt, exdt, bfls, qpnt, nnts, desn, txta)
SELECT
 PartList.cprj,
 PartList.PartNumber,
 PartList.SWITEM,
 PartList.QTY,
 RUMaster.mitm,
 RUMaster.opno,
 RUMaster.tano,
 RUMaster.cwoc,
 RUMaster.mcno,
 RUMaster.sutm,
 RUMaster.rutm,
 RUMaster.prte,
 RUMaster.mnoc,
 RUMaster.mcoc,
 RUMaster.olap,
 RUMaster.exin,
 RUMaster.subr,
 RUMaster.indt,
 RUMaster.exdt,
 RUMaster.bfls,
 RUMaster.qpnt,
 RUMaster.nnts,
 RUMaster.desn,
 RUMaster.txta
 
 

 FROM 
    [SSIS-POC].dbo.SWBOM as PartList
INNER JOIN
    ItemMasterRoutingPOC.dbo.[Walls Routing Table] AS RUMaster ON RUMaster.mitm = PartList.PartNumber 

UNION ALL

SELECT 
 PartList.cprj,
 PartList.PartNumber,
 PartList.SWITEM,
 PartList.QTY,
 RUMaster.mitm,
 RUMaster.opno,
 RUMaster.tano,
 RUMaster.cwoc,
 RUMaster.mcno,
 RUMaster.sutm,
 RUMaster.rutm,
 RUMaster.prte,
 RUMaster.mnoc,
 RUMaster.mcoc,
 RUMaster.olap,
 RUMaster.exin,
 RUMaster.subr,
 RUMaster.indt,
 RUMaster.exdt,
 RUMaster.bfls,
 RUMaster.qpnt,
 RUMaster.nnts,
 RUMaster.desn,
 RUMaster.txta
 
FROM 
    [SSIS-POC].dbo.SWBOM as PartList

INNER JOIN
ItemMasterRoutingPOC.dbo.[Walls Routing Table] AS RUMaster ON 

RUMaster.mitm IN  --**********PROBLEM IS HERE WITH THE IN CONDITION

	(SELECT mitm FROM ItemMasterRoutingPOC.dbo.[Walls Routing Table]
	
	
	WHERE mitm LIKE (LEFT(PartList.PartNumber,8) + '-%')
	--WHERE mitm = PartList.itlu

)
LEFT JOIN
 
(    SELECT
        PartList.PartNumber as FoundPartNumber
    FROM 
       [SSIS-POC].dbo.SWBOM as PartList
    INNER JOIN
        ItemMasterRoutingPOC.dbo.[Walls Routing Table] AS RUMaster ON RUMaster.mitm = PartList.PartNumber 


) IT ON IT.FoundPartNumber = PartList.PartNumber
WHERE
  IT.FoundPartNumber IS NULL;