Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jason Hall]

On the more technical side of Dynamics NAV 2013 R2, we have put in some effort to improve performance and deadlocking between NAV and SQL Server. One of the major culprits of missing index candidates and high user seeks is the ChargeLogic EFT Transaction table, specifically the following keys/indexes in the NAV table object/SQL Server table:

[dbname$EFT Transaction$VSIFT$3] [dbname$EFT Transaction$VSIFT$4]

The table object was modified to support SQL Server’s recommended index configuration, so in NAV the EFT Transaction Key SQLIndex field for the [dbname$EFT Transaction$VSIFT$3] key was changed to Document No.,Document Type,Transaction Status,Method Type.

In looking at the SQL Table, I noticed that these two indexes were odd.  They are both non-unique. I took a look at the data.  I wanted to know how non-unique these records were.  It turns out the records in this table are VERY non-unique.  Why?  Please read on.

Again, the data tells a story.  Look how many non-unique key values we have for [dbname$EFT Transaction$VSIFT$3]

Notice, Document No. is blank for all of the top culprits.  Why?  Are these orphaned records? Can they be removed?   Please read on…

The transaction year also tells a story.  We have quite a few EFT Transactions stored here going back to the beginning.  I also found EFT Transaction Archive and EFT Transaction Archive Log tables are empty.  Why? It’s clear they were created for a reason, so why aren’t we archiving old EFT Transaction data?  I believe we can improve the SQL performance on this table dramatically if we simply archive some of the old EFT Transactions. 

Can these old transactions can be archive and what’s the impact in doing so (on reporting historical accounting data).

Also, are other ChargeLogic users running into similar performance issues? 

——————————
Jason Hall
Ortho Molecular Products, Inc
Woodstock IL
——————————

[Crystal Tollison]

Jason,
The Archiving process can be found in the EFT Periodic Activities.  There are two fields in the EFT Setup table that should be populated that work in conjunction with the Periodic Activity to Archive transactions. You can Purge transactions as well. This may help your performance.

Hope this helps!

——————————
Crystal Tollison
Consultant
ArcherPoint Inc.
Lawrenceville GA
——————————
——————————————-

[Jason Hall]

Thank you for pointing that feature out! I attempted to archive anything over a year old (in our development database) And I received this error message.  Look at how many transaction statuses we have that are not approved, used, or void.  Is there a way to void these? It’s worth noting that we only used BLUEPAY for ACH transactions.  I could change their status programmatically with a script, but I want to avoid that unless a last resort.

——————————
Jason Hall
Ortho Molecular Products, Inc
Woodstock IL
——————————
——————————————-

[Paul Turner]

If you don’t need to keep an archived copy of those transactions, you can simply delete those.

Go to the “EFT Gateway Account Status” form/page.  The New transactions show under the Unsubmitted Transaction Count and you can select more than one record from that list to delete.  Declined, Duplicate, Failed, Error, and Referred transactions show under the Unapproved Transaction Count and also can be mass-deleted from there.  Queued transactions show under Queued Transaction Count but I am not sure if you can delete those if a related document still exists.

——————————
Paul Turner
Liberty Mountain
Sandy UT
——————————
——————————————-

[Jason Wilder]

The way the archive process works is that it moves the transactions from the “EFT Transaction” table to the “EFT Transaction Archive” table.  You have the opportunity to move these transactions back to the EFT Transaction table if for some reason you need them.  This should help with your deadlocking.  You can go one step further and purge those transaction from the “EFT Transaction Archive” table if you wish (but won’t help your deadlocking since it is a different table). In your case the field “Age to Archive Transactions” should be set to -1Y.  If you want to purge them as well you could set “Age to Purge Archived Transactions” to -1Y-1M which would give you a rolling month to move transaction back if you want.

We did not want to archive our gift cards, only credit cards so I added some code to Codeunit 37028526  (Your version might be report 37028326 not sure):
  Tran.SETFILTER(“Transaction Status”,’%1|%2|%3′,Tran.”Transaction Status”::Approved,  
                    Tran.”Transaction Status”::Used,Tran.”Transaction Status”::Void);
  Tran.SETFILTER(“Tender Type”,'<>%1′,Tran.”Tender Type”::”Gift Card”);

Also added code to make sure the EFT Transaction is not archived if the Sales Order is still open since we have some orders that remain open for over a year.

Also added code to skip archiving the transaction if it is the last one for a certain “Terminal No.” since this causes an error.  You will know if you run into this one or not and it really depends on the activity for each “Terminal No.”. It just so happens that we have a few Terminal No.’s that are only used during the month of December so we do run into this, but you may not.

I can post the code for these last 2 issues if you need them.

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————
——————————————-

[Jason Hall]

Jason, Paul, and Crystal, thank you for your replies and your recommendations. They all were very useful! This was my very first post on NAVUG and I’m really pleased with the support.  I marked Crystal’s as the best response but you all helped me get to the solution. I’ve actually taken a piece from all of you.  I ended up calling the Archive function from my own Code Unit and placing my own custom purge activity on the menu suite. I wasn’t able to do this until I got rid of the abandoned transactions in the system.  I even had to change the layout to the EFT Transaction Archive table.  It was out of synch with the record layout for the EFT Transaction table.  May have been a miss during the last NAV upgrade that was performed here.

——————————
Jason Hall
NAV/ERP Developer
Ortho Molecular Products, Inc
Woodstock IL
——————————
——————————————-