Report runs forever.`

Question

Post reply

Report runs forever.`

jeffshelix

SSCrazy

Points: 2257

October 30, 2019 at 6:41 pm

#3693236

Not sure why this report runs continously...

Select distinct
H.Customs_Entry_Num as [Entry Num]
,H.Entry_Summary_Date
,L.Part_Num
,L.HTS_Num
,l.HTS_Value
,L.Line_Item_Duty AS Duty
,z.HTS_NUMBER
,z. PTNR_ID
,Z.ELIGIBILITY
,z.Created_Date
,z.COMPOSITE_PART
FROM ADHOC.ATS_ESH H
INNER JOIN adhoc.ATS_ESL L
ON h.TRANS_SK = l.TRANS_SK
LEFT JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON L.Part_Num = Z.COMPOSITE_PART AND 
Z.CREATED_DATE =
(
SELECT max(Z.CREATED_DATE) FROM [TSI].[ZATS_BROKER_FEED] Z
WHERE Z.CREATED_DATE <= H.ENTRY_DATE and 
Z.COMPOSITE_PART = L.PART_Num
)
WHERE H.Importer = 'Sample'
AND H.Com_Desc_Code = 'Parts'
AND H.Entry_Summary_Date > '10/15/2019'

thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jeffshelix SSCrazy Points: 2257 More actions · Answer 1

SOLVED - ZATS is HUGE and i didn't have a Where clause on iMPORTER

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Do you have an execution plan as a .sqlplan file or .pesession file that you can share with us?
What are the indexes on the tables:
ADHOC.ATS_ESH
ADHOC.ATS_ESL
TSI.ZATS_BROKER_FEED

I notice you are hitting the ZATS_BROKER_FEED table twice when you probably don't need to. Instead of LEFT JOIN with a correlated subquery inside the join condition, try something like:

OUTER APPLY
    (SELECT TOP 1 Z1.HTS_NUMBER, Z1.PTNR_ID, Z1.ELIGIBILITY, Z1.Created_Date, Z1.COMPOSITE_PART
     FROM [TSI].[ZATS_BROKER_FEED] Z1
     WHERE Z1.COMPOSITE_PART = L.Part_Num
      AND Z1.CREATED_DATE <= H.ENTRY_DATE
     ORDER BY Z1.CREATED_DATE DESC) Z
?

To really help you though, we'd need to know the execution plan and indexes available.

This reply was modified 5 years ago by Chris Harshman.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- Avoid DISTINCT, it almost always invokes a SORT.

-- Avoid reading tables more times than is necessary

SELECT -- DISTINCT

H.Customs_Entry_Num as [Entry Num]

,H.Entry_Summary_Date

,L.Part_Num

,L.HTS_Num

,l.HTS_Value

,L.Line_Item_Duty AS Duty

,z.HTS_NUMBER

,z. PTNR_ID

,z.ELIGIBILITY

,z.Created_Date

,z.COMPOSITE_PART

FROM ADHOC.ATS_ESH H

INNER JOIN adhoc.ATS_ESL L

ON h.TRANS_SK = l.TRANS_SK

OUTER APPLY (

SELECT TOP(1) *

FROM [TSI].[ZATS_BROKER_FEED] Z

WHERE Z.CREATED_DATE <= H.ENTRY_DATE

AND Z.COMPOSITE_PART = L.PART_Num

ORDER BY Z.CREATED_DATE DESC

) z

WHERE H.Importer = 'Sample'

AND H.Com_Desc_Code = 'Parts'

AND H.Entry_Summary_Date > '10/15/2019'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden