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.