1. sanping peng
  2. .NET DataStore
  3. Tuesday, 25 January 2022 21:08 PM UTC

Report:

Conclusion from the report(24962 rows data tested):

1:when load data from db,datastore performance is good,time and memory almost equal to ado.net datatable.

2:when convert data to poco model (with six compute columns),datastore performance seems not so good,more time and more memory.

3:combine load and convert to poco model in one function,datastore performance seems not so good,,more time and more memory.

4:How to get poco model to a list,one by one GetModel is the best way or not?

5:How to get a compute column CLR data type?

6:Can not get the compute column value when use AsEnumerable<TModel>().ToList(),How to get the full list with compute column value?

7:Something wrong in my test code caused the performance is not so good? 

Core code:

using BenchmarkDotNet.Attributes;
using DWNet.Data;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FxPerformanceTest
{
    [AllStatisticsColumn]
    [MemoryDiagnoser]
    [MarkdownExporter]
    [CsvExporter]
    [RPlotExporter()]
    [HtmlExporter]
    [CsvMeasurementsExporter]
    public class FxLoadDataTest
    {

        static FxLoadDataTest()
        {
            FxReadDBConnString();
            FxDWNetDataContext dataContext = new FxDWNetDataContext(FxDBConnString);
            FxDataInDataStore = new DataStore(typeof(appfunc), dataContext);
            FxSqlSelect = FxDataInDataStore.GetSqlSelect();
            FxDataInDataStore.Retrieve();
            using SqlDataAdapter adapter = new SqlDataAdapter(FxSqlSelect, FxDBConnString);
            FxDataInDataTable = new DataTable();
            try
            {
                adapter.Fill(FxDataInDataTable);
                //add compute column to datatable
                for (int i = 0; i < FxDataInDataStore.DwMeta.Computes.Count; i++)
                {
                    var compute = FxDataInDataStore.DwMeta.Computes[i];
                    DataColumn colCompute = new DataColumn(compute.Name, System.Type.GetType("System.Int32"));//how to get compute CLR data type?
                    colCompute.Expression = compute.Expression.Expression;
                    FxDataInDataTable.Columns.Add(colCompute);
                }
            }
            catch (Exception ee)
            {
                throw ee;
            }
            
        }
        public static string FxDBConnString;
        public static string FxSqlSelect;
        public static DataTable FxDataInDataTable;
        public static DataStore FxDataInDataStore;
        public static string FxReadDBConnString()
        {
            if (FxDBConnString != null)
            {
                return FxDBConnString;
            }

            var builder = new ConfigurationBuilder()
                .AddJsonFile($"appsettings.json", true, true);

            var config = builder.Build();
            FxDBConnString = config.GetConnectionString("FxDBConn");
            //System.Console.WriteLine(connStr);
            return FxDBConnString;
        }

        /// <summary>
        /// load data to datatable,select sql is same as test datastore
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark(Baseline = true)]
        [Arguments(1)]
        [Arguments(5)]
        //[Arguments(50)]
        public object FxLoadDataTable(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    using SqlDataAdapter adapter = new SqlDataAdapter(FxSqlSelect, FxDBConnString);
                    DataTable _dt = new DataTable();
                    try
                    {
                        adapter.Fill(_dt);
                    }
                    catch (Exception ee)
                    {
                        throw ee;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }
        /// <summary>
        /// load data to datastore,select sql is same as load data to datatable
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark]
        [Arguments(1)]
        [Arguments(5)]
        public object FxLoadDataStore(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    FxDWNetDataContext dataContext = new FxDWNetDataContext(FxDBConnString);
                    DataStore ds = new DataStore(typeof(appfunc),dataContext);
                    ds.Retrieve();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }

        /// <summary>
        /// convert datatable to poco class instance,total number is 24962,data has been filled,the compute field has been caculated too.
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark]
        [Arguments(1)]
        [Arguments(5)]
        public object FxDataTable2Model(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    List<appfunc> lst = new List<appfunc>();
                    for (int j = 0; j < FxDataInDataTable.Rows.Count; j++)
                    {
                        lst.Add(FxDataInDataTable.Rows[j].Buffer2Model<appfunc>());
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }
        /// <summary>
        /// convert datastore to poco model(get poco class instance from datastore),data has been filled(retrieved),caculate the compute result when get the model
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark]
        [Arguments(1)]
        [Arguments(5)]
        public object FxDataStore2Model(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    List<appfunc> lst = new List<appfunc>();
                    for (int j = 0; j < FxDataInDataStore.RowCount; j++)
                    {
                        lst.Add(FxDataInDataStore.GetModel<appfunc>(j, true));
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }
        /// <summary>
        /// convert datastore to poco model(get poco class instance from datastore),data has been filled(retrieved),caculate the compute result when get the model
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark]
        [Arguments(1)]
        [Arguments(5)]
        public object FxDataStore2ModelByAsEnumerable(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    List<appfunc> lst = FxDataInDataStore.AsEnumerable<appfunc>().ToList();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }
        /// <summary>
        /// fill data to datatable,add compute columns,convert to poco class instance
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark]
        [Arguments(1)]
        [Arguments(5)]
        public object FxDataTableLoadAnd2Model(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    DataTable _dt = null;
                    using SqlDataAdapter adapter = new SqlDataAdapter(FxSqlSelect, FxDBConnString);
                    try
                    {
                        _dt = new DataTable();
                        adapter.Fill(_dt);
                        //add compute column to datatable
                        for (int j = 0; j < FxDataInDataStore.DwMeta.Computes.Count; j++)
                        {
                            var compute = FxDataInDataStore.DwMeta.Computes[j];
                            DataColumn colCompute = new DataColumn(compute.Name, System.Type.GetType("System.Int32"));//how to get compute CLR data type?
                            colCompute.Expression = compute.Expression.Expression;
                            _dt.Columns.Add(colCompute);
                        }
                    }
                    catch (Exception ee)
                    {
                        throw ee;
                    }
                    List<appfunc> lst = new List<appfunc>();
                    for (int k = 0; k < _dt.Rows.Count; k++)
                    {
                        lst.Add(_dt.Rows[k].Buffer2Model<appfunc>());
                    }
                }
                
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }
        /// <summary>
        /// retrieve data to datastore and get poco class instance to a list
        /// </summary>
        /// <param name="concurrent"></param>
        /// <returns></returns>
        [Benchmark]
        [Arguments(1)]
        [Arguments(5)]
        public object FxDataStoreLoadAnd2Model(int concurrent)
        {
            try
            {
                for (int i = 0; i < concurrent; i++)
                {
                    FxDWNetDataContext dataContext = new FxDWNetDataContext(FxDBConnString);
                    DataStore ds = new DataStore(typeof(appfunc), dataContext);
                    ds.Retrieve();
                    List<appfunc> lst = new List<appfunc>();
                    for (int j = 0; j < ds.RowCount; j++)
                    {
                        lst.Add(ds.GetModel<appfunc>(j, true));
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return 0;
        }
    }
}

Full code has been uploaded in the attachments.

Anybody can explain the performance test report?

Thanks.

Attachments (1)


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.