Android - 填充 sqlite 表需要很长时间

Question

几个月前我遇到了这个问题，现在是时候回到它了。

我将电话的通话记录查询到数据库中，但填充表格大约需要 30 秒。看起来查询需要大约 1 秒，但人口需要永远，尽管电话只存储最后 500 个电话。为什么这么慢？我做错了什么吗？

我只在我的手机上测试它，因为我在模拟器的通话记录中只有 8 个项目。

  final String[] projection = null;

            HotOrNot infoA = new HotOrNot(Charts.this);
            infoA.open();
            infoA.createtable_Calls();
            infoA.deleteAllEntries_Calls();
            infoA.close();

            final Context context = getApplicationContext();
            final String selection = null;
            final String sortOrder = android.provider.CallLog.Calls.DATE + " DESC";

            Cursor c = context.getContentResolver().query(android.provider.CallLog.Calls.CONTENT_URI, projection, selection, null, sortOrder);
            while (c.moveToNext()) { 
                String callLogID = c.getString(c.getColumnIndex(android.provider.CallLog.Calls._ID));

                int numberColumn = c.getColumnIndex(android.provider.CallLog.Calls.NUMBER);
                int dateColumn = c.getColumnIndex(android.provider.CallLog.Calls.DATE);
                int typeColumn = c.getColumnIndex(android.provider.CallLog.Calls.TYPE);
                int durationColumn = c.getColumnIndex(android.provider.CallLog.Calls.DURATION);
                int person = c.getColumnIndex(android.provider.CallLog.Calls.CACHED_NAME);



                String number = c.getString(numberColumn);
                int duration = c.getInt(durationColumn);
                String personname = c.getString(person);
                long callDate = c.getLong(dateColumn);
                int callType = c.getInt(typeColumn);

                if (duration >= 0)
                {
                    switch (callType) {
                    case 1:
                        duration_in = duration;
                        duration_out = 0;
                        break;
                    case 2:
                        duration_out = duration;
                        duration_in = 0;
                        break;
                    case 3:
                        duration_in = 0;
                        duration_out = 0;
                        break;


                    }
                    }

    //Here comes the slow part

                    HotOrNot info = new HotOrNot(Charts.this);
                    info.open();
                    info.pop   
ulate_Calls(personname, number, String.valueOf(callType), Integer.toString(duration), Long.toString(callDate), callLogID);
                info.close();   
             }

这是填充函数:

public long populate_Calls(String name, String phone, String type, String duration, String date, String contactid) {
        ContentValues cv = new ContentValues();
        cv.put(KEY_NAME, name);
        cv.put(KEY_PHONE, phone);
        cv.put(KEY_TYPE, type);
        cv.put(KEY_DURATION, duration);
        cv.put(KEY_DATE, date);
        cv.put(KEY_CONTACTID, contactid);
        return ourDatabase.insert(DATABASE_TABLE, null, cv);        
    }

编辑:

对于 Andreas Ka 和 twaddington 的回答，我修改了 SQLiteOpenHelper 类中的 population 方法，但不幸的是，它并没有什么不同:

public long populate_Calls(String name, String phone, String type, String duration, String date, String contactid) {
    ContentValues cv = new ContentValues();
     try {
         ourDatabase.beginTransaction();

         cv.put(KEY_NAME, name);
         cv.put(KEY_PHONE, phone);
         cv.put(KEY_TYPE, type);
         cv.put(KEY_DURATION, duration);
         cv.put(KEY_DATE, date);
         cv.put(KEY_CONTACTID, contactid);

         ourDatabase.yieldIfContendedSafely();

         ourDatabase.setTransactionSuccessful();
     } finally {
         ourDatabase.endTransaction();
     }

    return ourDatabase.insert(DATABASE_TABLE, null, cv);        
}

