Dramatically Improve Dynamics GP eConnect Performance, But There's a Catch

My blog has moved!  Please visit the new blog at:  https://blog.steveendow.com/

I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com

Thanks!




By Steve Endow

If you've developed a Dynamics GP integration using eConnect and .NET, you should be familiar with the eConnect Serialization process, whereby the eConnect Serialization .NET assembly converts your transaction objects and data into an XML document.

Here's an example of the serialization of a GL transaction.

Notice that this code involves a single GL transaction object, glTrans, with the glTrxHeader and glTransLines assigned to the object properties.

That single GL transaction is then assigned as a single element to the glType array, which is then converted to XML.

Wait a minute.  Why is the GLTransactionType object, in this case named glType, an array?

Well, it's an array because eConnect Serialization supports the conversion of multiple transactions into a single XML document.

What?

So...why would you want to do that?  Why serialize several transactions instead of one transaction at a time?

Good question!

Here is a video where I discuss this feature and demonstrate the performance:

I asked that same question ages ago when I was developing with eConnect on Dynamics GP 7.5, probably just over 12 years ago.  I was using Visual Basic 6 and the eConnect COM components.

When I saw that eConnect serialization supported transaction arrays, I assumed there must be some reason why that feature existed, so I performed a test to compare the import performance.

I recall that with VB 6, when serializing and sending individual transactions, I was able to import an average of 20 AP invoice transactions per second, which is actually quite a bit better than what I see with .NET today.  And I recall that when I tested serializing and sending multiple transactions at the same time, I did not see a performance benefit. I was therefore puzzled about the functionality.

While I didn't observe any performance benefit, I did find a downside to sending multiple transactions to eConnect at once.  If eConnect encountered an error while importing any of the transactions in the 'batch', the entire batch would be rolled back and no transactions would be saved.  So if you send in 100 transactions, and 1 transaction fails to import, nothing is imported.

And another annoyance:  If your batch of 100 transactions had 5 transactions with errors, eConnect would only return 1 error.  As soon as the first transaction has an error, eConnect stops, rolls everything back, and returns a single error.  Once you fix error #1 and resubmit, eConnect will stop on error #2.  So you are forced to deal with the errors one at a time, and in the meantime, zero transactions have been imported.  It's not an ideal error handling process.

So, fast forward to the year 2018, when I was recently asked if eConnect could import multiple transactions in a single call.  I initially responded that it can, but that there was no performance benefit or value in doing so.

But then, for some unknown reason, I figured I should test single vs. multiple transaction submissions and verify my long-held 'belief'.

So I pulled up my eConnect load testing tool and modified it to support serializing multiple transactions, in addition to one transaction at a time.

When I saw the results of sending multiple transactions to eConnect in a single submission...

My...

Jaw...

Dropped.

100 GL JE transactions imported in 17 seconds when sent individually to eConnect.

But when I submitted a single XML document with 100 journal entries, eConnect imported them in 3 seconds.

WHAT???

I then imported 700 transactions, submitting them all to eConnect in a single import request.  They imported in 17 seconds, the same time it took to import just 100 transactions individually.

That's a 7 fold increase in performance, resulting from a minor change in the import code.  That is a staggering difference.

I repeated the tests several times and confirmed that it wasn't a fluke.

I then tested with AP Invoices and saw the same results.  In this test, I was able to import 750 AP Invoices in a single submission in the same amount of time as submitting 100 AP Invoices individually.  That is cra-zee.

So, based on my new tests with GL JEs and AP Invoices, submitting multiple transactions to eConnect can produce a huuuuuuge performance improvement with the eConnect .NET assemblies.

My guess is that something changed when the eConnect assemblies were converted to .NET, resulting in a slower performance when importing individual transactions, but faster performance when importing multiple transactions.

But....

There is a catch.

