[MSSQL] OPENQUERY - BETWEEN 성능??? (Feat. ORACLE)

MSSQL에서  ORACLE 서버를 링크드서버 설정하여, 프로시저에서 OPENQUERY를 사용하는데,,
계속 잘 되던 쿼리인데,, 갑자기 되지않는다..성능문제인지,,,

BETWEEN에서 아래와 같이 하면 안되는데,,

SUBL_DATE BETWEEN TO_CHAR(SYSDATE-7,''YYYYMMDD'') AND TO_CHAR(SYSDATE,''YYYYMMDD'')

아래와 같이 직접입력을 해주면 조회가 됨;;;;

SUBL_DATE BETWEEN ''20220808'' and ''20220812'' ')

---

■ 안되는 쿼리 (기존쿼리)
 - 프로시저에서 일반 쿼리로 실행되도록 처리

INSERT INTO CZ_KIDV_OUTPUT_PARCEL
	(
		SUBL_DATE, TAX_NO, SUBL_NO, ORDER_NO, RCVR_NM, RCVR_TEL_NO, 
		RCVR_ZIP_NO, RCVR_ADDR, RCVR_DETAIL_ADDR, INVC_NO, BOX_QTY, GDS_NM, REG_DATE, REMARK
	)
	SELECT  SUBL_DATE, TAX_NO, SUBL_NO, ORDER_NO, RCVR_NM, RCVR_TEL_NO, 
			RCVR_ZIP_NO, RCVR_ADDR, RCVR_DETAIL_ADDR, INVC_NO, BOX_QTY, GDS_NM, REG_DATE, REMARK
	FROM OPENQUERY(MWMS, 'SELECT * FROM KIDV_OUTPUT_PARCEL A WHERE TAX_NO=''0443'' AND SUBL_DATE BETWEEN TO_CHAR(SYSDATE-7,''YYYYMMDD'') AND TO_CHAR(SYSDATE,''YYYYMMDD'') ') A 
	WHERE	NOT EXISTS (SELECT 1 FROM CZ_KIDV_OUTPUT_PARCEL Z WITH (NOLOCK)
						WHERE	Z.SUBL_DATE		= A.SUBL_DATE
						AND		Z.TAX_NO		= A.TAX_NO
						AND		Z.SUBL_NO		= A.SUBL_NO
						AND		Z.INVC_NO		= A.INVC_NO)

■ 되는 쿼리
 - 쿼리 문자열 선언하여, EXEC 로 실행하도록 변경

	DECLARE @V_SQL NVARCHAR(MAX)
		, @P_DT_START CHAR(8)
		, @P_DT_END CHAR(8)

	SET @P_DT_START = CONVERT(CHAR(8), GETDATE(), 112)
	SET @P_DT_END = CONVERT(CHAR(8), DATEADD(DAY, -7, GETDATE()), 112)

		SET @V_SQL = '
					INSERT INTO CZ_KIDV_OUTPUT_PARCEL
					(
						SUBL_DATE, TAX_NO, SUBL_NO, ORDER_NO, RCVR_NM, RCVR_TEL_NO, 
						RCVR_ZIP_NO, RCVR_ADDR, RCVR_DETAIL_ADDR, INVC_NO, BOX_QTY, GDS_NM, REG_DATE, REMARK
					)
					SELECT  SUBL_DATE, TAX_NO, SUBL_NO, ORDER_NO, RCVR_NM, RCVR_TEL_NO, 
							RCVR_ZIP_NO, RCVR_ADDR, RCVR_DETAIL_ADDR, INVC_NO, BOX_QTY, GDS_NM, REG_DATE, REMARK
					FROM OPENQUERY(MWMS, ''SELECT * FROM KIDV_OUTPUT_PARCEL A WHERE TAX_NO=''''0443'''' AND SUBL_DATE BETWEEN ''''' + @P_DT_START + ''''' AND ''''' + @P_DT_END + ''''' '') A 
					WHERE	NOT EXISTS (SELECT 1 FROM CZ_KIDV_OUTPUT_PARCEL Z WITH (NOLOCK)
										WHERE	Z.SUBL_DATE		= A.SUBL_DATE
										AND		Z.TAX_NO		= A.TAX_NO
										AND		Z.SUBL_NO		= A.SUBL_NO
										AND		Z.INVC_NO		= A.INVC_NO)
					'
	EXEC (@V_SQL)

끝~