Android事件：APP_SCOUT_HANG警告SQLite

该警告似乎是由于主线程上的延迟太多造成的.关闭数据库，然后重新打开数据库是相对耗费资源的.

此外，像每个INSERT一样执行许多单个事务本身将是低效的.

您不仅不应该关闭和重新打开数据库，而且可能应该考虑在单个事务中执行循环中的所有插入.

• 也许还考虑到AUTOINCREMENT可能不是必需的，因为仍将生成id列.AUTOINCREMENT增加了另一个增加资源使用的因素.见https://www.sqlite.org/autoinc.html(第一段对此方面提出警告).

或许可以考虑以下几点(即基于您的代码的loosely个)

为了便于不获取数据库的实例，insertData方法的两个版本:-

void insertData(SQLiteDatabase db, String kateg, String fullname, String version) {
    if (db == null) db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_2, kateg);
    contentValues.put(COL_3, fullname);
    contentValues.put(COL_4, version);
    db.insert(TABLE_NAME, null, contentValues);
}

void insertData(String kateg, String fullname, String version) {
    insertData(null, kateg, fullname, version);
}

• 请注意，后者不需要传递Current/In Use SQliteDatabase，因为它随后调用core/first insertData方法，并将SQLiteDatabase设置为空(因此将获得一个SQLiteDatabase).

不使用clearTable方法，因为删除行可能更有效(删除表无论如何都必须这样做).相反，删除被嵌入到建议的insertAfterClearIfApplicable方法中(显然，可以根据需要更改方法名称)

• Note为了满足答案的简洁性和至少语法判断的需要，传递了电影列表而不是响应.显然，您可能希望进行相应的修改.答案的真正意图是展示什么可能是更有效的方法，即

1. 删除所有行而不是删除表
2. 将所有数据库操作包含在单个事务中

密码:-

void insertAfterClearIfApplicable(List<Movie> movieList, int liveVersion) {
    if (movieList == null || movieList.size() < 1) return; /* nothing to do so return */
    SQLiteDatabase db = this.getWritableDatabase(); /* get the SQLiteDatabase */
    db.beginTransaction(); /* Start a transaction */
    boolean allDoneOk = true; /*<<<<<<<<<< set to false if the transaction should be rolled back */
    if (movieList.size() > 0) {
        /* Handle the first element i.e. whether or not to delete all rows from the table */
        int version = Integer.parseInt(movieList.get(0).getVersion());
        if (liveVersion > version || version == 0) {
            /* might as well use delete instead of drop as drop has to delete all rows anyway
             *  and saves a little by not having to then create the table again */
            db.delete(TABLE_NAME, null, null);
        }
        /* Insert all of the rows to be added */
        for (Movie m : movieList) {
            insertData(db, m.getKateg(), m.getFullName(), m.getVersion());
        }
        /* unless otherwise flagged set the transaction to NOT rollback */
        /* note if not set as successful then ALL database actions will be rolled back */
        if (allDoneOk) db.setTransactionSuccessful();
     /* end the transaction will commit/write all the actions to the database (if set as susccessful) in a single go
        thus reducing the overheads */
        db.endTransaction();
    }
}

• 同样，please note以上是一个近似值，使用的是List<Movie>而不是响应体，因此应该是adapted/altered accordingly.
  • 即答案旨在演示技术，而不是实际使用.

Demonstration个

同样，这不是复制代码的确切try ，而是主要突出建议的单个事务和多个事务的效率的近似值.

首先是DatabaseHelper类(上面，但为计时添加了日志(log)记录):-

class DatabaseHelper extends SQLiteOpenHelper {
    public static final String TABLE_NAME = "thetable";
    public static final String COL_2 = "KATEG";
    public static final String COL_3 = "FULLNAME";
    public static final String COL_4 = "VERSION";

    public DatabaseHelper(Context context) {
        super(context, "the_database.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, KATEG TEXT, FULLNAME TEXT, VERSION TEXT)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    void clearTable() {
        Log.d(MainActivity.TAG,"Starting clearTable Method (DROP THEN CREATE)");
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        db.execSQL("CREATE TABLE " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, KATEG TEXT, FULLNAME TEXT, VERSION TEXT)");
        /* db.close(); */
        Log.d(MainActivity.TAG,"Ending clearTable Method");
    }

    void insertData(SQLiteDatabase db, String kateg, String fullname, String version) {
        if (db == null) db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2, kateg);
        contentValues.put(COL_3, fullname);
        contentValues.put(COL_4, version);
        db.insert(TABLE_NAME, null, contentValues);
    }

    void insertData(String kateg, String fullname, String version) {
        insertData(null, kateg, fullname, version);
    }