It's the same catch that I found when testing with VB 6 and eConnect COM objects.  If there is a single import error, the entire batch will fail to import, and no transactions will be saved.  So if you try to import 500 transactions, a single error will roll everything back and you'll need to fix the error before resubmitting.  And if there is a second or third error, the entire batch will fail a second and third time.  495 transactions will be held up because of 5 errors.  It's a hassle.

And if you assigned 500 journal entry numbers and the batch of transactions fails to import, you've just burned through 500 JEs.  When your auditors review the JEs in Dynamics GP, they'll see a gap of 500 JE numbers and start asking questions.  If such errors happen frequently, you'll quickly burn through thousands of transaction numbers.  It's not the end of the world, but it's not ideal.

So, given these drawbacks, what's the conclusion?

My personal recommendation is that most Dynamics GP users should stick with submitting single transactions to eConnect.  For most Dynamics GP users, eConnect imports are not a bottleneck for the business, and I find that it is uncommon for customers to be waiting around for eConnect integrations to complete.  Usually, customers are able to import hundreds or thousands of transactions relatively quickly, but are then having to wait for those transactions to post in Dynamics GP, as posting is far slower than importing.

However, there are some very high volume customers that import tens of thousands or hundreds of thousands of transactions every day, and are either importing or posting transactions non-stop throughout the day.  If you have Dynamics GP integrations that collectively take hours to complete, and this technique could save you hours of import time, then I would definitely recommend looking into it.  But only if you 1) have the ability to develop pre-validation routines for all of your transactions to minimize errors, and 2) have the ability to deal with errors and reimport the failed transactions, and 3) you can develop a way to manage transaction numbers so that they aren't wasted with each failed import.

I hope this was informative!

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com

Get Next Dynamics GP SOP Transaction Number Using SQL

My blog has moved!  Please visit the new blog at:  https://blog.steveendow.com/

I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com

Thanks!



By Steve Endow

As I mentioned in a recent post, some times it's better to use SQL to retrieve the next document number from Dynamics GP.

I am currently working on an eConnect SOP Order import and needed to retrieve the next order number from GP.

Here is my C# code for retrieving the next SOP number from GP using SQL.

The SQL stored procedure is called taGetSopNumber and you need to provide it with the SOP Type and the Doc Type ID of your desired transaction.


public string GetNextSOPNumber(int sopType, string docTypeID)
{
    //SOP Type:  
    //1 = Quote
    //2 = Order
    //3 = Invoice
    //4 = Return
    //5 = Back Order
    //6 = Fulfillment Order

    try
    {
        SqlConnection gpConn = ConnectionGP();

        string commandText = "taGetSopNumber";

        SqlParameter[] sqlParameters = new SqlParameter[5];
        sqlParameters[0] = new SqlParameter("@I_tSOPTYPE", System.Data.SqlDbType.TinyInt);
        sqlParameters[0].Value = sopType;
        sqlParameters[1] = new SqlParameter("@I_cDOCID", System.Data.SqlDbType.Char, 15);
        sqlParameters[1].Value = docTypeID;
        sqlParameters[2] = new SqlParameter("@I_tInc_Dec", System.Data.SqlDbType.TinyInt);
        sqlParameters[2].Value = 1;
        sqlParameters[3] = new SqlParameter("@O_vSopNumber", System.Data.SqlDbType.Char, 21);
        sqlParameters[3].Value = string.Empty;
        sqlParameters[3].Direction = ParameterDirection.Output;
        sqlParameters[4] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);
        sqlParameters[4].Direction = ParameterDirection.Output;
        sqlParameters[4].Value = 0;

        string nextNum = string.Empty;
        int recordCount = DataAccess.ExecuteNonQuery(gpConn, Controller.Instance.Model.GPDatabase, CommandType.StoredProcedure, commandText, sqlParameters);
        nextNum = sqlParameters[3].Value.ToString().Trim();

        return nextNum;
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in GetNextSOPNumber: " + ex.Message, true);
        return string.Empty;
    }
}

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com

Consulting is never boring!

By Steve Endow

