INSERT INTO CCP_D2
(CONT_MAS_SID,
COMP_M_SID,
ITM_M_SID)
SELECT DISTINCT CM.CONT_MAS_SID,
CMP_MAS.COMP_M_SID,
IM.ITM_M_SID
FROM CONT_MAS CM
INNER JOIN CFP_C CCT
ON CM.CONT_MAS_SID = CCT.CONT_MAS_SID
AND CM.IN_STATUS <> 'D'
AND CCT.IN_STATUS <> 'D'
INNER JOIN CFP_C_DET CCD
ON CCT.CFP_C_SID = CCD.CFP_C_SID
INNER JOIN COMP_M CMP_MAS
ON CMP_MAS.COMP_M_SID = CCD.COMP_M_SID
AND CMP_MAS.IN_STATUS <> 'D'
INNER JOIN IFP_C IFP_CONT
ON IFP_CONT.CFP_C_SID = CCT.CFP_C_SID
AND IFP_CONT.IN_STATUS <> 'D'
AND CM.CONT_MAS_SID = IFP_CONT.CONT_MAS_SID
INNER JOIN IFP_C_DET ICD
ON IFP_CONT.IFP_C_SID = ICD.IFP_C_SID
INNER JOIN ITM_M IM
ON IM.ITM_M_SID = ICD.ITM_M_SID
AND IM.IN_STATUS <> 'D'
WHERE NOT EXISTS (SELECT 1
FROM CCP_D CD
WHERE CD.CONT_MAS_SID = CM.CONT_MAS_SID
AND CD.COMP_M_SID = CMP_MAS.COMP_M_SID
AND CD.ITM_M_SID = IM.ITM_M_SID)
- The number of records returned = 209519554
I have sql that returns almost 20 million records. I want to insert the result into a table. It works great with fewer records, but when the number of records reaches a million, then it takes about 1 hour 30 minutes, even if I have the appropriate indexes for all the connecting columns.
I tried the following things
- Split into several insert bits using a while loop, but does not work in this case.
- Created all recommended indexes, but performance did not improve as expected.
Note. I do not prefer the section, since I am using the standard version of SQL Server.
Could you suggest any way to improve the performance of this request.