    void insertAfterClearIfApplicable(List<Movie> movieList, int liveVersion) {
        Log.d(MainActivity.TAG,"Starting CORE INSERTAFTERCLEARIFAPPLICABLE Method"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
        if (movieList == null || movieList.size() < 1) return; /* nothing to do so return */
        SQLiteDatabase db = this.getWritableDatabase(); /* get the SQLiteDatabase */
        db.beginTransaction(); /* Start a transaction */
        boolean allDoneOk = true; /*<<<<<<<<<< set to false if the transaction should be rolled back */
        if (movieList.size() > 0) {
            /* Handle the first element i.e. whether or not to delete all rows from the table */
            int version = Integer.parseInt(movieList.get(0).getVersion());
            if (liveVersion > version || version == 0) {
                Log.d(MainActivity.TAG,"Start deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
                /* might as well use delete instead of drop as drop has to delete all rows anyway
                 *  and saves a little by not having to then create the table again */
                db.delete(TABLE_NAME, null, null);
                Log.d(MainActivity.TAG,"End deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
            }
            /* Insert all of the rows to be added */
            Log.d(MainActivity.TAG,"Start INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
            for (Movie m : movieList) {
                insertData(db, m.getKateg(), m.getFullName(), m.getVersion());
            }
            Log.d(MainActivity.TAG,"End INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
            /* unless otherwise flagged set the transaction to NOT rollback */
            /* note if not set as successful then ALL database actions will be rolled back */
            if (allDoneOk) db.setTransactionSuccessful();
         /* end the transaction will commit/write all the actions to the database (if set as susccessful) in a single go
            thus reducing the overheads */
            db.endTransaction();
            Log.d(MainActivity.TAG,"Ending CORE INSERTAFTERCLEARIFAPPLICABLE DATA Method (transaction ended)"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
        }
    }
}

MainActivity中的第二个测试代码:-

public class MainActivity extends AppCompatActivity {
    public static final String TAG = "DBINFO";
    DatabaseHelper myDb;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myDb = new DatabaseHelper(this);
        myDb.getReadableDatabase(); /* Force open and thus onCreate so negate associated overheads */
        Response t1 = buildTestResponse(1,1000,0);
        tryItOut(1,t1,false);
        tryItOut(2,t1,true);
    }
    /*
        if (movieList != null) {
                if (response.isSuccessful() && movieList.size() > 0) {int liveVersion = 0;

                    liveVersion = Integer.parseInt(movieList.get(0).getVersiondb());

                    if (liveVersion>version || version==0) { myDb.clearTable();

                        for (int i = 0; i < movieList.size(); i++) {
                            myDb.insertData(movieList.get(i).getKateg(),movieList.get(i).getFullname(), movieList.get(i).getVersiondb());
                        }
                    }
                } }
     */

    /* Build some testing data */
     Response buildTestResponse(int liveVersion, int moviesToGenerate, int passedMovieVersion) {
         ArrayList<Movie> m = new ArrayList<>();
         int movieVersion=0;
         for (int i=0; i < moviesToGenerate;i++) {
             if (passedMovieVersion < 0) {
                 movieVersion = new Random().nextInt( liveVersion * movieVersion);
             }
             m.add(new Movie(String.valueOf(movieVersion),"KATEG" + i,"FNAME" + i));
         }
         return new Response(liveVersion,m);
     }
     /* TESTING approximation with logging for timings */
     void tryItOut(int testNumber, Response response, boolean newWay) {
         Log.d(TAG,"Starting TEST " + String.valueOf(testNumber));
         if (newWay) {
             myDb.insertAfterClearIfApplicable(response.getBody(),response.getLiveVersion());
         } else {
             if (response.getLiveVersion() > Integer.parseInt(response.getBody().get(0).getVersion())) {
                 myDb.clearTable();
                 Log.d(TAG,"Starting TEST INSERT LOOP (OLDWAY) " + String.valueOf(testNumber));
                 for (Movie m: response.getBody()) {
                     myDb.insertData(m.getKateg(),m.getFullName(),m.getVersion());
                 }
                 Log.d(TAG,"Ending TEST INSERT LOOP (OLDWAY) " + String.valueOf(testNumber));
             }
         }
         Log.d(TAG,"END of TEST " + String.valueOf(testNumber));
     }
}

• Note个Response和Movie类是为演示而创建的近似类(为简洁起见).

Results个

论证的目的是提供资源使用差异的例子，该差异影响建议的单一交易方式与原始问题中的方式之间的时间差.运行中的日志(log)(按顺序同时使用旧方法和新方法)为:

2024-01-22 11:30:59.323 D/DBINFO: Starting TEST 1
2024-01-22 11:30:59.323 D/DBINFO: Starting clearTable Method (DROP THEN CREATE)
2024-01-22 11:30:59.325 D/DBINFO: Ending clearTable Method
2024-01-22 11:30:59.326 D/DBINFO: Starting TEST INSERT LOOP (OLDWAY) 1
2024-01-22 11:30:59.748 D/DBINFO: Ending TEST INSERT LOOP (OLDWAY) 1
2024-01-22 11:30:59.748 D/DBINFO: END of TEST 1
2024-01-22 11:30:59.748 D/DBINFO: Starting TEST 2
2024-01-22 11:30:59.748 D/DBINFO: Starting CORE INSERTAFTERCLEARIFAPPLICABLE Method
2024-01-22 11:30:59.748 D/DBINFO: Start deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method
2024-01-22 11:30:59.748 D/DBINFO: End deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method
2024-01-22 11:30:59.748 D/DBINFO: Start INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)
2024-01-22 11:30:59.897 D/DBINFO: End INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)
2024-01-22 11:30:59.897 D/DBINFO: Ending CORE INSERTAFTERCLEARIFAPPLICABLE DATA Method (transaction ended)
2024-01-22 11:30:59.897 D/DBINFO: END of TEST 2

• 通过DROP然后CREATE(旧方法)清除表需要2毫秒.新的方式(删除所有行)在1毫秒(第2行和第3行)内完成.
• 用旧方法插入全部1000行(每个插入一个事务)需要422毫秒.新的/建议的方式(单个事务中的所有插入)需要149毫秒.

EVEN STILL使用主线程进行数据库访问是不受欢迎的，您可能希望考虑通过另一个线程进行数据库访问.