Today I had to switch between several tasks, and during one of those task switches, my brain put on the brakes.


My brain:  This is crazy!

Me:  What is crazy?

Brain:  This!  This is crazy!  Switching from SQL queries to Dynamics GP VS Tools to an ASP.NET Core web API for Dynamics GP to working with multiple Azure services.  And that's just in the last hour!  It's nuts!

Me:  Uh, hello, we do this every day. So that you're not bored, remember?

Brain:  Dude, that doesn't make it any less crazy.

Me:  Noted.  I'll blog about it just to make you feel better.


If I actually stop for a moment, step back, and look at all of the things I do, all of the tools I use, and all of the things I have to know and understand to do my job, it is kinda crazy.

If you're modest, you might think that this is fairly normal, which in some respects it is--lots of people probably do what you do in the consulting world.  But if you want to really appreciate how much you really know, try hiring a 20 year old intern and give them a few small projects.  You'll quickly realize that the "simple" task you gave the intern requires tons of fundamental knowledge that informs how to perform the task.  It probably took you years to develop that fundamental knowledge, and then many more years on top of that to develop competence or mastery.

Let's start with SQL.  In the Dynamics GP consulting world, a basic understanding of SQL Server and T-SQL is pretty much essential. 

"Hey intern, can you run this query?"

"What's a query?"

"It's a way to get data out of SQL Server."

"SQL Server?"

"Yes, SQL Server is a relational database."

"Relational?"

"Nevermind, just launch Management Studio and connect to the GP SQL instance"

"GP SQL instance?"


These steps may seems obvious, and is probably invisible to you if you've been doing it for years, but every single step requires an entire fundamental skill stack to perform even a basic task.

So you need to know how to work with Management Studio.  How to connect to a SQL instance.  How to write some T-SQL.  It's a good to understand SQL databases, tables, stored procedures, and views.  Maybe triggers and cursors if you're daring.  And how about backups and transaction logs and Recovery Model, just in case there's a problem?

If you're on the bleeding edge, you'll know how to backup SQL Server databases to Azure.  Which means you should be familiar with SQL jobs and Azure Storage and backup compression.  And Azure is an entire universe of knowledge.

But back to Dynamics GP.  How about SET files and dictionaries and chunk files and shared dictionaries and modified forms and reports and AddIns and Modifier & VBA?  And there's all the knowledge around GL, AP, AR, SOP, POP, and IV, not to mention the other ancillary modules like AA, PA, FA, MC, CM, IC, HR, UPR, and others.  You know that one checkbox under Tools -> Setup -> Posting -> Posting?  Ya, that one that affects whether transaction posting hits the GL?  Or what about that option in the SOP Type ID that affects inventory allocation and quantity overrides?  Or the hundreds of other options you kinda need to be aware of?

And naturally, since you're working with an accounting system, it's good to understand debits vs credits and income statement vs balance sheet and cash vs income vs expenses vs assets vs liabilities.  And if you're into reporting, there's the entire universe of standard reporting tools and financial reporting tools.

In my particular line of work, I also need to understanding everything from Excel macros to VBA to VB Script to Integration Manager to eConnect to SmartConnect.  I need to know how to use .NET 2.5 through .NET Core 2.0 using Visual Studio 2010 through 2017.  I need to thoroughly understand IIS and Kestrel, TCP/IP, ports, firewalls, DNS, HTTPS, TLS, SSH, and nmap.  I need to know HMAC, AES, and SHA and have a fairly good understanding encryption.

