Dapper ORM VS SqlSugar ORM的 8场对决

CUP和.NET SQL版本不同也会存在少许差距,但不会有质变,下面的测试结果仅供参考

比赛规则

1.统一使用Realse版本的最新 DLL,Realse模式启用程序

2.为了平衡CPU和数据库空闲情况,使用车轮战,每场比赛连续10回合比试

3.多次重启电脑取平均成绩上图

比赛成员 

1.SqlSugar 3.1.01

2.Dapper 1.5.0.2  Dapper.Contrib 1.5 官方DLL

 

第一场 :查询所有  ,主要比拼数据转换实体的性能 

每次查询100万条数据 

 

SqlSugar 100分  Dapper95

比赛结果: SqlSugar小胜 

 

代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class SelectBigData : IDemos
    {
        /// <summary>
        /// 测试一次读取100万条数据的速度
        /// </summary>
        public void Init()
        {
            Console.WriteLine("测试一次读取100万条数据的速度");
            var eachCount = 1;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {

                //dapper
                Dapper(eachCount);

                //sqlSugar
                SqlSugar(eachCount); 
            }
 
        }

        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var list = conn.Queryable<Test>().ToList();
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var list = conn.GetAll<Test>();
                }
            });
        }
    }
}
View Code

 

第二场:查询单条,主要比拼实体转换以外的性能

SqlSugar 100分  Dapper 100

比赛结果:平手

 

代码: 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class SelectSingle : IDemos
    {
        /// <summary>
        /// 测试一次读取1条,每次执行1000次
        /// </summary>
        public void Init()
        {
            Console.WriteLine("测试一次读取1条");

            var eachCount = 1000;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {

                //dapper
                Dapper(eachCount);

                //sqlSugar
                SqlSugar(eachCount);
            }

        }

        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var list = conn.Queryable<Test>().InSingle(1000);
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var list = conn.Get<Test>(1000);
                }
            });
        }
    }
}
View Code

 

第三场:比拼海量数据更新

 

SqlSugar 100分  Dapper 60

比赛结果:SqlSugar胜出

 

代码: 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class UpdateList : IDemos
    {

        public void Init()
        {
            Console.WriteLine("测试更新1000条的集合");

            var eachCount = 10;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {

                //dapper
                Dapper(eachCount);

                //sqlSugar
                SqlSugar(eachCount);

            }

        }
        private static List<Test> GetList
        {
            get
            {
                List<Test> list = new List<Test>();
                for (int i = 1000; i < 2000; i++)
                {
                    Test t = new Test()
                    {
                        Id=i,
                        F_Int32 = 1,
                        F_String = "Test",
                        F_Float = 1,
                        F_DateTime = DateTime.Now,
                        F_Byte = 1,
                        F_Bool = true
                    };
                    list.Add(t);
                }
                return list;
            }
        }
        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var list = conn.SqlBulkReplace(GetList);
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var list = conn.Update(GetList);
                }
            });
        }
    }
}
View Code

 

 

第四场:比拼海量数据插入 

SqlSugar 100分  Dapper 12

比赛结果:SqlSugar胜出

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class InsertList : IDemos
    {
 
        public void Init()
        {
            Console.WriteLine("测试插入1000条记录的集合");

            var eachCount = 10;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {
                //清除
                DeleteAddDatas();

                //dapper
                Dapper(eachCount);


                //清除
                DeleteAddDatas();

                //sqlSugar
                SqlSugar(eachCount);

            }
            Console.WriteLine("SqlSugar批量插入性能,秒杀Dapper一条街。(Dapper并没有优化过)");
        }

        private static void DeleteAddDatas()
        {
            using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
            {
                conn.Delete<Test>(it => it.F_String == "Test");
            }
        }
        private static List<Test> GetList
        {
            get
            {
                List<Test> list = new List<Test>();
                for (int i = 0; i < 1000; i++)
                {
                    Test t = new Test()
                    {
                        F_Int32 = 1,
                        F_String = "Test",
                        F_Float = 1,
                        F_DateTime = DateTime.Now,
                        F_Byte = 1,
                        F_Bool = true
                    };
                    list.Add(t);
                }
                return list;
            }
        }

        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var list = conn.SqlBulkCopy(GetList);
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var list = conn.Insert(GetList);
                }
            });
        }
    }
}
View Code

 

第五场:比拼批量删除 

 

SqlSugar 100分  Dapper 50

