/ Check-in [2914e419]
Login

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

Overview
Comment:Merge the VDBE_PROFILE fixes from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | insert-optimization
Files: files | file ages | folders
SHA1:2914e4191121004a3f564b1fbf1c235dcc595503
User & Date: drh 2014-02-17 15:36:39
Context
2014-02-17
15:40
Add a test case for OP_SoftNull. check-in: f29d194e user: drh tags: insert-optimization
15:36
Merge the VDBE_PROFILE fixes from trunk. check-in: 2914e419 user: drh tags: insert-optimization
14:59
Avoid unnecessary calls to applyAffinity() during INSERT and UPDATE operations, especially for table that have indices and tables for which all columns have affinity "NONE". check-in: 35b4d6e9 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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbeaux.c.

1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
....
2548
2549
2550
2551
2552
2553
2554










2555
2556
2557
2558
2559
2560
2561
  char zCom[100];
  static const char *zFormat1 = "%4d %-13s %4d %4d %4d %-13s %.2X %s\n";
  if( pOut==0 ) pOut = stdout;
  zP4 = displayP4(pOp, zPtr, sizeof(zPtr));
#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  displayComment(pOp, zP4, zCom, sizeof(zCom));
#else
  zCom[0] = 0
#endif
  /* NB:  The sqlite3OpcodeName() function is implemented by code created
  ** by the mkopcodeh.awk and mkopcodec.awk scripts which extract the
  ** information from the vdbe.c source text */
  fprintf(pOut, zFormat1, pc, 
      sqlite3OpcodeName(pOp->opcode), pOp->p1, pOp->p2, pOp->p3, zP4, pOp->p5,
      zCom
................................................................................
    if( out ){
      int i;
      fprintf(out, "---- ");
      for(i=0; i<p->nOp; i++){
        fprintf(out, "%02x", p->aOp[i].opcode);
      }
      fprintf(out, "\n");










      for(i=0; i<p->nOp; i++){
        fprintf(out, "%6d %10lld %8lld ",
           p->aOp[i].cnt,
           p->aOp[i].cycles,
           p->aOp[i].cnt>0 ? p->aOp[i].cycles/p->aOp[i].cnt : 0
        );
        sqlite3VdbePrintOp(out, i, &p->aOp[i]);







|







 







>
>
>
>
>
>
>
>
>
>







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

Added tool/vdbe_profile.tcl.





































































































































































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

foreach stmt $allstmt {
  puts "********************************************************************"
  puts [string trim $sql($stmt)]
  puts "Execution count: $cnt($stmt)"
  for {set i 0} {[info exists stat($stmt,$i)]} {incr i} {
    foreach {cx tx detail} $stat($stmt,$i) break
    if {$cx==0} {
      set ax 0
    } else {
      set ax [expr {$tx/$cx}]
    }
    puts [format {%8d %12d %12d %4d %s} $cx $tx $ax $i $detail]
  }
}
puts "********************************************************************"
puts "OPCODES:"
foreach op [lsort [array names opcnt]] {
  set cx $opcnt($op)
  set tx $opcycle($op)
  if {$cx==0} {
    set ax 0
  } else {
    set ax [expr {$tx/$cx}]
  }
  puts [format {%8d %12d %12d %s} $cx $tx $ax $op]
}