我有一个用DB浏览器制作的SQLite数据库。这个数据库是用于字典程序的,它包含很多列。我将这个数据库添加到Android studio中,并使用database Helper类读取它。加载这些列需要花费太多时间。有没有什么技巧可以减少加载时间?一个人告诉我使用"sqlite索引“。我怎样才能使用这个?
DataBaseHelper类:
public class DataBaseHelper extends SQLiteOpenHelper {
private static String DB_NAME = "database.sqlite";
private static String DB_PATH = "";
private SQLiteDatabase mydatabase;
private Context context;
public DataBaseHelper(Context context) {
super(context, DB_NAME, null, 1);
if (Build.VERSION.SDK_INT >= 15) {
DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
} else {
DB_PATH = Environment.getDataDirectory() + "/data/" + context.getPackageName() + "/databases/";
}
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public void checkAndCopyDatabase() {
boolean dbExist = checkDatabases();
if (dbExist) {
Log.d("TAG", "already exist");
} else {
this.getReadableDatabase();
}
try {
copyDatabases();
} catch (IOException e) {
e.printStackTrace();
Log.d("TAG", "Error copy DataBase");
}
}
public boolean checkDatabases() {
SQLiteDatabase checkdb = null;
try {
String myPath = DB_PATH + DB_NAME;
checkdb = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
} catch (SQLException e) {
}
if (checkdb != null) {
checkdb.close();
}
return checkdb != null ? true : false;
}
public void copyDatabases() throws IOException {
InputStream myInput = context.getAssets().open(DB_NAME);
String outFileName = DB_PATH + DB_NAME;
OutputStream myOutput = new FileOutputStream(outFileName);
byte[] buffer = new byte[2048];
int lenght;
while ((lenght = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, lenght);
}
}
public void openDatabase() {
String myPath = DB_PATH + DB_NAME;
mydatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
}
public synchronized void close() {
if (mydatabase != null) {
mydatabase.close();
}
super.close();
}
public Cursor QueryData(String query) {
return mydatabase.rawQuery(query, null);
}}
加载DataBase:
private void loadDatabase() {
dataBaseHelper = new DataBaseHelper(getContext());
try {
dataBaseHelper.checkAndCopyDatabase();
dataBaseHelper.openDatabase();
} catch (SQLException e) {
e.printStackTrace();
}
try {
cursor = dataBaseHelper.QueryData(" SELECT name FROM dictionary ");
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
item = new Info_Name_Main();
item.setName(cursor.getString(0));
arrayList.add(item);
} while (cursor.moveToNext());
}
}
} catch (SQLException e) {
e.printStackTrace();
}
LinearLayoutManager linearLayoutManager = new LinearLayoutManager(getContext());
adapterMain = new Adapter_Name_Main(getActivity(), arrayList);
recyclerView.setHasFixedSize(true);
recyclerView.setLayoutManager(linearLayoutManager);
recyclerView.setAdapter(adapterMain);
}
发布于 2019-02-19 03:54:43
从显示的代码来看,我不认为添加索引会有帮助。也就是说,对数据库的唯一访问是通过SELECT name FROM dictionary
,因此在没有任何索引(隐式或显式定义)的情况下,将使用基于行in的索引来扫描表,这应该是最有效的方式;
通过rowid对记录进行
访问是高度优化的,而且速度非常快。Rowid Tables
上面的代码假设字典不是视图。
如果花了太多时间。您应该调查一下it是什么。它真的是arrayList的一代吗?是不是数据的复制和数据库的打开结合在一起呢?我会从添加一些相关的日志开始,尝试根据时间来确定瓶颈在哪里。
要回答实际问题,请按照以下方式使用CREATE INDEX :-
SQL As Understood By SQLite- CREATE INDEX
与任何合理的SQlite管理器一样,DB浏览器将允许您以类似于创建表的方式创建它们。
https://stackoverflow.com/questions/54752229
复制相似问题