News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics AX (Archived) SSMS Query goes from 3...

Microsoft Dynamics AX (Archived)

SSMS Query goes from 3 seconds to 30 minutes...???

(0) Share

Report

Report

Posted on by b_radlyjames

3,061

I am attempting a SQL query of some ax tables:

Select PET.TransId

FROM dbo.PROJEMPLTRANS AS PET
JOIN dbo.HCMWORKER AS hw
ON hw.RECID = pet.WORKER
JOIN dbo.PROJTABLE AS PT
ON pt.PROJID = PET.PROJID
AND pt.DATAAREAID = pet.DATAAREAID
JOIN dbo.PROJINVOICETABLE AS pit
ON pit.PROJINVOICEPROJID = pt.PROJINVOICEPROJID
AND pit.DATAAREAID = pt.DATAAREAID

WHERE PET.TRANSDATE between '2018-11-12' and '2019-02-10'

If I take out the HCMWORKER join OR the PROJINVOICETABLE join, the query runs about 95,000 records in 3 seconds.

Once I have both HCMWORKER and PROJINVOICETABLE in together, it takes 30+ minutes. I can't seem to understand why either of those joins would cause such an increase in execution time.

*This post is locked for comments

All responses Img

All responses (2)

Answers Img

Answers (0)

b_radlyjames 3,061 on at

Like (0)

Report

RE: SSMS Query goes from 3 seconds to 30 minutes...???

Martin,

Thank you for the response. Although the execution plan didn't show the issue, it led me in the right direction...

Some how, if I use a date that is <= '2018-10-28' it works in a matter of seconds

WHERE PET.TRANSDATE between '2018-10-28' and '2019-02-10'

Anything >= '2018-10-28' and it takes 30+ minutes. Very strange.
Martin Dráb 230,188 Most Valuable Professional on at

Like (0)

Report

RE: SSMS Query goes from 3 seconds to 30 minutes...???

Looking at the actual execution plan should give you some idea.

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2

Martin Dráb 230,188 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans