[sqlite] The Performance Between [Drop Table] And [Rename Table] ?

[sanhua.zh]

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