สรุปความเร็ว
Insert/Update เร็ว . File กว่า 20%
Select ไม่มี Where เร็วกว่า File 1000% หรือ 10 เท่า
Select มี Where เร็ว กว่า File 200-350% หรือ ประมาณ 3 เท่า
ตัวอย่าง Code
static void ReadData(SQLiteConnection conn)
{
SQLiteDataReader sqlite_datareader;
SQLiteCommand sqlite_cmd;
sqlite_cmd = conn.CreateCommand();
System.Random rng = new Random();
int rnNumber = rng.Next(1, 1000); // select by where
sqlite_cmd.CommandText = "SELECT * FROM Person Where ID="+rnNumber;
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
string myreader = sqlite_datareader.GetInt32(0).ToString();//.GetString(0);
//Console.WriteLine(myreader);
}
// conn.Close();
}
static void DropTable(SQLiteConnection conn)
{
SQLiteCommand sqlite_cmd;
string Createsql = "Drop TABLE Person;";
sqlite_cmd = conn.CreateCommand();
sqlite_cmd.CommandText = Createsql;
sqlite_cmd.ExecuteNonQuery();
}
private void button1_Click(object sender, EventArgs e)
{
using (SQLiteConnection connection = new SQLiteConnection(
"Data Source=:memory:;"
// "Data Source=C:/test/DataSQLite/myDb.db"
))
{
connection.Open();
// connection.CreateModule(new SQLiteModuleEnumerable(
// "sampleModule", new string[] { "one", "two", "three" }));
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText =
"CREATE TABLE IF NOT EXISTS Person (ID INTEGER,FirstName TEXT, LastName TEXT);";
command.ExecuteNonQuery();
}
using (SQLiteCommand command = connection.CreateCommand())
{
var stopwatch = new Stopwatch();
stopwatch.Start();
using (var cmd = new SQLiteCommand(connection))
{
using (var transaction = connection.BeginTransaction())
{
// 100,000 inserts
for (var i = 0; i < 1000000; i++)
{
cmd.CommandText =
string.Format("INSERT INTO Person (ID,FirstName, LastName) VALUES ({0},'SOM', 'LIVE');",i);
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
listBox1.Items.Add(string.Format("{0} seconds with one transaction.",
stopwatch.Elapsed.TotalSeconds));
// try select
stopwatch.Reset();
stopwatch.Start();
for (int i = 0; i < 100; i++)
{
ReadData(connection);
}
listBox1.Items.Add(string.Format("{0} seconds with one transaction.",
stopwatch.Elapsed.TotalSeconds));
stopwatch.Stop();
}
DropTable(connection);
connection.Close();
}
