文章目录

在做android SQLite数据插入性能优化时,前面一篇android SQLite性能优化(一)——使用事务和预处理提到了使用事务和预处理做优化,本篇介绍使用NDK的方式进行数据插入优化。
使用NDK操作数据库时,需要使用sqlite的官方源码,我这里测试使用的是官方的3.8.7.4版本的代码。同样在使用sqlite操作数据库时使用了事务和预处理。

下面是Java层的代码,主要是将List数据转换成数组数据,指定数据库文件路径,及数组数据和长度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
void insertNative(List<DemoItem> data) {
Log.d(TAG, "databases path: " + getCacheDir());
final int size = data.size();
DemoItem[] items = new DemoItem[size];
data.toArray(items);
File file = new File(getCacheDir(), "tel.db");
long start = System.currentTimeMillis();
TelTools.insertNative(file.getAbsolutePath(), items, items.length);
long end = System.currentTimeMillis();
Log.d(TAG, "used time: " + (end - start) + ", nums: " + items.length + ", average item: " + (end - start) / items.length);
// 使用预处理
// used time: 2372, nums: 65536, average item: 0
// used time: 2422, nums: 65536, average item: 0
}

整个耗时2.4秒左右,是使用Java的方式耗时的一半都不到。下面是C代码,可以看出来,使用C代码操作数据库需要做很多事情,远没有Java来的方便;如果数据库结构发生修改,就更难维护了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
JNIEXPORT jint JNICALL Java_com_example_sqlitedemo_TelTools_insertNative
(JNIEnv *env, jclass jcls, jstring jdbPath, jobjectArray jarr, jint jlen)
{
if (jlen <= 0) {
LOGW("len must > 0");
return -1;
}
const char* sdbPath = (*env)->GetStringUTFChars(env, jdbPath, NULL);
sqlite3* db;
int result = 0;
sqlite3_initialize( );
result = sqlite3_open_v2(sdbPath, &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
if (SQLITE_OK != result) {
LOGE("sqlite3_open failed, result: %d, error msg: %s", result, sqlite3_errmsg(db));
return result;
}
const char* createSQL = "CREATE TABLE tel(_id INTEGER PRIMARY KEY, id TEXT, tel TEXT, code TEXT, address TEXT, type TEXT, date INTEGER)";
char* pErrorMsg;
result = sqlite3_exec(db, createSQL, NULL, NULL, &pErrorMsg);
if (SQLITE_ERROR != result && SQLITE_OK != result) {
LOGE("exec sql(%s) failed, result: %d, error msg: %s", createSQL, result, pErrorMsg);
sqlite3_free(pErrorMsg);
sqlite3_close(db);
return result;
}
jclass cItem = (*env)->FindClass(env, "com/example/sqlitedemo/mode/DemoItem");
jfieldID fID = (*env)->GetFieldID(env, cItem, "id", "Ljava/lang/String;");
jfieldID fTel = (*env)->GetFieldID(env, cItem, "tel", "Ljava/lang/String;");
jfieldID fCode = (*env)->GetFieldID(env, cItem, "code", "Ljava/lang/String;");
jfieldID fAddress = (*env)->GetFieldID(env, cItem, "address", "Ljava/lang/String;");
jfieldID fType = (*env)->GetFieldID(env, cItem, "type", "Ljava/lang/String;");
jfieldID fDate = (*env)->GetFieldID(env, cItem, "date", "J");
//const int sqlLen = 512;
//char sql[sqlLen];
const char* insertSQL = "insert into tel(id, tel, code, address, type, date) values(?,?,?,?,?,?)";
const char* beginSQL = "begin;";
const char* commitSQL = "commit;";
const char* rollbackSQL = "rollback;";
sqlite3_exec(db, beginSQL, NULL, NULL, NULL);
//string sql;
int isSuccess = 1;
jobject obj;
jstring sId, sTel, sCode, sAddress, sType;
sqlite3_stmt *stmt = NULL;
sqlite3_prepare_v2(db, insertSQL, strlen(insertSQL) * 2, &stmt, NULL);
int i;
for (i = 0; i < jlen; i++) {
obj = (*env)->GetObjectArrayElement(env, jarr, i);
sId = (jstring) ((*env)->GetObjectField(env, obj, fID));
const char* id = (*env)->GetStringUTFChars(env, sId, NULL);
sTel = (jstring) ((*env)->GetObjectField(env, obj, fTel));
const char* tel = (*env)->GetStringUTFChars(env, sTel, NULL);
sCode = (jstring) ((*env)->GetObjectField(env, obj, fCode));
const char* code = (*env)->GetStringUTFChars(env, sCode, NULL);
sAddress = (jstring) ((*env)->GetObjectField(env, obj, fAddress));
const char* address = (*env)->GetStringUTFChars(env, sAddress, NULL);
sType = (jstring) ((*env)->GetObjectField(env, obj, fType));
const char* type = (*env)->GetStringUTFChars(env, sType, NULL);
(*env)->DeleteLocalRef(env, obj);
//jstring sDate = (jstring) (env->GetObjectField(obj, fTel));
//const char* tel = env->GetStringUTFChars(sTel, NULL);
sqlite3_bind_text(stmt, 0, id, strlen(id), NULL);
sqlite3_bind_text(stmt, 1, tel, strlen(tel), NULL);
sqlite3_bind_text(stmt, 2, code, strlen(code), NULL);
sqlite3_bind_text(stmt, 3, address, strlen(address), NULL);
sqlite3_bind_text(stmt, 4, type, strlen(type), NULL);
sqlite3_bind_text(stmt, 5, "10000000", 8, NULL);
sqlite3_step(stmt);
sqlite3_reset(stmt);
sqlite3_clear_bindings(stmt);
//LOGD("sql: %s", sql.c_str());
//result = sqlite3_exec(db, sql, NULL, NULL, &pErrorMsg);
//sql.clear();
(*env)->ReleaseStringUTFChars(env, sId, id);
(*env)->ReleaseStringUTFChars(env, sTel, tel);
(*env)->ReleaseStringUTFChars(env, sCode, code);
(*env)->ReleaseStringUTFChars(env, sAddress, address);
(*env)->ReleaseStringUTFChars(env, sType, type);
(*env)->DeleteLocalRef(env, sId);
(*env)->DeleteLocalRef(env, sTel);
(*env)->DeleteLocalRef(env, sCode);
(*env)->DeleteLocalRef(env, sAddress);
(*env)->DeleteLocalRef(env, sType);
// isSuccess = result == 0;
// if (!isSuccess) {
// break;
// }
}
if (isSuccess) {
sqlite3_exec(db, commitSQL, NULL, NULL, NULL);
} else {
sqlite3_exec(db, rollbackSQL, NULL, NULL, NULL);
}
(*env)->ReleaseStringUTFChars(env, jdbPath, sdbPath);
(*env)->DeleteLocalRef(env, jdbPath);
sqlite3_free(pErrorMsg);
sqlite3_finalize(stmt);
sqlite3_close(db);
sqlite3_shutdown();
return result;
}

So,到这一步就没能再进行优化下去了,上面测试的表结构没有加索引,加了会影响性能,但可以提高查询效率。其中要注意语言细节性能问题,因为循环有几万次,所以很小的细节都会被放大。网上还有论文优化了sqlite的查询性能,单次性能提升了10%,目测是修改了sqlite中虚拟机产生代码的处理流程,恕水平有限,没有耗更多的精力和时间在这上面,到此就结束了,如果读者感兴趣,可以接着优化。google搜索关键字《SQLite的SQL语句高速缓存技术》即可找到这篇论文。

文章目录