[C#]遵守TSQL王道的TinyORM

2017-05-23

2861
0
C#
2017-05-24

使用過EF應該都知道所產生的TSQL一大長串(尤其新增一些累贅條件是我最討厭的)，

而且執行順序可能非預期(單一包交易中有insert、update、select同table，更容易產生deadlock)，

同時EF並無法產生SQL Server所內建高效率陳述式(如Merge)，

這時TinyORM主推所產生的TSQL絕對簡單並更貼近SQL Server，

且改善Dapper一些缺點和效能。

ps:目前無法支援.NET Core

TinyORM總大小49KB，雖然Dapper已經陪我經歷過無數大小專案，但我還是忍不住一窺TinyORM，

先是SequentialGuid完全符合SQL Server(以前我硬幹這件事)，避免索引碎片大幅產生，同時更快速也更省CPU，

為什麼預設GUID會有碎片問題，可參考[SQL SERVER][Memo]再談 Clustered Index，

交易預設啟用nocount和xact_abort，兄弟我要說xact_abort對交易真的超實用又重要，

也支援Batched CUD操作(沒有批次操作都可以跳過了)，

還有UPSERT簡直太棒了(如果你用過TSQL Merge才知道這陳述式的好)，

TVP support讓in-memory輕鬆達陣(參考[SQL SERVER]善用 In-Memory 資料表變數提高效能)，

這裡我簡單和Dapper比較bulk insert。

 public static async void DoAsync()
        {
            Console.WriteLine("Starting");
           

            var task1 = TinyORM(10000);
            var task2 = DapperORM(10000);

            var task3 = TinyORM(50000);
            var task4 = DapperORM(50000);

            var task5 = TinyORM(100000);
            var task6 = DapperORM(100000);
           

            await Task.WhenAll(task1, task2, task3, task4, task5, task6)
                .ContinueWith((_) =>
                {
                    _.Wait();
                    Console.WriteLine("End");
                }
            );

        }

static async Task<int> TinyORM(int numbers)
        {           
            Stopwatch sw = new Stopwatch();
            var db = DbContext.Create(connString);
          
            var batchinsert = QueryBatch.Create();
            for (int i = 0; i < numbers; i++)
            {
                batchinsert.AddQuery<myorders>(@"insert into myordersDisk(c1, c2,c3)
values(@c1, @c2, @c3);", new myorders { c1 = i, c2 = "rico" + i.ToString(), c3 = DateTime.Now });
               
            }
            try
            {         
                sw.Start();
                int result1 = await db.CommitQueryBatchAsync(batchinsert);
                //Console.WriteLine("result1:"+ result1.ToString());
                sw.Stop();
               
                Console.WriteLine(numbers.ToString()+" ElapsedTime(ms) of TinyORM:" + sw.ElapsedMilliseconds.ToString());
                await db.QueryAsync("truncate table myordersDisk");              
                Console.WriteLine("truncate table is done");
                
                return 1;
            }
            catch (Exception ex)
            {
                Console.WriteLine("ex:" + ex.Message);
               
                return -1;
            }
        }

static async Task<int> DapperORM(int numbers)
        {
           
            Stopwatch sw = new Stopwatch();
            List<myorders> datas = new List<myorders>();
            for (int i = 0; i < numbers; i++)
            {
                datas.Add(new myorders { c1 = i, c2 = "rico" + i.ToString(), c3 = DateTime.Now });
            }
            try
            {
                using (var cn = new SqlConnection(connString))
                {
                    cn.Open();
                    sw.Start();
                    using (SqlTransaction trans = cn.BeginTransaction())
                    {
                        cn.Execute(@"
                        insert into myordersDiskB(c1, c2,c3)
                        values(@c1, @c2,@c3)", datas, transaction: trans);
                        trans.Commit();
                      
                    }                   
                    sw.Stop();
                    await cn.ExecuteAsync("truncate table myordersDiskB");                  
                    Console.WriteLine("truncate table is done");
                }
                Console.WriteLine(numbers.ToString() + " ElapsedTime(ms) of DapperORM:" + sw.ElapsedMilliseconds.ToString());
                
                return 1;
            }
            catch (Exception ex)
            {
                Console.WriteLine("ex:" + ex.Message);
               
                return -1;
            }
        }

TinyORM完勝(資料量越大，差異越明顯)

Round 1