编辑 2:根据 Babibu 和 twaddington 的回答发布整个代码。顺便说一下，temp_ 数组现在是 LinkedList，但这不会在时间上产生影响。

 final String[] projection = null;
        final Context context = getApplicationContext();
        final String selection = null;
        final String sortOrder = android.provider.CallLog.Calls.DATE + " DESC";
        lv1 = (ListView) findViewById(R.id.ListView02); 


        HotOrNot infoA = new HotOrNot(Calllogs.this);
        infoA.open();
        infoA.createtable_Calls();
        infoA.deleteAllEntries_Calls();
        infoA.close();

          pd = ProgressDialog.show(Calllogs.this, "Please wait..", "Loading data, it may take a few" +
                " seconds based on the number of data.", false, true);

        Cursor c = context.getContentResolver().query(android.provider.CallLog.Calls.CONTENT_URI, projection, selection, null, sortOrder);
        while (c.moveToNext()) { 
            String callLogID = c.getString(c.getColumnIndex(android.provider.CallLog.Calls._ID));

            int numberColumn = c.getColumnIndex(android.provider.CallLog.Calls.NUMBER);
            int dateColumn = c.getColumnIndex(android.provider.CallLog.Calls.DATE);
            int typeColumn = c.getColumnIndex(android.provider.CallLog.Calls.TYPE);
            int durationColumn = c.getColumnIndex(android.provider.CallLog.Calls.DURATION);
            int person = c.getColumnIndex(android.provider.CallLog.Calls.CACHED_NAME);



            String number = c.getString(numberColumn);
            int duration = c.getInt(durationColumn);
            String personname = c.getString(person);
            long callDate = c.getLong(dateColumn);
            int callType = c.getInt(typeColumn);

            if (duration >= 0)
            {
                switch (callType) {
                case 1:
                    duration_in = duration;
                    duration_out = 0;
                    break;
                case 2:
                    duration_out = duration;
                    duration_in = 0;
                    break;
                case 3:
                    duration_in = 0;
                    duration_out = 0;
                    break;
                }
            }

            temp_name.add(personname);
            temp_num.add(number);
            temp_type.add(String.valueOf(callType));
            temp_dur.add(Integer.toString(duration));
            temp_date.add(String.valueOf(callDate));
            temp_id.add(callLogID);
          } //end of while loop


        HotOrNot infotemp = new HotOrNot(Calllogs.this);
        infotemp.open();


            for (int i=0; i<temp_name.size(); i++)
            {
                infotemp.populate_Calls(temp_name.get(i), temp_num.get(i), temp_type.get(i), temp_dur.get(i), temp_date.get(i), temp_type.get(i));
            }
 infotemp.close();

解决方案

我正在发布 twaddington 的解决方案，它将时间从 8 秒减少到不到 2 秒:

 HotOrNot infotemp = new HotOrNot(Calllogs.this);
        infotemp.open();

        // Get our database. You can do this however you wish, but
        // it seems like since the database is contained in your `HotOrNot`
        // object, it would be best to simply add a getter method to
        // the class.
        SQLiteDatabase db = infotemp.getDatabase();

        try {
            // Begin our transaction
            db.beginTransaction();

            // Loop over the array of calls and
            // perform a db insert for each.
            for (int i=0; i<temp_name.size(); i++) {
                // Yield the database lock if requested. This will
                // temporarily suspend our loop, but it should
                // continue when the lock is opened.
                db.yieldIfContendedSafely();

                infotemp.populate_Calls(temp_name.get(i), temp_num.get(i),
                        temp_type.get(i), temp_dur.get(i), temp_date.get(i), temp_type.get(i));
            }

            // Mark our transaction as successful!
            db.setTransactionSuccessful();
        } finally {
            // Always end the transaction!
            db.endTransaction();
        }

        infotemp.close();

Answer 1

对于您对 SQLite 数据库所做的每一次更改，都会有一系列复杂的发生步骤，包括创建 journal file回滚如果发生错误，请更改。您可以将一系列更新包装在数据库中强制 SQLite 将整个系列视为单个操作的事务。这样效率会高很多。

try {
    db.beginTransaction();
    while (c.moveToNext()) {
        // Yield the database lock if requested
        db.yieldIfContendedSafely();

        // Add your code here!
        // ...

        // Perform the database insert
        populate_Calls(...);
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}