Android SQLite 大量行

Question

需要在 SQLite 数据库中本地存储大约 20,000 行信息。只有两列包含短文本字符串，因此实际数据量很小，但我很难在设备上在 3-5 分钟内插入行。我试过直接循环遍历数据集并调用一个简单的 WritableDatabase.Insert，但这需要很长时间。所以我做了一些研究并被引导到the InsertHelper class作者站点处理行的速度约为 900 行/秒(这应该让我达到 20-30 秒)。我仍然无法让数据处理速度超过 3-5 分钟。我错过了什么？性能是否因设备而异？使用中兴 Optik Android 3.2.1。

public class SqLiteHelper : SQLiteOpenHelper
{
    private const string DATABASE_NAME = "NAME";
    private const int DATABASE_VERSION = 1;
    private readonly Context _context;

    public SqLiteHelper(Context context)
        : base(context, DATABASE_NAME, null, DATABASE_VERSION)
    {
        _context = context;
    }

    public override void OnCreate(SQLiteDatabase db)
    {
        try
        {
            db.ExecSQL("Create Table Inventory (ItemNumber Text Primary Key Not Null, ItemDescription Text);");
        }
        catch (SQLiteException ex)
        {
            Toast.MakeText(_context, ex.Message, ToastLength.Long).Show();
        }
    }

    public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
    }
}

class InventoryRepository : AsyncTask
{
    private SqLiteHelper Helper { get; set; }
    private Context Context { get; set; }

    public InventoryRepository(SqLiteHelper helper, Context context)
    {
        Helper = helper;
        Context = context;
    }

    protected override Java.Lang.Object DoInBackground(params Java.Lang.Object[] @params)
    {
       WS ws = new WS();
       DataTable parts = ws.GetInventory();  //Web service getting inventory items from Server

        Helper.WritableDatabase.Delete("Inventory", null, null);

        DatabaseUtils.InsertHelper ih = new DatabaseUtils.InsertHelper(Helper.WritableDatabase, "Inventory");

        Helper.WritableDatabase.SetLockingEnabled(false);

        int partColumn = ih.GetColumnIndex("ItemNumber");
        int partDescColumn = ih.GetColumnIndex("ItemDescription");

        Helper.WritableDatabase.BeginTransaction();

        try
        {

            foreach (DataRow part in parts.Rows)
            {
                try
                {
                    ih.PrepareForInsert();

                    ih.Bind(partColumn, part[0].ToString().Replace("'", "''"));
                    ih.Bind(partDescColumn, part[1].ToString().Replace("'", "''"));

                    ih.Execute();
                }
                catch (SQLiteException ex)
                {
                    if (ex.Message.Contains("constraint"))
                        continue;
                    throw;
                }
                catch (NullReferenceException e)
                {
                    continue;
                }
            }
            Helper.WritableDatabase.SetTransactionSuccessful();

        }
        finally
        {
            Helper.WritableDatabase.EndTransaction();
            Helper.WritableDatabase.SetLockingEnabled(true);
            ih.Close();
        }

        return "Done";
    }

    protected override void OnPostExecute(Java.Lang.Object result)
    {
        PreferenceManager.GetDefaultSharedPreferences(Context).Edit().PutString("LastInventoryUpdate", DateTime.Today.ToShortDateString()).Commit();
        Intent intent = new Intent(Context, typeof(Login));
        intent.AddFlags(ActivityFlags.NewTask);
        Context.StartActivity(intent);
    }
}

Answer 1

将整个插入循环包装在一个事务中。这将使速度加快一个数量级。

db.beginTransaction();
try {
    // your insertion loop goes here
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}