SQLite PCL数据库插入时间太长
本文关键字：时间 插入 PCL 数据库 SQLite | 更新日期: 2023-09-27 18:10:37
namespace App3
{
public sealed partial class App : Application
{
private TransitionCollection transitions;
public App()
{
this.InitializeComponent();
this.Suspending += this.OnSuspending;
}
public static async Task ReadFile(String file1, String table1)
{
using (var connection = new SQLiteConnection("Storage.db"))
{
string a;
var file = await StorageFile.GetFileFromApplicationUriAsync(new Uri(file1));
var stream = await file.OpenStreamForReadAsync();
using (StreamReader sr = new StreamReader(stream))
{
while (sr.Peek() >= 0)
{
a = sr.ReadLine();
string[] l = a.Split(',');
if (table1 == "commercial")
{
using (var statement = connection.Prepare(@"INSERT INTO " + table1 + " VALUES (''" + l[0] + "'',''" + l[1] + "'',''" + l[2] + "'','"" + l[3] + "'",'"" + l[4] + "'",''" + l[5] + "'',''" + l[6] + "'',''"
+ l[7] + "'',''" + l[8] + "'',''" + l[9] + "'',''" + l[10] + "''); ON DUPLICATE UPDATE ID=ID"))
{
statement.Step();
statement.Reset();
statement.ClearBindings();
}
}
else
{
using (var statement = connection.Prepare(@"INSERT INTO " + table1 + " VALUES (''" + l[0] + "'',''" + l[1] + "'',''" + l[2] + "'','"" + l[3] + "'",'"" + l[4] + "'",''" + l[5] + "'',''" + l[6] + "'',''"
+ l[7] + "'',''" + l[8] + "'',''" + l[9] + "''); ON DUPLICATE UPDATE ID=ID"))
{
// Inserts data.
statement.Step();
statement.Reset();
statement.ClearBindings();
}
}
}
}
}
}
public void createdb(String table){
using (var connection = new SQLiteConnection("Storage.db"))
{
using (var statement1 = connection.Prepare(@"DROP TABLE IF EXISTS " + table+";"))
{
statement1.Step();
statement1.Reset();
statement1.ClearBindings();
}
using (var statement = connection.Prepare(@"
CREATE TABLE IF NOT EXISTS "+table+" (id INT NOT NULL PRIMARY KEY,"
+ "TYPE VARCHAR(255),"
+"MAKE VARCHAR(255),"
+"MODEL VARCHAR(255),"
+"Fitment VARCHAR(255),"
+"Part VARCHAR(255),"
+"NRB_No VARCHAR(255),"
+"Dimension VARCHAR(255),"
+"No_Off INT,"
+"Company VARCHAR(255)"
+");"))
{
statement.Step();
}
}
}
protected async override void OnLaunched(LaunchActivatedEventArgs e)
{
createdb("Moped");
await ReadFile("ms-appx:///Assets/Mopeds.txt", "Moped");
createdb("Scooter");
await ReadFile("ms-appx:///Assets/Scooters.txt", "Scooter");
createdb("Motorcycle");
await ReadFile("ms-appx:///Assets/Motorcycles.txt", "Motorcycle");
createdb("Auto");
await ReadFile("ms-appx:///Assets/3W.txt", "Auto");
createdb("Passenger");
await ReadFile("ms-appx:///Assets/cars.txt", "Passenger");
createdb("Muv");
await ReadFile("ms-appx:///Assets/Muv.txt", "Muv");
createdb("Commercial");
await ReadFile("ms-appx:///Assets/Commercial.txt", "Commercial");
createdb("Tractor");
await ReadFile("ms-appx:///Assets/Tractors.txt", "Tractor");
}
这是我的app . example .cs我在onlaunching方法中添加表。使用本地文件夹中的文本文件填充表。但插入需要花费太多时间。每次插入大约有300行和9个条目，但需要花费时间。有办法加快速度吗?我做正确的还是有任何其他方法来玩数据库。我正在使用SQLITE PCL，这是Windows 8.1 RT
编辑
public static async Task ReadFile(String file1, String table1)
{
using (var connection = new SQLiteConnection("Storage.db"))
{
string a;
var file = await StorageFile.GetFileFromApplicationUriAsync(new Uri(file1));
var stream = await file.OpenStreamForReadAsync();
using (StreamReader sr = new StreamReader(stream))
{
while (sr.Peek() >= 0)
{
a = sr.ReadLine();
string[] l = a.Split(',');
try
{
using (var tr = connection.BeginTransaction())
{
using (SQLiteCommand cmd = connection.CreateCommand(@"DROP TABLE IF EXISTS " + table1 + ";"))
{
cmd.ExecuteNonQuery();
}
if (table1 == "commercial")
{
using (SQLiteCommand cmd1 = connection.CreateCommand(@"
CREATE TABLE IF NOT EXISTS " + table1 + " (id INT NOT NULL PRIMARY KEY,"
+ "TYPE VARCHAR(255),"
+ "MAKE VARCHAR(255),"
+ "MODEL VARCHAR(255),"
+ "Fitment VARCHAR(255),"
+ "Part VARCHAR(255),"
+ "NRB_No VARCHAR(255),"
+ "Dimension VARCHAR(255),"
+ "No_Off INT,"
+ "Company VARCHAR(255)"
+ ");"))
{
cmd1.ExecuteNonQuery();
}
using (SQLiteCommand cmd = connection.CreateCommand(@"INSERT INTO " + table1 + " VALUES (''" + l[0] + "'',''" + l[1] + "'',''" + l[2] + "'','"" + l[3] + "'",'"" + l[4] + "'",''" + l[5] + "'',''" + l[6] + "'',''"
+ l[7] + "'',''" + l[8] + "'',''" + l[9] + "'',''" + l[10] + "''); ON DUPLICATE UPDATE ID=ID"))
{
cmd.ExecuteNonQuery();
}
}
else
{
using (SQLiteCommand cmd1 = connection.CreateCommand(@"
CREATE TABLE IF NOT EXISTS " + table1 + " (id INT NOT NULL PRIMARY KEY,"
+ "TYPE VARCHAR(255),"
+ "MAKE VARCHAR(255),"
+ "MODEL VARCHAR(255),"
+ "Fitment VARCHAR(255),"
+ "Part VARCHAR(255),"
+ "NRB_No VARCHAR(255),"
+ "Dimension VARCHAR(255),"
+ "No_Off INT,"
+ "Company VARCHAR(255)"
+ "Usage VARCHAR(255)"
+ ");"))
{
cmd1.ExecuteNonQuery();
}
using (SQLiteCommand cmd = connection.CreateCommand(@"INSERT INTO " + table1 + " VALUES (''" + l[0] + "'',''" + l[1] + "'',''" + l[2] + "'','"" + l[3] + "'",'"" + l[4] + "'",''" + l[5] + "'',''" + l[6] + "'',''"
+ l[7] + "'',''" + l[8] + "'',''" + l[9] + "''); ON DUPLICATE UPDATE ID=ID"))
{
// Inserts data.
cmd.ExecuteNonQuery();
}
}
tr.Commit();
}
}
catch (SQLiteException ex)
{
tr.Rollback();
}
}
}
}
}
确保将insert封装在显式打开并提交的事务中。否则，SQLite将把每个语句包装在自己的事务中，这会降低性能。HTH .
编辑:另外，看看这个关于SQLite性能的一般性问题。
我知道这是相当晚，但我也面临着同样的问题在SQLite。Net-PCL图书馆。我在事务下一个接一个地插入记录，这就是为什么插入7k记录需要3-4分钟。现在我使用InsertAll函数，它已经下降到10秒的最大值。这是我的代码:
_connection.BeginTransaction();
var listPeople = new List<Table_People>();
foreach (var item in peopleList.People)
{
var peopleTable = new Table_People();
peopleTable.AccountNumber = item.AccountNumber;
peopleTable.FirstName = item.FirstName;
peopleTable.LastName = item.LastName;
peopleTable.MiddleName = item.MiddleName;
peopleTable.PersonID = item.ID;
listPeople.Add(peopleTable);
//_peopleManager.SaveTask(peopleTable);
}
_connection.InsertAll(listPeople);
_connection.Commit();
快乐编码。