eConnect Performance: Using GetNextDocNumbers vs taGetNext stored procedures

By Steve Endow

This one is definitely an obscure topic that nobody is asking about.

But hey, I was curious.

I was researching whether the eConnect GetNextGLJournalEntryNumber could handle a heavy load, and whether it would throw any errors when issuing lots of JE numbers.

Interestingly, I was unable to break it.  I was able to get one SQL exception error when running my test while also trying to get a new JE number in the GP Journal Entry window, but I was unable to reproduce that error.

This image shows 3 instances of my load test application simultaneously retrieving a total of 3,000 JE numbers over about 45 seconds.

3,000 JE Numbers

 public string GetNextJENumbereConn()  
 {  
   GetNextDocNumbers getNext = new GetNextDocNumbers();  
   
   try  
   {  
     string nextJE = getNext.GetNextGLJournalEntryNumber(IncrementDecrement.Increment, ConnectionStringWindows());  
     return nextJE;  
   }  
   catch (Exception ex)  
   {  
     throw ex;  
   }  
 }  

Testing just 1 instance of my load tester, I saw that it took about 16 seconds to generate 1,000 JE numbers using the eConnect method.

So, naturally, I wondered what the performance would be if I called the stored procedure directly.

 public int GetNextJENumber()  
 {  
   
   string database = Properties.Settings.Default.gpDatabase;  
   
   string commandText = "taGetNextJournalEntry";  
   
   SqlParameter[] sqlParameters = new SqlParameter[3];  
   sqlParameters[0] = new SqlParameter("@I_vInc_Dec", System.Data.SqlDbType.TinyInt);  
   sqlParameters[0].Value = 1;  
   sqlParameters[1] = new SqlParameter("@O_vJournalEntryNumber", System.Data.SqlDbType.Char, 13);  
   sqlParameters[1].Direction = ParameterDirection.Output;  
   sqlParameters[1].Value = 1;  
   sqlParameters[2] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);  
   sqlParameters[2].Direction = ParameterDirection.Output;  
   sqlParameters[2].Value = 0;  
   
   int nextJE = 0;  
   int recordCount = ExecuteNonQuery(database, CommandType.StoredProcedure, commandText, ref sqlParameters);  
   nextJE = Convert.ToInt32(sqlParameters[1].Value.ToString());  
   
   return nextJE;  
   
 }  

Calling stored procedure, my app was able to retrieve 1,000 JE numbers in about 9 seconds.

9 seconds versus 16 seconds is a pretty big difference, but that's spread across 1,000 JEs.

I guess if you have to import a lot of transactions very quickly and you are counting seconds, consider calling the procs instead of the .NET method.

Personally, I tend to call the get next number stored procedures when possible, as those can be used with either SQL authentication or Windows authentication.

So there you have it.  An eConnect performance metric that you probably never cared about!

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 and YouTube

http://www.precipioservices.com