SqlCE与SQLite 性能测试

最近做快递项目，用到了PDA WinCe的系统 采用先本地保存 再进行上传的模式

但是客户提出了一个问题：操作慢

一直没有管 就这样一直运行着  就是最近 客户拿了一把新枪让我们开发

说我们的PDA设置不行 反应到领导那 决定找下原因

后来通过增加几个时间点 发现一个问题 扫描用时是60ms 插入一条数据600ms  然后再从数据库中查询出来显示到界面上用时900ms

 

比较可怕 用的SQLCe数据库

 

后来通过网上查阅 SQLite性能要高点  亲测 确实如果 测试代码如下

 

支行结果

单条插入SQLCE    170545ms

单条插入SQLite      22596ms

事务插入SQLCE        9272ms

事务插入SQLite          151ms

读取SQLCE              7544ms

读取SQLite              3384ms

 

 

无论从读取速度还是从插入速度上，SQLite都比SQLCE快很多 尤其是在事务方面

 

 DatabaseCE ce = new DatabaseCE();
            DatabaseSQLite lite = new DatabaseSQLite();
            int start = 0;
            int end = 0;
            int countCE = 0;
            int countLite = 0;
            string[] sqlTransCE = new string[1000];
            for (int i = 0; i < 1000; i++)//插入1000条数据到SQLCE中
            {
                string sqlInsert = "insert into TMsg(FromUserNo,ToUserNo,SendTime,MsgType,MsgContent,MsgStatus,SendTag,MsgUserName,AddUserNo,AddTime)"
                    + " values(" + i + "," + i + ",getdate(),0,'第" + i + "条',0,0,'User" + i + "',0,getdate())";
                sqlTransCE[i] = sqlInsert;
                start = Environment.TickCount;
                ce.ExecuteSQL(sqlInsert);
                end = Environment.TickCount;
                countCE += (end - start);
            }
            lbCE.Text = countCE + "ms";
            Application.DoEvents();

            string[] sqlTransLite = new string[1000];
            for (int i = 0; i < 1000; i++)//插入1000条数据到SQLite中
            {
                string sqlInsert = "insert into TMsg(FromUserNo,ToUserNo,SendTime,MsgType,MsgContent,MsgStatus,SendTag,MsgUserName,AddUserNo,AddTime)"
                    + " values(" + i + "," + i + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',0,'第" + i + "条',0,0,'User" + i + "',0,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                sqlTransLite[i] = sqlInsert;
                start = Environment.TickCount;
                lite.ExcuteSql(sqlInsert);
                end = Environment.TickCount;
                countLite += (end - start);
            }

            lbLite.Text = countLite + "ms";
            Application.DoEvents();

            start = Environment.TickCount;
            ce.ExecuteSQL(sqlTransCE);//事务插入1000条数据到SQLCE中
            end = Environment.TickCount;

            lbCET.Text=(end-start).ToString();
            Application.DoEvents();

            start = Environment.TickCount;
            lite.ExecuteSQL(sqlTransCE);//事务插入1000条数据到SQLite中
            end = Environment.TickCount;


            lbLiteT.Text = (end - start).ToString();
            Application.DoEvents();

            string sql = "Select * from TMsg where MsgStatus=0";
            start = Environment.TickCount;
            DataTable dt = ce.GetDataTable(sql);//从SQLCE中读取1000条数据
            end = Environment.TickCount;
            lbCEQ.Text = (end - start).ToString();
            Application.DoEvents();

            start = Environment.TickCount;
            DataTable dtLite = lite.GetDataTable(sql);//从SQLite中读取1000条数据
            end = Environment.TickCount;

            lbLiteQ.Text = (end - start).ToString();