March 3, 2018 at 7:45 pm
This report used to run until i added the CTE to pull MAX Created date from the Broker Feed table.
Now it just runs . . . Can anyone see an issue?
thanks
;WITH CTE_1 AS
(
SELECT
Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) AS MAX_DATE
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN ADHOC.ATS_ESL L
ON L.PART_NUM = Z.COMPOSITE_PART
AND Z.CREATED_DATE <= L.ENTRY_DATE
WHERE
Z.SUB_ORG='FORD'
GROUP BY
Z.COMPOSITE_PART
,Z.HTS_NUMBER
)
SELECT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM AS [ESL Part]
,L.HTS_NUM AS [ESL HTS]
,CAST(L.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
,CTE_1.HTS_NUMBER
,CTE_1.MAX_DATE
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on ZATS */
LEFT join CTE_1
on L.Part_Num = CTE_1.COMPOSITE_PART
/* Joining on Prod_Class */
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON L.[PART_NUM] = Replace(C.[PROD_ID],'-','') AND C.[CTRY_CODE] = 'US' AND C.CLASS_TYPE = 'HS' and C.BUS_UNIT = 'FORD' AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
WHERE (H.Importer = 'FORD'
AND H.Entry_Summary_Date >= GetDate () - 10
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')) --ENTRY <> PROD_CLASS
March 3, 2018 at 7:52 pm
The problem is in the CTE, but i dont see it . . .
SELECT
Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) AS MAX_DATE
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN ADHOC.ATS_ESL L
ON L.PART_NUM = Z.COMPOSITE_PART
AND Z.CREATED_DATE <= L.ENTRY_DATE
WHERE
Z.SUB_ORG='FORD'
GROUP BY
Z.COMPOSITE_PART
,Z.HTS_NUMBER
March 3, 2018 at 9:08 pm
The CTE runs on its own, and the rest of the SELECT runs its own, FAST.
But when i JOIN them, it runs forever . .
I hope that helps
March 3, 2018 at 9:37 pm
ok, maybe this is a "correlated subquery" issue??? i dont know
thanks
March 4, 2018 at 12:31 am
jeffshelix - Saturday, March 3, 2018 9:37 PMok, maybe this is a "correlated subquery" issue??? i dont knowthanks
Can you post the execution plan for the query please?
😎
March 4, 2018 at 4:38 am
Eirikur Eiriksson - Sunday, March 4, 2018 12:31 AMjeffshelix - Saturday, March 3, 2018 9:37 PMok, maybe this is a "correlated subquery" issue??? i dont knowthanks
Can you post the execution plan for the query please?
😎
Also, try this and post up whether or not it works, and the execution plan (as a .sqlplan attachment). Thanks.
SELECT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM AS [ESL Part]
,L.HTS_NUM AS [ESL HTS]
,CAST(L.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
,CTE_1.HTS_NUMBER
,CTE_1.MAX_DATE
FROM ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on ZATS */
--LEFT join CTE_1
-- on L.Part_Num = CTE_1.COMPOSITE_PART
OUTER APPLY (
SELECT
Z.HTS_NUMBER,
MAX(Z.CREATED_DATE) AS MAX_DATE
FROM [TSI].[ZATS_BROKER_FEED] Z
WHERE Z.SUB_ORG = 'FORD'
AND Z.COMPOSITE_PART = L.Part_Num
AND Z.CREATED_DATE <= L.ENTRY_DATE
GROUP BY Z.HTS_NUMBER
) CTE_1
/* Joining on Prod_Class */
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON L.[PART_NUM] = Replace(C.[PROD_ID],'-','')
AND C.[CTRY_CODE] = 'US'
AND C.CLASS_TYPE = 'HS'
and C.BUS_UNIT = 'FORD'
AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
WHERE (H.Importer = 'FORD'
AND H.Entry_Summary_Date >= GetDate () - 10
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')) --ENTRY <> PROD_CLASS
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 4, 2018 at 7:59 am
thanks for looking at this.... the suggested query just ran also. Attached is the execution plan for my query.
thanks . . .
March 4, 2018 at 8:31 am
jeffshelix - Sunday, March 4, 2018 7:59 AMthanks for looking at this.... the suggested query just ran also. Attached is the execution plan for my query.thanks . . .
Looking at the execution plan, estimated row number output 34669.by the way, that's 53856200 x 29831100 or 1606589687820000 rows, add that producing that product 120 times, that is 192790762538400000 rows.
I think you have a problem! This is by the way, more rows than the number of grains on sand in Sahara!
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply