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