Now I found the possible reason why [Rename Table] become slow.
While the one table renamed, the schema need to be updated. But SQLite use the
['SELECT name, rootpage, sql FROM 'main'.sqlite_master WHERE
tbl_name=’tablename' ORDER BY rowid”] to update the schema.
Isn’t it too ugly ? Why we should select the whole [sqlite_master] to update
ONLY ONE altered table ?
原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月9日(周日) 17:59
主题:[sqlite] The Performance Between [Drop Table] And [Rename Table] ?
I found that [Rename Table](using ‘ALTER TABLE RENAME TO') is much slower than
[Drop Table]. The cost of [Rename Table] may be twice, even if the table is
empty(which means it has no index, no trigger, no view and no column). As I
known, both [Drop Table] and [Rename Table] just modify the `sqlite_master`
when the table is empty. But in my testcase, [Rename Table] is much more
slower. Does anyone know the reason ? Here is my test code. Result: drop table
total cost 4705633 alter total cost 13172092 Code: #import sqlite3.h #import
sys/time.h #define EXIT_IF_FAILED(rc) if (rc!=SQLITE_OK) {printf("%d failed at
%d\n", rc, __LINE__); exit(0);} #define TABLE_COUNT 10000 static uint64_t now()
{ #define MICROSECOND_PER_SECOND 1000000 struct timeval cur; gettimeofday(cur,
NULL); uint64_t time = cur.tv_sec*MICROSECOND_PER_SECOND+cur.tv_usec; return
time; } void preCreateTable(sqlite3* db) { int rc = SQLITE_OK; rc =
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i =
0; i TABLE_COUNT; i++) { NSString* sql = [NSString stringWithFormat:@"CREATE
TABLE test%d (name TEXT)", i]; rc = sqlite3_exec(db, sql.UTF8String, NULL,
NULL, NULL); EXIT_IF_FAILED(rc); } rc = sqlite3_exec(db, "COMMIT", NULL, NULL,
NULL); EXIT_IF_FAILED(rc); } void config(sqlite3* db) { sqlite3_exec(db,
"PRAGMA journal_mode=WAL;", NULL, NULL, NULL); sqlite3_exec(db, "PRAGMA
synchronous=FULL;", NULL, NULL, NULL); } int main(int argc, const char *
argv[]) { const char* testDropTablePath =
"/Users/sanhuazhang/Desktop/testDropTablePath"; const char* testAlterTablePath
= "/Users/sanhuazhang/Desktop/testAlterTablePath"; //test 'drop table' {
sqlite3* db; int rc = sqlite3_open(testDropTablePath, db); EXIT_IF_FAILED(rc);
config(db); preCreateTable(db); uint64_t before = now(); rc = sqlite3_exec(db,
"BEGIN", NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 0; i TABLE_COUNT;
i++) { NSString* sql = [NSString stringWithFormat:@"DROP TABLE test%d", i]; rc
= sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL); EXIT_IF_FAILED(rc); } rc
= sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); EXIT_IF_FAILED(rc); uint64_t
after = now(); printf("drop table total cost %llu\n", after-before);
sqlite3_close(db); } //test 'alter table' { sqlite3* db; int rc =
sqlite3_open(testAlterTablePath, db); EXIT_IF_FAILED(rc); config(db);
preCreateTable(db); uint64_t before = now(); rc = sqlite3_exec(db, "BEGIN",
NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 0; i TABLE_COUNT; i++) {
NSString* sql = [NSString stringWithFormat:@"ALTER TABLE test%d RENAME TO
re%d", i, i]; rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
EXIT_IF_FAILED(rc); } rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
EXIT_IF_FAILED(rc); uint64_t after = now(); printf("alter total cost %llu\n",
after-before); sqlite3_close(db); } return 0; }
_______________________________________________ sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
