[MonetDB-users] Profiling queries

m h sesquile at gmail.com
Thu Dec 6 08:45:27 CET 2007


On Dec 5, 2007 3:33 PM, m h <sesquile at gmail.com> wrote:
> I ran the query from the command line and piped the results.  At the
> bottom are them two numbers:
>
> Timer     197.414 msec
> Timer       1.690 msec
>
> I'm assumming one represents the runtime, not sure about the other.
>
> I also ran the query with trace. At the bottom it says:
>
> Timer     245.435 msec
> Timer       1.380 msec
>
> In the trace file here are the 13 biggest lines:
> [   243 usec  #    _16 := nil; ]
> [   251 usec  #    _31 := nil; ]
> [  1538 usec  #    _30 :=
> algebra.join(_27=<~tmp_21501>bat[:oid,:oid]{176871},
> _28=<tmp_16674>bat[:oid,:oid]{4000000}) ]
> [  1877 usec  #    _104 :=
> algebra.joinPath(_60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _102=<tmp_13260>bat[:oid,:str]{32}) ]
> [  1883 usec  #    _121 :=
> algebra.joinPath(_112=<tmp_21470>bat[:oid,:oid]{89},
> _60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _97=<tmp_13261>bat[:oid,:str]{32}) ]
> [  1885 usec  #    _122 :=
> algebra.joinPath(_112=<tmp_21470>bat[:oid,:oid]{89},
> _60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _102=<tmp_13260>bat[:oid,:str]{32}) ]
> [  1900 usec  #    _120 :=
> algebra.joinPath(_112=<tmp_21470>bat[:oid,:oid]{89},
> _60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _17=<tmp_11202>bat[:oid,:str]{32}) ]
> [  1906 usec  #    _119 :=
> algebra.joinPath(_112=<tmp_21470>bat[:oid,:oid]{89},
> _60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _89=<tmp_10351>bat[:oid,:str]{32}) ]
> [  1910 usec  #    _94 :=
> algebra.joinPath(_60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _17=<tmp_11202>bat[:oid,:str]{32}) ]
> [  1969 usec  #    _91 :=
> algebra.joinPath(_60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _89=<tmp_10351>bat[:oid,:str]{32}) ]
> [  1979 usec  #    _99 :=
> algebra.joinPath(_60=<~tmp_21352>bat[:oid,:oid]{89},
> _43=<~tmp_21452>bat[:oid,:oid]{1304},
> _88=<~tmp_21522>bat[:oid,:oid]{176871},
> _97=<tmp_13261>bat[:oid,:str]{32}) ]
> [  3256 usec  #    sql.exportResult(_197=0, _242="") ]
> [  4921 usec  #    _39 :=
> algebra.join(_38=<tmp_21352>bat[:oid,:oid]{210},
> _31=<~tmp_21452>bat[:oid,:oid]{176871}) ]
> [ 73885 usec  #    _23 :=
> algebra.semijoin(_16=<~tmp_13761>bat[:oid,:oid]{4000000},
> _20=<tmp_21501>bat[:oid,:oid]{1}) ]
>
> (the above is piped through sort, not the last one is an order of
> magnitude bigger than anything else).  How do I go about starting to
> understand/debug this?
>

I've gotten as far as understanding the the 73885 and 4921 statements
have to do with two statements in the where clause that showed up as
IN.  I'm changed the query a bit and they are not doing = comparisons
against varchars.

These two lines:

  loc_dim.store IN ('FOO STORE') AND  --varchar
  item_dim.groupnumnm IN ('BAR ITEMS') --varchar

Any suggestions for optimizing there?




More information about the users-list mailing list