/ Check-in [7adb3da2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the VDBE_PROFILE logic. Add a script to process the output file.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7adb3da235c8c162c84f05ef4ccf1cc463805d5f
User & Date: drh 2014-02-17 01:13:28
Context
2014-02-17
23:52
Merge in performance enhancements for INSERT operations, especially INSERTs on tables that have no affinity columns or that have many indices or INSERTs with content coming from a SELECT. Add the SQLITE_TESTCTRL_VDBE_COVERAGE test control and the SQLITE_VDBE_COVERAGE compile-time option used for measure coverage of branches in VDBE programs. check-in: a7268769 user: drh tags: trunk
15:36
Merge the VDBE_PROFILE fixes from trunk. check-in: 2914e419 user: drh tags: insert-optimization
01:13
Fix the VDBE_PROFILE logic. Add a script to process the output file. check-in: 7adb3da2 user: drh tags: trunk
2014-02-14
23:49
Seek past NULLs in a top-constrained search. Avoid checking for NULLs in the body of the search. check-in: e07a32f3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/vdbeaux.c.

  1170   1170     char zCom[100];
  1171   1171     static const char *zFormat1 = "%4d %-13s %4d %4d %4d %-13s %.2X %s\n";
  1172   1172     if( pOut==0 ) pOut = stdout;
  1173   1173     zP4 = displayP4(pOp, zPtr, sizeof(zPtr));
  1174   1174   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  1175   1175     displayComment(pOp, zP4, zCom, sizeof(zCom));
  1176   1176   #else
  1177         -  zCom[0] = 0
         1177  +  zCom[0] = 0;
  1178   1178   #endif
  1179   1179     /* NB:  The sqlite3OpcodeName() function is implemented by code created
  1180   1180     ** by the mkopcodeh.awk and mkopcodec.awk scripts which extract the
  1181   1181     ** information from the vdbe.c source text */
  1182   1182     fprintf(pOut, zFormat1, pc, 
  1183   1183         sqlite3OpcodeName(pOp->opcode), pOp->p1, pOp->p2, pOp->p3, zP4, pOp->p5,
  1184   1184         zCom
................................................................................
  2548   2548       if( out ){
  2549   2549         int i;
  2550   2550         fprintf(out, "---- ");
  2551   2551         for(i=0; i<p->nOp; i++){
  2552   2552           fprintf(out, "%02x", p->aOp[i].opcode);
  2553   2553         }
  2554   2554         fprintf(out, "\n");
         2555  +      if( p->zSql ){
         2556  +        char c, pc = 0;
         2557  +        fprintf(out, "-- ");
         2558  +        for(i=0; (c = p->zSql[i])!=0; i++){
         2559  +          if( pc=='\n' ) fprintf(out, "-- ");
         2560  +          putc(c, out);
         2561  +          pc = c;
         2562  +        }
         2563  +        if( pc!='\n' ) fprintf(out, "\n");
         2564  +      }
  2555   2565         for(i=0; i<p->nOp; i++){
  2556   2566           fprintf(out, "%6d %10lld %8lld ",
  2557   2567              p->aOp[i].cnt,
  2558   2568              p->aOp[i].cycles,
  2559   2569              p->aOp[i].cnt>0 ? p->aOp[i].cycles/p->aOp[i].cnt : 0
  2560   2570           );
  2561   2571           sqlite3VdbePrintOp(out, i, &p->aOp[i]);

Added tool/vdbe_profile.tcl.

            1  +#!/bin/tclsh
            2  +#
            3  +# Run this script in the same directory as the "vdbe_profile.out" file.
            4  +# This script summarizes the results contained in that file.
            5  +#
            6  +if {![file readable vdbe_profile.out]} {
            7  +  error "run this script in the same directory as the vdbe_profile.out file"
            8  +}
            9  +set in [open vdbe_profile.out r]
           10  +set stmt {}
           11  +set allstmt {}
           12  +while {![eof $in]} {
           13  +  set line [gets $in]
           14  +  if {$line==""} continue
           15  +  if {[regexp {^---- } $line]} {
           16  +    set stmt [lindex $line 1]
           17  +    if {[info exists cnt($stmt)]} {
           18  +      incr cnt($stmt)
           19  +      set firsttime 0
           20  +    } else {
           21  +      set cnt($stmt) 1
           22  +      set sql($stmt) {}
           23  +      set firsttime 1
           24  +      lappend allstmt $stmt
           25  +    }
           26  +    continue;
           27  +  }
           28  +  if {[regexp {^-- } $line]} {
           29  +    if {$firsttime} {
           30  +      append sql($stmt) [string range $line 3 end]\n
           31  +    }
           32  +    continue
           33  +  }
           34  +  if {![regexp {^ *\d+ *\d+ *\d+ *\d+ ([A-Z].*)} $line all detail]} continue
           35  +  set c [lindex $line 0]
           36  +  set t [lindex $line 1]
           37  +  set addr [lindex $line 3]
           38  +  set op [lindex $line 4]
           39  +  if {[info exists opcnt($op)]} {
           40  +    incr opcnt($op) $c
           41  +    incr opcycle($op) $t
           42  +  } else {
           43  +    set opcnt($op) $c
           44  +    set opcycle($op) $t
           45  +  }
           46  +  if {[info exists stat($stmt,$addr)]} {
           47  +    foreach {cx tx detail} $stat($stmt,$addr) break
           48  +    incr cx $c
           49  +    incr tx $t
           50  +    set stat($stmt,$addr) [list $cx $tx $detail]
           51  +  } else {
           52  +    set stat($stmt,$addr) [list $c $t $detail]
           53  +  }
           54  +}
           55  +close $in
           56  +
           57  +foreach stmt $allstmt {
           58  +  puts "********************************************************************"
           59  +  puts [string trim $sql($stmt)]
           60  +  puts "Execution count: $cnt($stmt)"
           61  +  for {set i 0} {[info exists stat($stmt,$i)]} {incr i} {
           62  +    foreach {cx tx detail} $stat($stmt,$i) break
           63  +    if {$cx==0} {
           64  +      set ax 0
           65  +    } else {
           66  +      set ax [expr {$tx/$cx}]
           67  +    }
           68  +    puts [format {%8d %12d %12d %4d %s} $cx $tx $ax $i $detail]
           69  +  }
           70  +}
           71  +puts "********************************************************************"
           72  +puts "OPCODES:"
           73  +foreach op [lsort [array names opcnt]] {
           74  +  set cx $opcnt($op)
           75  +  set tx $opcycle($op)
           76  +  if {$cx==0} {
           77  +    set ax 0
           78  +  } else {
           79  +    set ax [expr {$tx/$cx}]
           80  +  }
           81  +  puts [format {%8d %12d %12d %s} $cx $tx $ax $op]
           82  +}