sqlite3 java 内存_SQLite插入：内存：性能

我正在寻找一个使用SQLite的内存表的“缓存”解决方案 . 快速插入和快速选择是高优先级 .

问题是我没有得到我在SQLite中期望的插入速度 .

我正在对SQLite内存表进行基准测试，而将BulkCopyInserts编入SQL Server数据库 . 我可以将速度为50 000记录/秒的100,000条记录BulkCopy到表中，但是使用SQLite我的速度为12 500 rec / sec .

知道我可以做些什么来提高性能吗？没有循环并为每条记录执行插入，是否无法将数据推送到表中？

/埃里克

private void SQLLiteRunButton_Click(object sender, EventArgs e)

{

int noOfRecords = Convert.ToInt32(NoOfRecordsTextBox.Text);

SQLLiteListBox.Items.Add("Start generating "+noOfRecords+" in memory");

DataTable table = GetTable(noOfRecords);

var dbConnection = new SQLiteConnection("Data Source=:memory:;Version = 3;");

dbConnection.Open();

//Create Target Table

string sql = "CREATE TABLE LedgerSumTemp([RowID] [int] NOT NULL,[Client_ID] [nvarchar](50) NULL,[ISOMONTH] [nvarchar](50) NULL,[Date] [datetime] NULL, [Dim1] [nvarchar](50) NULL,[Dim2] [nvarchar](50) NULL, [Dim3] [nvarchar](50) NULL, [Dim4] [nvarchar](50) NULL, [Amount] [decimal](18, 6) NULL)";

SQLiteCommand command = new SQLiteCommand(sql, dbConnection);

command.ExecuteNonQuery();

//Set insertTimeStart

Stopwatch stopwatch = new Stopwatch();

stopwatch.Start();

//Fill table with data fast - Alternative 2 – using params

using (var cmd = new SQLiteCommand(dbConnection))

{

using (var transaction = dbConnection.BeginTransaction())

{

cmd.CommandText = "INSERT INTO LedgerSumTemp (RowID, Client_ID, ISOMONTH, Date, Dim1, Dim2, Dim3, Dim4, Amount) VALUES (@RowID,@Client_ID,@ISOMONTH,@Date,@Dim1,@Dim2,@Dim3,@Dim4,@Amount);";

cmd.CommandType = CommandType.Text;

foreach (DataRow row in table.Rows)

{

cmd.Parameters.Add(new SQLiteParameter("@RowID", row["RowID"]));

cmd.Parameters.Add(new SQLiteParameter("@Client_ID", row["Client_ID"]));

cmd.Parameters.Add(new SQLiteParameter("@ISOMONTH", row["ISOMONTH"]));

cmd.Parameters.Add(new SQLiteParameter("@Date", row["Date"]));

cmd.Parameters.Add(new SQLiteParameter("@Dim1", row["Dim1"]));

cmd.Parameters.Add(new SQLiteParameter("@Dim2", row["Dim2"]));

cmd.Parameters.Add(new SQLiteParameter("@Dim3", row["Dim3"]));

cmd.Parameters.Add(new SQLiteParameter("@Dim4", row["Dim4"]));

cmd.Parameters.Add(new SQLiteParameter("@Amount", row["Amount"]));

cmd.ExecuteNonQuery();

}

transaction.Commit();

}

}

stopwatch.Start();

int mS = Convert.ToInt32(stopwatch.Elapsed.TotalMilliseconds);

double s = mS / 1000;

double recPerSec = (noOfRecords / s);

SQLLiteListBox.Items.Add(noOfRecords.ToString()+" Time: " + mS.ToString()+ " mSec"+" Speed: "+recPerSec.ToString() +" RecPerSec");

SQLLiteListBox.Items.Add("--------END-------");

}

static DataTable GetTable(int NoOfRows)

{

int noOfRows = NoOfRows;

//Create DataTable in memory

DataTable table = new DataTable();

table.TableName = "LedgerSumTemp";

table.Columns.Add("RowID", typeof(int));

table.Columns.Add("Client_ID", typeof(string));

table.Columns.Add("ISOMONTH", typeof(string));

table.Columns.Add("Date", typeof(DateTime));

table.Columns.Add("Dim1", typeof(string));

table.Columns.Add("Dim2", typeof(string));

table.Columns.Add("Dim3", typeof(string));

table.Columns.Add("Dim4", typeof(string));

table.Columns.Add("Amount", typeof(double));

//Add rows

for (int i = 0; i < noOfRows; i++)

{

table.Rows.Add(i, "10", "2013"+i.ToString(), DateTime.Now, "3010", "110", "A58", "1000", Convert.ToDouble(i));

}

return table;

}