RSMUS.comSteve Endow, owner of Precipio Services, has done a fair amount of volume testing of imports to Dynamics GP and has shown that a single GL Entry with 1,000 lines can import in as little as 4 seconds. Obviously, everyone’s hardware and SQL Server environment is different so times are going to vary, but what could be happening to one of RSM’s clients with journals between 300 and 700 lines taking 5 to 10 minutes and bringing the experience in GP for everyone else to a painful crawl?
I suspected there must be some form of blocking happening but this isn’t my area of expertise, so I found a SQL script promising to reveal blocks. I ran it manually a few times while the user ran an import. After a few tries, it returned the following results:
I am still unsure why the text is reported as a ‘create procedure’ when I know the procedure is simply being run and not created but this gave me something to research. Even though the first row reports NULL for the text column we can use DB_NAME() and OBJECT_NAME() to discover more about this object. It turns out that database 9 is the GP company database to which we are importing, and object 1201816485 is the eConnect stored procedure taGLTransactionHeaderInsert. So the question is, why is that blocked by the smCleanupPostingJournalEntries procedure running on a different database?
The answer was found by looking at the SQL jobs. This procedure is part of a standard cleanup process created by a script with the name PJjob.sql which you can find in your GP application directory’s SQL\Util subfolder. Running this procedure creates the SQL job Remove Posted PJOURNALS from All Companies. I found that the client had two copies of this job. As such, both were running every 30 minutes and entering a deadlock with each other. When the GL import ran at the same time it became blocked as well.
Disabling one of the jobs should solve the problem, right? Unfortunately, it did not. We discovered that when the GL import ran at the same time as these two jobs, it timed out. However, removing this deadlock situation only stopped the time-out problem and all GL imports were still taking an extraordinary amount of time.
Next, I turned to the First Responder Kit from Brent Ozar. I wasn’t certain what I was looking for, but I luckily stumbled onto the kit’s sp_BlitzCache tool. With permission to use the client’s test company, I used the Direct Doc Sender1 application and an XML document representing a Journal Entry with 100 lines for testing. I performed this import and used sp_BlitzCache to seek poor performing queries. At first, it reported the eConnect procedure taGLTransactionHeaderInsert in the top 10 worst queries but with without much detail since it was created with encryption. I used Red Gate’s SQL Prompt on my workstation to get a copy without encryption and deployed this to the client’s test company. Now sp_BlitzCache could show me more details about this eConnect procedure and statements inside it.
Using the SortOrder parameter of sp_BlitzCache I found a query inside taGLTransactionHeaderInsert was performing a high number of reads but returning no results for the given journal entry number. I captured the query’s execution plan and found that a Table Scan operation was being performed and the plan suggested it would benefit from a new index on DTA10100. This table is part of the Multidimensional Analysis (MDA) feature of Dynamics GP and it contained 100,000+ rows.
After creating this index, my test import of a 100 line transaction went from over 1 minute down to less than 2 seconds. We deployed this to the production company and our GP user also reported a significant improvement of their largest transactions.
1: Direct Doc Sender is a sample application included with eConnect. Its default install location is at C:\Program Files (x86)\Microsoft Dynamics\eConnect <version #>\eConnect Samples\CSHARP DirectDocSender\. To use it you must open the CSPROJ file in Visual Studio and Build the application which outputs the executable program.
Brent Ozar’s First Responder Kit: https://www.brentozar.com/first-aid/
sp_BlitzCache instructions: https://www.brentozar.com/blitzcache/
Red Gate’s SQL Prompt: https://www.red-gate.com/products/sql-development/sql-prompt/
Special thanks to Steve Endow of Precipio Services for performing additional baseline performance testing of eConnect GL imports.