比赛结果:SqlSugar胜出

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class DeleteArray : IDemos
    {

        public void Init()
        {
            Console.WriteLine("测试删除1000条的集合");

            var eachCount = 10;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {

                //dapper
                Dapper(eachCount);

                //sqlSugar
                SqlSugar(eachCount);

            }

        }

        /// <summary>
        /// 查询出刚插入的1000条数据
        /// </summary>
        /// <returns></returns>
        private static List<Test> GetDeleteList()
        {
            using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
            {
                conn.Delete<Test>(it => it.F_String == "Test");
                //插入1000条
                conn.SqlBulkCopy(GetList);
                //查询出插入的1000条
                var list = conn.Queryable<Test>().Where(it => it.F_String == "Test").ToList();
                return list;
            }
        }
        private static List<Test> GetList
        {
            get
            {
                List<Test> list = new List<Test>();
                for (int i = 1; i < 1000; i++)
                {
                    Test t = new Test()
                    {
                        F_Int32 = 1,
                        F_String = "Test",
                        F_Float = 1,
                        F_DateTime = DateTime.Now,
                        F_Byte = 1,
                        F_Bool = true
                    };
                    list.Add(t);
                }
                return list;
            }
        }
        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var ids = GetDeleteList().Select(it => it.Id).ToArray();
                    var list = conn.Delete<Test,int>(ids);
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var delList = GetDeleteList();
                    var list = conn.Delete(delList);
                }
            });
        }
    }
}
View Code

 

第六场:分页,项目使用最多的场景之一

 

SqlSugar 96分  Dapper 100

比赛结果:Dapper小胜

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class Page : IDemos
    {
 
        public void Init()
        {
            Console.WriteLine("分页");

            var eachCount = 3000;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {

                //dapper
                Dapper(eachCount);

                //sqlSugar
                SqlSugar(eachCount);
            }

        }

        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var list = conn.SqlQuery<Test>("select * from(select *,row_number() over(order by id) as r from test  ) t where t.r between @b and @e ", new { b = 1000, e = 1010 });
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var list = conn.Query<Test>("select * from(select *,row_number() over(order by id) as r from test  ) t where t.r between @b and @e ", new { b = 1000, e = 1010 });
                }
            });
        }
    }
}
View Code

 

第七场:比拼普通插入

SqlSugar 100分  Dapper 96

比赛结果: SqlSugar小胜 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using Models.PkDapper;
using SqlSugar;
using SyntacticSugar;
namespace PkDapper.Demos
{
    public class InsertItem : IDemos
    {

        public void Init()
        {
            Console.WriteLine("测试插入单条记录");

            var eachCount = 1000;

            /*******************车轮战是性能评估最准确的一种方式***********************/
            for (int i = 0; i < 10; i++)
            {
                //清除
                DeleteAddDatas();

                //dapper
                Dapper(eachCount);


                //清除
                DeleteAddDatas();

                //sqlSugar
                SqlSugar(eachCount);

            }
        }

        private static void DeleteAddDatas()
        {
            using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
            {
                conn.Delete<Test>(it => it.F_String == "Test");
            }
        }
        private static Test GetItem
        {
            get
            {
                    Test t = new Test()
                    {
                        F_Int32 = 1,
                        F_String = "Test",
                        F_Float = 1,
                        F_DateTime = DateTime.Now,
                        F_Byte = 1,
                        F_Bool = true
                    };
                return t;
            }
        }

        private static void SqlSugar(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>
            {
                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
                {
                    var list = conn.Insert(GetItem);
                }
            });
        }

        private static void Dapper(int eachCount)
        {
            GC.Collect();//回收资源
            System.Threading.Thread.Sleep(2000);//休息2秒

            //正试比拼
            PerHelper.Execute(eachCount, "Dapper", () =>
            {
                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
                {
                    var list = conn.Insert(GetItem);
                }
            });
        }
    }
}
View Code

 

 

第八场:比拼普通更新

SqlSugar 90分  Dapper 100

比赛结果:Dapper小胜

 

总结 

Dapper在批量操作上性能不尽人意,普通查询 增  删 和改 速度可以接受,想了解SqlSugar可以看这篇文章

http://www.cnblogs.com/sunkaixuan/p/5911334.html

 

测试代码:

有兴趣的朋友可以下载测试,也可以方便的加上其它ORM

https://github.com/sunkaixuan/SqlSugar

posted @ 2016-10-12 05:10  阿妮亚  阅读(16021)  评论(14编辑  收藏  举报