SQLite和SQl-Server2005简单对比

本次测试的数据量是60000条数据

分别在两个DB的数据库中创建相同结的表：

 

 CREATE TABLE t_pincode
 (
     auto_no int IDENTITY(1,1) NOT NULL,
     pin_code varchar(50),
     batch_no varchar(50),
     code_status int NULL
 )

分别插入600000条数据进去，下面进行查询数据的性能对比：

SQLiteDB类

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;


namespace DataTool
{
    public class SQLiteDB
    {
        public void GetData()
        {
            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = "data source = d:\\mydb.bby";

            conn.Open();

            //SQLiteTransaction tran = conn.BeginTransaction();

            SQLiteCommand command = conn.CreateCommand();

            //try
            //{

                command.CommandType = CommandType.Text;
                command.CommandText = "select * from t_pincode";

                SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                int iCount = 0;

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        iCount++;
                    }
                }

                //tran.Commit();

                reader.Close();
                reader.Dispose();
                conn.Close();
                conn.Dispose();

                Console.WriteLine(string.Format("SQLite记录数：{0}", iCount));
            //}
            //catch (Exception)
            //{
            //    tran.Rollback();
            //}
        }
    }
}

 

SQLServerDB类：

 

 1using System;
 2using System.Collections.Generic;
 3using System.Linq;
 4using System.Text;
 5using System.Data;
 6using System.Data.SqlClient;
 7
 8namespace DataTool
 9{
    public class SQLServerDB
    {
        public void GetData()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "server=192.168.1.11;database=db_test_event;uid=db_test_event_user;pwd=0xBFBAE8B63072A7798E803AE9F7D4FFAA;pooling=true;max pool size=2000;";
            conn.Open();

            SqlCommand command = conn.CreateCommand();

            command.CommandType = CommandType.Text;
            command.CommandText = "select * from t_pincode with(nolock)";

            SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            int iCount = 0;

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    iCount++;
                }
            }

            reader.Close();
            reader.Dispose();
            command.Dispose();
            conn.Close();
            conn.Dispose();

            Console.WriteLine(string.Format("SQL-Server记录数：{0}", iCount));
            
        }

        public void GetDataByProc()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "server=192.168.1.11;database=db_test_event;uid=db_test_event_user;pwd=0xBFBAE8B63072A7798E803AE9F7D4FFAA;pooling=true;max pool size=2000;connection timeout=60;";

            conn.Open();

            SqlCommand command = conn.CreateCommand();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "tpp_test";

            SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            int iCount = 0;

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    iCount++;
                }
            }

            reader.Close();
            reader.Dispose();
            command.Dispose();
            conn.Close();
            conn.Dispose();

            Console.WriteLine(string.Format("SQL-Server记录数：{0}", iCount));
        }
    }
}

以下是调试代码：

 

 1using System;
 2using System.Collections.Generic;
 3using System.Linq;
 4using System.Text;
 5using System.Xml;
 6using System.Xml.Linq;
 7using DataTool;
 8using System.Diagnostics;
 9
namespace MyConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch watch2 = new Stopwatch();
            watch2.Start();
            SQLiteDB sqlite = new SQLiteDB();
            sqlite.GetData();
            watch2.Stop();

            Console.WriteLine(string.Format("sqlite花费的时间:{0}", watch2.ElapsedMilliseconds));

            Stopwatch watch1 = new Stopwatch();
            watch1.Start();
            SQLServerDB sqlserver = new SQLServerDB();
            sqlserver.GetDataByProc();
            watch1.Stop();

            Console.WriteLine(string.Format("sqlserver花费的时间:{0}", watch1.ElapsedMilliseconds));

            Console.ReadLine();
        }
    }
}

对比出来SQLite花费时间：462 SQL-Server花费时间：3281

这个只是一个简单的对比。可能中间有一些问题，欢迎大家指正。