Performance difference in Acumatica between MS SQL and MySQL

Hello everybody,

today I want to write a few words about performance difference between MySQL and MS SQL in Acumatica.

Relatively recently Acumatica provided possibility to use MySQL as it's database. For some unknown reason

I didn't notice anybody who decided to use MySQL as their database solution. If you know any, please let me know,

how they like their experience. 

For me as developer it become interesting to check performance of MySQL vs MS SQL. Especially from standpoint of servers. 

Take note on how one of mine task managers look like:

as you can see I have 40 logical cores or 20 physical cores. Try to guess what is price of license for MS SQL for using at such a computer? According to this link one core costs 14 256 $, or 285 120$. Definetely prety costly.

And what would be price of MySQL for such a machine? Zero.

From this comparison MySQL looks very attractive. What about performance?

In order to test it, I've created small button code that executes creation of sales orders, and in the end gives some numbers. Below goes the code for cases if you wish to try it by yourself and compare my results with yours:



public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
    {
        public PXAction<SOOrder> Test;
 
        [PXProcessButton]
        [PXUIField(DisplayName = "Test", MapEnableRights = PXCacheRights.Update, MapViewRights = PXCacheRights.Update)]
        public virtual IEnumerable test(PXAdapter adapter)
        {
            PXLongOperation.StartOperation(this, ()=>
            {
                var graph = PXGraph.CreateInstance<SOOrderEntry>();
                CreateSalesOrders(graph);
            });
            return adapter.Get();
        }
 
        private const int smallTest = 10;
        private const int mediumTest = 100;
        private const int bigTest = 500;
        private const int bigerTest = 1000;
        private const int bigerTest2 = 1500;
 
        public static void CreateSalesOrders(SOOrderEntry graph)
        {
            StringBuilder sb = new StringBuilder();
 
            var sw = new Stopwatch();
 
            ExecuteSOCreationInCycle(graph, sw, sb, smallTest);
            ExecuteSOCreationInCycle(graph, sw, sb, mediumTest);
            ExecuteSOCreationInCycle(graph, sw, sb, bigTest);
            ExecuteSOCreationInCycle(graph, sw, sb, bigerTest);
            ExecuteSOCreationInCycle(graph, sw, sb, bigerTest2);
 
            using (StreamWriter file =
                new StreamWriter(@"e:\Acumatica\results.txt"))
            {
                file.Write(sb.ToString());
            }
        }
 
        private static void ExecuteSOCreationInCycle(SOOrderEntry graph, Stopwatch sw, StringBuilder log, int numberOfCycles)
        {
            sw.Start();
            for (int i = 0; i < numberOfCycles; i++)
            {
                CreateSO(graph);
            }
            sw.Stop();
            log.Append($"{numberOfCycles} Sales orders = {sw.ElapsedMilliseconds} milliseconds\r\n");
        }
 
        private static void CreateSO(SOOrderEntry graph)
        {
            try
            {
                graph.Clear();
                var newSoOrder = graph.Document.Insert();
                graph.Document.SetValueExt<SOOrder.customerID>(graph.Document.Current, 4901);
                graph.Document.SetValueExt<SOOrder.status>(graph.Document.Current, "N");
                graph.Document.Update(newSoOrder);
 
                var transaction = graph.Transactions.Insert();
                graph.Transactions.SetValueExt<SOLine.inventoryID>(transaction, 456);
                graph.Transactions.SetValueExt<SOLine.orderQty>(transaction, 5.0m);
                graph.Transactions.SetValueExt<SOLine.curyUnitPrice>(transaction, 6.0m);
 
                graph.Transactions.Update(transaction);
 
                graph.Persist();
            }
            catch (Exception ex)
            {
                PXTrace.WriteError(ex);
            }
            
        }
    }

Basically that code adds button Test on screen Sales orders, and if you click on that button, it will execute single thread that will measure how many milliseconds it will take to create 10, 100, 500, 1000, 1500 sales orders. 

Below goes table which describes results on MS SQL and MySQL:

MS SQL MySql
ms for 10 Sales Orders 9082 11005
ms for 100 Sales Orders 57059 70894
ms for 500 Sales Orders 332826 400403
ms for 1000 Sales Orders 1064203 1239445
ms for 1500 Sales Orders 2540225 2891575

As you can see from the table, MS SQL is as usually around 20% faster then MySql.

If you prefer to see diagrams, here is the one:

lower means better in this case.

Summary

As you see from the test, MySql is slower on 20% then MS SQL. It means that if you need something 20% faster then the other and can affort it, go on MS SQL. If you want to get cheaper, and don't plan to have huge loading on your Acumatica then MySQL can help you to save some money

No Comments

Add a Comment