February 26, 2018 at 9:17 am
Before i added this, my code ran in under 1 minute. Now, it takes at least 6 minutes (I stopped query at 6 minutes)
Is there a better way to do this?,ZATS = (Select top 1 HTS_NUMBER
From [TSI].[ZATS_BROKER_FEED] Z
where L.Part_Num = Z.composite_Part
AND Z.[SUB_ORG] = 'BROKER'
order by Z.Created_Date Desc
)
Note, the Broker Feed Table is huge. the same composite part could be in there 40 times.
thanks
February 26, 2018 at 9:27 am
You could try a CTE/ROW_NUMBER(). Since you haven't supplied your complete query I can't give you the full details, but it would be something like the following:;
WITH Brokers AS
(
SELECT z.Composite_Part, z.HTS_Number, ROW_NUMBER() OVER(PARTITION BY z.Composite_Part ORDER BY z.Created_Date DESC) AS rn
FROM TSI.ZATS_BROKER_FEED z
WHERE z.SUB_ORG = 'BROKER'
)
.
.
.
LEFT OUTER JOIN Brokers B
ON L.Part_Num = b.Composite_Part
AND b.rn = 1
.
.
.
It would help if you had an index on at least SUB_ORG, Composite_Part, and Created_Date.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 26, 2018 at 9:28 am
Yikes! A correlated subquery. You could try putting this query into a CTE and joining to it on Part_Number = composite_Part and RowNo = 1.
SELECT
composite_Part
, HTS_NUMBER
, ROW_NUMBER() OVER (PARTITION BY composite_Part ORDER BY Created_Date DESC) AS RowNo
FROM TSI.ZATS_BROKER_FEED
WHERE SUB_ORDER = 'BROKER'
John
February 26, 2018 at 9:38 am
so now i have this . . . but dont know how to join on this
SELECT DISTINCT
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(H.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
,ZATS = (SELECT
composite_Part
, HTS_NUMBER
, ROW_NUMBER() OVER (PARTITION BY composite_Part ORDER BY Created_Date DESC) AS RowNo
FROM TSI.ZATS_BROKER_FEED
WHERE SUB_ORG = 'BROKER'
)
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
February 26, 2018 at 9:47 am
Do it exactly as Drew showed you, but in the ZATS column, you only need ZATS = b.HTS_NUMBER - you don't need the subquery there.
John
February 26, 2018 at 9:52 am
John Mitchell-245523 - Monday, February 26, 2018 9:28 AMYikes! A correlated subquery. You could try putting this query into a CTE and joining to it on Part_Number = composite_Part and RowNo = 1.
SELECT
composite_Part
, HTS_NUMBER
, ROW_NUMBER() OVER (PARTITION BY composite_Part ORDER BY Created_Date DESC) AS RowNo
FROM TSI.ZATS_BROKER_FEED
WHERE SUB_ORDER = 'BROKER'John
A correlated subquery is not necessarily bad if the broker feed is dense with respect to the composite_Part, the number of composite_parts is relatively small, and the appropriate index is in place. We don't have enough details to determine if that is the case.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 26, 2018 at 9:57 am
drew.allen - Monday, February 26, 2018 9:52 AMJohn Mitchell-245523 - Monday, February 26, 2018 9:28 AMYikes! A correlated subquery. You could try putting this query into a CTE and joining to it on Part_Number = composite_Part and RowNo = 1.
SELECT
composite_Part
, HTS_NUMBER
, ROW_NUMBER() OVER (PARTITION BY composite_Part ORDER BY Created_Date DESC) AS RowNo
FROM TSI.ZATS_BROKER_FEED
WHERE SUB_ORDER = 'BROKER'John
A correlated subquery is not necessarily bad if the broker feed is dense with respect to the composite_Part, the number of composite_parts is relatively small, and the appropriate index is in place. We don't have enough details to determine if that is the case.
Drew
Yes, very true, although there's circumstantial evidence in the first post that it might indeed not be the case!
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply