Steve Endow
UPDATE: October 2018: After further investigation, I was able to pinpoint a potential cause for this issue. Testing with standard GL JEs (no AA data) and over 1,000 JEs with over 120,000 lines in the GL10001 table didn’t result in any performance decrease. This leads me to believe that the AA portion of the import might be responsible for the slowdown as JE records increase. This aligns with other performance issues I’ve encountered while importing transactions containing AA data using eConnect. Typically, eConnect imports with AA data are considerably slower than those without.
UPDATE 2: October 2018: Additional testing was conducted on a fresh GP 2018 R2 installation running on SQL Server 2017. Despite numerous imports of JEs containing AA data, I couldn’t replicate the performance problem described below. Importing multiple single JEs with 2,000 lines each didn’t lead to slower times—in fact, the times slightly improved after 5 JEs. This suggests an environment or version-specific issue requiring analysis on the affected SQL Server.
A client recently asked about the performance of an eConnect GL journal entry import I created for them. They had been informed that changing a Dynamics GP setting could enhance eConnect’s speed. Intrigued, I decided to investigate this claim on one of my test servers.
Before altering any Dynamics GP settings, I wanted to establish a performance baseline for my JE import by recording multiple import times to calculate an average.
I launched the client’s eConnect JE import and added timers using the .NET System.Diagnostics.Stopwatch class to measure the time eConnect took to import the journal entry transaction.
My test data file contained 1,710 lines within a single JE, along with Analytical Accounting codes that my application imports. My import transmits all 1,710 lines plus the AA data to eConnect as a single XML document.
After running the import several times and recording the eConnect import durations, I noticed a puzzling trend after 5 imports. The times varied significantly, ranging from 28 seconds to 43 seconds for the same JE data. Clearly, eConnect’s performance was degrading with each import.
28.71 35.35 41.47 41.93 43.03
Suspecting that the eConnect service might be retaining SQL connections, I restarted it. Initially, there was no noticeable difference. However, after 7 more test imports, the times unexpectedly dropped before rising again. The recorded times now ranged from 15 seconds to 45 seconds—an even wider variance than before!
42.89 27.94 15.64 16.95 31.22 33.00 45.20
This inconsistency was perplexing and raised concerns. It seemed implausible for the same JE data to take 15 seconds in one instance and 45 seconds in another. What explained this fluctuation?
Next, I restarted the SQL Server service, speculating that SQL might be experiencing slowdowns with repeated imports. While this theory seemed unlikely, I wanted to eliminate it as a possibility. After restarting SQL, the initial import was slower (as expected), but subsequent imports remained consistent around 45 seconds. Restarting eConnect again didn’t change these results.
56.50 43.35 43.35 45.24 45.18 44.45 46.11 43.20
While the times were now consistent at approximately 45 seconds, the question remained: why the initial variation, including the remarkably fast 15 and 16-second imports?
After a moment of contemplation, I decided to delete all unposted test JEs, leaving the GL work table empty.
As if by magic, the first JE import completed in under 14 seconds.
13.65 17.87 23.37 32.73 41.09
However, each subsequent import took progressively longer, eventually exceeding 40 seconds.
Repeating the experiment by deleting all JEs and restarting the import produced the same result: import times dropped below 15 seconds.
14.66 16.96 23.84 33.15 37.74 45.68
To confirm consistency, I deleted the JEs once more and observed similar results.
13.48 16.84
This discovery was both intriguing and unexpected. My interpretation is that the performance of the eConnect JE import is highly sensitive to the contents of the GL JE work tables. Each imported JE added 1,710 lines to this table, and as the number of lines grew, the eConnect JE insert operation slowed down.
The observed performance fluctuations with decreasing and then increasing times were likely due to deleting all imported JEs midway through that particular test run.
Although I haven’t examined the eConnect stored procedures in detail, my suspicion is that this behavior is related to SQL indexes and potentially a suboptimal query within the eConnect procedure. It’s unlikely that SQL Server itself would experience such significant performance degradation after inserting a mere 6,000 records into a database table. Therefore, a query optimization issue seems more plausible. Given that eConnect stored procedures are often complex and lengthy (sometimes spanning thousands of lines), encountering a few unoptimized or scenario-specific queries wouldn’t be surprising.
It’s important to note that these are highly specific tests conducted on one of my development servers. Therefore, these findings might not apply to other environments, transaction types, or even different JE types. Nonetheless, the results are consistent and highly reproducible within this specific context, warranting consideration for other clients or projects involving high-volume integrations.
_Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles. He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.