SqlSugar

SqlSugar

关注
发信
关注(28)粉丝(399)

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万条数据

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper分

比赛结果： 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 = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; i++)

            {

                //dapper

                Dapper(eachCount);

                //sqlSugar

                SqlSugar(eachCount);

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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();//休息2秒

            //正试比拼

            PerHelper.Execute(eachCount, "Dapper", () =>

            {

                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))

                {

                    var list = conn.GetAll<Test>();

                }

            });

        }

    }

}

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

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  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 SelectSingle : IDemos

    {

        /// <summary>

        /// 测试一次读取1条，每次执行1000次

        /// </summary>

        public void Init()

        {

            Console.WriteLine("测试一次读取1条");

            var eachCount = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; i++)

            {

                //dapper

                Dapper(eachCount);

                //sqlSugar

                SqlSugar(eachCount);

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息2秒

            PerHelper.Execute(eachCount, "SqlSugar", () =>

            {

                using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))

                {

                    var list = conn.Queryable<Test>().InSingle();

                }

            });

        }

        private static void Dapper(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息2秒

            //正试比拼

            PerHelper.Execute(eachCount, "Dapper", () =>

            {

                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))

                {

                    var list = conn.Get<Test>();

                }

            });

        }

    }

}

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

 Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper 分

比赛结果：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 = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; i++)

            {

                //dapper

                Dapper(eachCount);

                //sqlSugar

                SqlSugar(eachCount);

            }

        }

        private static List<Test> GetList

        {

            get

            {

                List<Test> list = new List<Test>();

                for (int i = ; i < ; i++)

                {

                    Test t = new Test()

                    {

                        Id=i,

                        F_Int32 = ,

                        F_String = "Test",

                        F_Float = ,

                        F_DateTime = DateTime.Now,

                        F_Byte = ,

                        F_Bool = true

                    };

                    list.Add(t);

                }

                return list;

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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();//休息2秒

            //正试比拼

            PerHelper.Execute(eachCount, "Dapper", () =>

            {

                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))

                {

                    var list = conn.Update(GetList);

                }

            });

        }

    }

}

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

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper 分

比赛结果：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 = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; 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 = ; i < ; i++)

                {

                    Test t = new Test()

                    {

                        F_Int32 = ,

                        F_String = "Test",

                        F_Float = ,

                        F_DateTime = DateTime.Now,

                        F_Byte = ,

                        F_Bool = true

                    };

                    list.Add(t);

                }

                return list;

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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();//休息2秒

            //正试比拼

            PerHelper.Execute(eachCount, "Dapper", () =>

            {

                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))

                {

                    var list = conn.Insert(GetList);

                }

            });

        }

    }

}

第五场：比拼批量删除

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper 分

比赛结果：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 = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; 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 = ; i < ; i++)

                {

                    Test t = new Test()

                    {

                        F_Int32 = ,

                        F_String = "Test",

                        F_Float = ,

                        F_DateTime = DateTime.Now,

                        F_Byte = ,

                        F_Bool = true

                    };

                    list.Add(t);

                }

                return list;

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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();//休息2秒

            //正试比拼

            PerHelper.Execute(eachCount, "Dapper", () =>

            {

                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))

                {

                    var delList = GetDeleteList();

                    var list = conn.Delete(delList);

                }

            });

        }

    }

}

第六场：分页，项目使用最多的场景之一

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper 分

比赛结果：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 = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; i++)

            {

                //dapper

                Dapper(eachCount);

                //sqlSugar

                SqlSugar(eachCount);

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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 = , e =  });

                }

            });

        }

        private static void Dapper(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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 = , e =  });

                }

            });

        }

    }

}

第七场：比拼普通插入

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper 分

比赛结果： 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 = ;

            /*******************车轮战是性能评估最准确的一种方式***********************/

            for (int i = ; i < ; 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 = ,

                        F_String = "Test",

                        F_Float = ,

                        F_DateTime = DateTime.Now,

                        F_Byte = ,

                        F_Bool = true

                    };

                return t;

            }

        }

        private static void SqlSugar(int eachCount)

        {

            GC.Collect();//回收资源

            System.Threading.Thread.Sleep();//休息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();//休息2秒

            //正试比拼

            PerHelper.Execute(eachCount, "Dapper", () =>

            {

                using (SqlConnection conn = new SqlConnection(PubConst.connectionString))

                {

                    var list = conn.Insert(GetItem);

                }

            });

        }

    }

}

 

第八场：比拼普通更新

Dapper ORM VS SqlSugar ORM的 8场对决-LMLPHP

SqlSugar 分  Dapper 分

比赛结果：Dapper小胜

总结

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

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

测试代码：

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

https://github.com/sunkaixuan/SqlSugar

05-04 03:26
Powered by LMLPHP ©2025 SqlSugar 0.064348