Hi.
I have used CTE extensively in my scripts and just lately I found that a new script is really slow to return a result set. I have been really dumbfounded on how to further optimize as I have found that CTE is the best method in querying multiple tables with related data. Because it kinda gets really complicated and fuzzy when using join types.
Here is my sample script:
ALTER PROCEDURE [dbo].[xxx]
@DTB AS DATETIME,
@DTE AS DATETIME
AS
BEGIN
WITH CTE AS
(
SELECT H.DOCID, C.NAME, NSDOCNO, EMDOCNO, H.DOCDT, LINEID, D.MODELID, M.MODEL, D.QTY, D.ID
FROM _NSPOHDR H
JOIN COSTCTR C ON H.SHPCOSTID = C.COSTID
JOIN _NSPODTL D ON H.DOCID = D.DOCID
JOIN MODEL M ON D.MODELID = M.MODELID
WHERE DOCDT BETWEEN @DTB AND @DTE
),
CTE2 AS
(
SELECT C.*,
(
SELECT COUNT(RID)
FROM TRANSFERHDR H JOIN TRANSFERDTL D ON H.DOCID = D.DOCID
WHERE H.APPTYPE = 0
AND REPLACE(REPLACE(PO,' ',''),'-','') LIKE REPLACE(REPLACE(C.EMDOCNO,' ',''),'-','')
AND MODELID = C.MODELID
) AS SERVED,
(
SELECT COUNT(DTLID)
FROM INVDTL D JOIN INVHDR H ON D.DOCID = H.DOCID
WHERE H.APPTYPE = 1
AND REFDOCID IN
(
SELECT DOCID
FROM TRANSFERHDR H
WHERE REPLACE(REPLACE(PO,' ',''),'-','') LIKE REPLACE(REPLACE(C.EMDOCNO,' ',''),'-','')
AND MODELID = C.MODELID
)
AND MODELID = C.MODELID
) AS RCVD
FROM CTE C
)
SELECT C.*, (QTY - SERVED) AS BALANCE, (SERVED - RCVD) AS TRANSIT
FROM CTE2 C
END
Hope you could help me out. Thanks a lot in advance :)
What I have tried:
Tried using joins and always get mixed results that doesn't satisfy the output needed for my project.
Cursor is is also not viable.