CODEWITHSUNDEEP
sqliteprofilingquery-optimization
yangsuli
yangsuli

Reputation: 1362

Is there a way to measure query time in sqlite at runtime?

I want to measure the execution time of each sql statement in sqlite.

I understand in sqlite shell you could just do .timer on, but I am wondering how to do it in a pogrammerable way so that I could know how much time a sql statement takes when applications are accessing the database at real time, not just by replaying that statement in a shell afterwards?

Could I just provide a plugin function for sqlite_profile?

Thanks a lot

Upvotes: 0

Views: 1835

Answers (2)

Somnath Sarode
Somnath Sarode

Reputation: 477

Well sqliteman provides some basic profiling features. It shows query execution time. Well though the query execution is on different processor ( i.e X86 as compare to ARM) still it will help you to write optimized raw query on sqlite . E.g. i just tested a select query with where clause without indexes and it took around 0.019 second and after creating index it's only taking 0.008.

Here is the link for sqlite man http://sourceforge.net/projects/sqliteman/

Upvotes: 0

Johannes Kuhn
Johannes Kuhn

Reputation: 15163

After reading the code of shell.c I found the following relevant information on top of the file:

/* Saved resource information for the beginning of an operation */
static struct rusage sBegin;

/*
** Begin timing an operation
*/
static void beginTimer(void){
  if( enableTimer ){
    getrusage(RUSAGE_SELF, &sBegin);
  }
}

/* Return the difference of two time_structs in seconds */
static double timeDiff(struct timeval *pStart, struct timeval *pEnd){
   return (pEnd->tv_usec - pStart->tv_usec)*0.000001 + 
         (double)(pEnd->tv_sec - pStart->tv_sec);
}

/*
** Print the timing results.
*/
static void endTimer(void){
  if( enableTimer ){
    struct rusage sEnd;
    getrusage(RUSAGE_SELF, &sEnd);
    printf("CPU Time: user %f sys %f\n",
       timeDiff(&sBegin.ru_utime, &sEnd.ru_utime),
       timeDiff(&sBegin.ru_stime, &sEnd.ru_stime));
  }
}

This means, SQLite does not provide some sort of that profiling itself, so you have to use your language features (e.g. endtime-startime) to do the job.

Upvotes: 1

Related Questions