一次LINQ性能研究

 

[Obsolete]
        public virtual string UpdateBySecondOld(string secondID, IEnumerable<string> firstIDs)
        {
            //Stopwatch sw = new Stopwatch();
            //sw.Start();
            TDataContext context = new TDataContext();
            TEntity t = new TEntity();
            var olds = context.GetTable<TEntity>().Where(t.SecondIDEquals(secondID));
            context.GetTable<TEntity>().DeleteAllOnSubmit(olds);
            List<TEntity> urList = new List<TEntity>();
            foreach (string firstID in firstIDs)
            {
                TEntity ur = new TEntity();
                ur.ID = Guid.NewGuid().ToString();
                ur.FirstID = firstID;
                ur.SecondID = secondID;
                urList.Add(ur);
            }
            context.GetTable<TEntity>().InsertAllOnSubmit(urList);
            context.SubmitChanges();
            //sw.Stop();
            //TimeSpan ts = sw.Elapsed;
            return Constances.ErrorMassage.SUCCEED;
        }

        public virtual string UpdateBySecond(string secondID, IEnumerable<string> firstIDs)
        {
            //Stopwatch sw = new Stopwatch();
            //sw.Start();
            TDataContext context = new TDataContext();
            TEntity t = new TEntity();
            string sql = "delete from " + TableName + " where " + t.SecondIDName + "='" + secondID + "'";
            string sqlInsert = "insert into  " + TableName + "(id," + t.FirstIDName + "," + t.SecondIDName + ") values({0},{1},{2})";
            context.Connection.Open();
            DbTransaction trans = context.Connection.BeginTransaction();
            context.Transaction = trans;
            try
            {
                context.ExecuteCommand(sql);
                foreach (string firstID in firstIDs)
                {
                    string guid = Guid.NewGuid().ToString();
                    context.ExecuteCommand(sqlInsert, guid, firstID, secondID);
                }
                context.Transaction.Commit();
                //sw.Stop();
                //TimeSpan ts = sw.Elapsed;
                return Constances.ErrorMassage.SUCCEED;
            }
            catch
            {
                context.Transaction.Rollback();
                return Constances.ErrorMassage.FAILED;
            }
        }

如上，UpdateBySecondOld為LINQ函數，UpdateBySecond後來用ADO.NET改寫的ADO.NET的函數。

分別傳入18個firstID，分別測試10次，結果如下：

 

次別	ADO.NET	LINQ
1	0.036	0.19
2	0.069	0.088
3	0.054	0.092
4	0.039	0.083
5	0.034	0.088
6	0.049	0.076
7	0.049	0.083
8	0.048	0.077
9	0.046	0.107
10	0.059	0.081
平均	0.0483	0.0965

 

可見，ADO.NET速度是LINQ的兩倍。

這主要是因為LINQ的更新，刪除，都不是直接的，而是先要獲取數據，這樣就降低了效率。

用LINQ查詢，因該差別不大。