You’re offline. This is a read only version of the page.
1,272
Hi, I am facing a strange issue when running the below cross company query
Share
Report while select crossCompany specTrans
where specTrans.SpecCompany == _ledgerJournalTrans.company() &&
specTrans.SpecTableId == _ledgerJournalTrans.TableId &&
specTrans.SpecRecId == _ledgerJournalTrans.RecId
join custTransOpen
where specTrans.RefCompany == custTransOpen.DataAreaId &&
specTrans.RefTableId == custTransOpen.TableId &&
specTrans.RefRecId == custTransOpen.RecId
join custTrans
where custTransOpen.RefRecId == custTrans.RecId
join custTransCustomer
where custTrans.AccountNum == custTransCustomer.AccountNum
AX freezes when I run this. But when I run it without the crossCompany keyword it runs smoothly. I have verified that this query only returns one record.
the actual code can be found in \Classes\LedgerJournalTransUpdateCust\checkSettleVoucher
Any idea why this would happen.
*This post is locked for comments
I am not sure if this was the real problem but we had same customers and vendors in muliple companies and the it was causing the freeze.
So I added the last line below as a fix for both customer and vendor
All responses (
Answers (
while select crossCompany specTrans
where specTrans.SpecCompany == _ledgerJournalTrans.company() &&
specTrans.SpecTableId == _ledgerJournalTrans.TableId &&
specTrans.SpecRecId == _ledgerJournalTrans.RecId
join custTransOpen
where specTrans.RefCompany == custTransOpen.DataAreaId &&
specTrans.RefTableId == custTransOpen.TableId &&
specTrans.RefRecId == custTransOpen.RecId
join custTrans
where custTransOpen.RefRecId == custTrans.RecId
join custTransCustomer
where custTrans.AccountNum == custTransCustomer.AccountNum
&& custTransCustomer.dataAreaId == _ledgerJournalTrans.company() //added
Do you have DataAreaId literals enabled to avoid parameter sniffing for your SQL queries? In case you are using multiple company accounts with varying data sizes, it is a good practice to have it on. Unfortunately crosscompany selects involve multiple companies, for which the query processor engine will have a harder time to optimize this.
As Sukrut said, you need to have SQL Server maintenance plans in place for fragmented indexes and you should regularly update statistics too.
A "freeze" typically means that your SQL Server is busy, have you tried to capture the Query execution plan and see what is wrong with that? Having a field list list as mentioned above is beneficial for the actually used information. But the biggest boost always comes if you have no fragmentation, statistics are updated, and you have covering indexes created for the frequently used queries such as the settlements you have problems with.
Please provide additional details of your findings for the mentioned areas.
I think 2012 code should be better. But as I expected this change didn't help. It's something wrong with the environment because on DEV it's fine. On TEST it freezes. I already checked the data and all the joins are fine and returns 1 row from each table. I will try and run it on SQL now
Thanks. let me try adding these.
What version are you using?
Mine is
Sure but it is standard AX code. I am not sure why they used crossCompany here
Anyway my question is not about the code but how the crossCompany is causing AX to freeze?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156