Question Status

Verified
David Ahalt asked a question on 18 Dec 2013 2:53 PM

Is there a way to limit the number of transaction in a batch or a way to break them into smaller batches?  I have several batches which grow very large during the day.  The problem we are seeing is the batches are so large that when they post at night we encounter deadlocks and network interrupts before the batches finish posting thus hanging the posting process requiring batch recovery.  Does anyone have a script or automated process to break a batch into smaller batches to minimize the chances of encountering interrupts?

Thanks,  Dave

Dave Ahalt – Senior Dynamics GP Systems Architect

Ascend Learning, LLC

www.ascendlearning.com

                       

 

Reply
Suggested Answer
Richard L. Whaley responded on 18 Dec 2013 5:24 PM

Sorry, there is no way to limit the number of transactions in a batch.

Are you using (or could you switch to using) Fulfillment Orders?  There is a sales multiple order to invoice transfer that allows you to select a range of orders that are fulfilled and transfer them to a new batch for posting.

However, you mentioned nightly posting.  Do you post multiple batches at the same time?  Having more batches posting at the same time can make the deadlock problem worse.

You may want to get a SQL expert to review the setup of your SQL.  It is possible to increase the number of threads, lengthen the time out, and several other changes that they would know best that could help.

Richard L. Whaley Author, Publisher, Consultant

http://www.AccoladePublications.com

Enhancing your Dynamics Knowledge!

Reply
Ian Grieve responded on 19 Dec 2013 3:53 AM

David, there is no way to do this out of the box.

How are your batches being created?


Author of azurecurve|Ramblings of a Dynamics GP Consultant
Senior Consultant at Perfect Image Ltd

Perfect Image/ Dynamics GP
Tel: +44 (0) 843 289 2656
Equinox House, Cobalt 3.2, Silver Fox Lane, Silverlink, North Tyneside, NE27 0QJ, United Kingdom

Reply
Suggested Answer
Sandip Jadhav responded on 19 Dec 2013 12:59 PM

David,

Why don't you force user to use another batch after certain no of transactions. Through vba or Visual Studio Tools you can check on each batches no of transaction and force user to create new batch ID.

Thanks

Sandip

Thanks Sandip Jadhav Cell : +1.210.440.9855 Dynamics GP Blog : http://sandipdjadhav.blogspot.com/
Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 19 Dec 2013 1:15 PM

Nothing more valuable to add, just to ensure what's your main target of this post. Is it to get this solved permanently so that you solve this problem once and forever, or just to handle the current huge batches in your system now.

To handle this permanently, nothing out of the box in GP to handle this as Mr. Ian says, although simple customization either to prevent or pop up a warning message on the entry window is doable.

In case you want to split your current batches for now, you may consider a simple macro to run through all the transaction and update the "Batch ID" field accordingly so that you could limit down the size of the batches.

I will absolutely echo Mr. Richard suggestion in not considering a mass posting with no previous consideration as deadlock might result with undesirable results.

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply
David Ahalt responded on 19 Dec 2013 1:24 PM

These are being sent in from an online store on a continous basis as orders are submitted.  The volume of daily transactions has increased to a level much greater than projected when this was implemented a few years back.  I would normally recommend keeping batches to around 200 transactions but we are seeing much more.  I had seen a batch parsing tool/sql job a couple years back that would doing something like this but cannot find it.

Dave Ahalt – Senior Dynamics GP Systems Architect

Ascend Learning, LLC

www.ascendlearning.com

                       

 

Reply
Frank Hamelly responded on 19 Dec 2013 1:47 PM

David, what tool is being used to integrate the orders into GP?

** Please, if this answers your question, mark it as 'Answered' so others experiencing the same will know it resolved your issue. **

Frank E. Hamelly, MCP-GP, MCP-AX, MCITP, MCT, MVP

http://gp2themax.blogspot.com/

Reply
Donnette responded on 19 Dec 2013 2:05 PM