I need to be able to glance at XML and JSON and quickly find data issues.  I need to know what HTTP verbs and response codes mean, as well as what "idempotent" means (that's actually a word).  I need to understand TXT and CSV parsing and the issues related to using Excel files as data sources.  I need to be able to review thousands of entries in a log file and figure out why two identical requests were processed 3 milliseconds apart, and that's only after I figure out how to reliably log activity with millisecond precision.

I need to understand PCI compliance and how to call credit card gateway APIs for CC and ACH tokenization and transaction processing.  And then there's the TLS 1.2 upgrade saga--don't get me started on that one.

And while writing some complex queries years ago, I needed to figure out why they were taking hours to run.  So I had to give myself a crash course SQL query optimization so that I didn't kill the SQL Server.  Which led to me developing a subspeciality in amateur SQL Server optimization, which can be quite challenging in the Dynamics GP world.  And if you're dealing with GP performance, it's helpful to understand virtualization and be familiar with Hyper-V and VMWare and how VM memory settings affect SQL Server.

And the list goes on and on.  It's a really, really long list of stuff you need to learn and know and understand and use on a regular basis.

It's kinda crazy.

But that's also why I like it. 

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com

Get Next Dynamics GP RM Payment Number Using SQL

By Steve Endow

When you import transactions into Dynamics GP, you often need to get the next transaction number or document number from Dynamics GP.

In some simple cases, you can leave the document number field blank and let eConnect get the next number for you, but if you are sending in distributions or Analytical Accounting data for a transaction, you need to assign a document number to those elements before sending the transaction off to eConnect.

eConnect does have a method to generate the next number, but there's a big catch: it requires Windows authentication to connect to SQL and get the next document number.  This works for some situations where you will be using Windows Authentication for your integration, but I have many situations where only a SQL or GP login will be available.

In those cases, you can usually directly call the underlying eConnect stored procedures.  The problem with this approach is figuring out which stored procedure to call and how to call it.  You'd be surprised how challenging this can be, and every time I have to do it, I have to go find some old code because I can't seem to find the correct eConnect stored proc.

Case in point is the process for generating the next RM payment (cash receipt) number.  I looked and looked for the eConnect taRM procedure, but couldn't find it.  Why?  Because it's inconsistently named "taGetPaymentNumber".  Ugh.

I couldn't find anything via Google on this, so, to document this lovely process, here is my C# code for getting the next RM Payment Number using the eConnect stored procedure.


        public static bool GetNextRMPaymentNumber(string gpDatabase, ref string nextPayment)
        {
            //eConnect method, which uses Windows auth
            //Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers nextDoc = new Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers();
            //string nextRMPayment = nextDoc.GetNextRMNumber(Microsoft.Dynamics.GP.eConnect.IncrementDecrement.Increment, Microsoft.Dynamics.GP.eConnect.RMPaymentType.RMPayments, ConnectionStringWindows(gpDatabase));
            //return nextRMPayment;

            //SQL method
            string commandText = "taGetPaymentNumber";

            SqlParameter[] sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@I_vDOCTYPE", System.Data.SqlDbType.TinyInt);
            sqlParameters[0].Value = 9;  //9 = Payment
            sqlParameters[1] = new SqlParameter("@I_vInc_Dec", System.Data.SqlDbType.TinyInt);
            sqlParameters[1].Value = 1;  //1 = Increment
            sqlParameters[2] = new SqlParameter("@O_vDOCNumber", System.Data.SqlDbType.VarChar, 21);
            sqlParameters[2].Direction = ParameterDirection.InputOutput;
            sqlParameters[2].Value = string.Empty;
            sqlParameters[3] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);
            sqlParameters[3].Direction = ParameterDirection.InputOutput;
            sqlParameters[3].Value = 0;

            int recordCount = DataAccess.ExecuteNonQuery(gpDatabase, CommandType.StoredProcedure, commandText, sqlParameters);

            if (int.Parse(sqlParameters[3].Value.ToString()) == 0)
            {
                if (sqlParameters[2].Value.ToString().Trim() != string.Empty)
                {
                    nextPayment = sqlParameters[2].Value.ToString().Trim();
                    return true;
                }
                else
                {
                    nextPayment = string.Empty;
                    return false;
                }
            }
            else
            {
                return false;
            }

        }


I prefer using this method, as it will work whether I am using Windows Auth or SQL auth.

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com