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;