Hi Dave,

It's been a minute since we've worked together.  Hope all is well in Kansas.  If you're using the web-service hit up Chad Larson on LinkedIn.  We capped SOP's at 200 per batch for a recent Xennsoft>GP integration.  He coded it and can point you in the right direction.

Donnette

Reply
Leslie Vail responded on 19 Dec 2013 5:44 PM

Hi,

I agree with the others. We've had to do this to manage the number of transactions coming in from the RMS systems. While not very elegant, we run a stored procedure before executing the post that scrolls through the batches and changes the batch ID after a set number of transactions. We first pull all of the 'postable' transactions out of the existing batches so that we have on huge batch containing everything we want to post.  Next, we traverse through that batch and change the batch ID after X number of batches.

So far, it has been working.

Kind regards,

Leslie

Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITS
ASCI, Inc.    *    PO Box 600965   *   Dallas, TX 75360    *   972-814-8550   *   leslievail@earthlink.net

Reply
Verified Answer
David Ahalt responded on 20 Dec 2013 8:25 AM

Thanks all appreciate the feedback.  Looks like a little stored proc is in order.

Dave Ahalt – Senior Dynamics GP Systems Architect

Ascend Learning, LLC

www.ascendlearning.com

                       

 

Reply
Richard Wheeler responded on 20 Dec 2013 10:27 AM

Just to add my two cents here, I have a Visual Studio integration that imports hundreds of eExpense entries and it is set to create one batch for each 50 invoices. This elimated all batches errors that occurred with large batch postings.

Richard E. Wheeler 2013 and 2014 MVP

Member Microsoft Academic Alliance

www.rbsolutions.com Revered Business Solutions Ballston Lake, NY  518-877-0763 x10

Reply
Verified Answer
David Ahalt responded on 20 Dec 2013 8:25 AM

Thanks all appreciate the feedback.  Looks like a little stored proc is in order.

Dave Ahalt – Senior Dynamics GP Systems Architect

Ascend Learning, LLC

www.ascendlearning.com

                       

 

Reply
Suggested Answer
Richard L. Whaley responded on 18 Dec 2013 5:24 PM

Sorry, there is no way to limit the number of transactions in a batch.

Are you using (or could you switch to using) Fulfillment Orders?  There is a sales multiple order to invoice transfer that allows you to select a range of orders that are fulfilled and transfer them to a new batch for posting.

However, you mentioned nightly posting.  Do you post multiple batches at the same time?  Having more batches posting at the same time can make the deadlock problem worse.

You may want to get a SQL expert to review the setup of your SQL.  It is possible to increase the number of threads, lengthen the time out, and several other changes that they would know best that could help.

Richard L. Whaley Author, Publisher, Consultant

http://www.AccoladePublications.com

Enhancing your Dynamics Knowledge!

Reply
Suggested Answer
Sandip Jadhav responded on 19 Dec 2013 12:59 PM

David,

Why don't you force user to use another batch after certain no of transactions. Through vba or Visual Studio Tools you can check on each batches no of transaction and force user to create new batch ID.

Thanks

Sandip

Thanks Sandip Jadhav Cell : +1.210.440.9855 Dynamics GP Blog : http://sandipdjadhav.blogspot.com/
Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 19 Dec 2013 1:15 PM

Nothing more valuable to add, just to ensure what's your main target of this post. Is it to get this solved permanently so that you solve this problem once and forever, or just to handle the current huge batches in your system now.

To handle this permanently, nothing out of the box in GP to handle this as Mr. Ian says, although simple customization either to prevent or pop up a warning message on the entry window is doable.

In case you want to split your current batches for now, you may consider a simple macro to run through all the transaction and update the "Batch ID" field accordingly so that you could limit down the size of the batches.

I will absolutely echo Mr. Richard suggestion in not considering a mass posting with no previous consideration as deadlock might result with undesirable results.

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply