[MonetDB-users] Profiling queries

Niels Nes Niels.Nes at cwi.nl
Thu Dec 6 08:48:19 CET 2007


On Thu, Dec 06, 2007 at 12:45:27AM -0700, m h wrote:
> 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?
could you send me the full trace output (unsorted).

Niels
> 
> -------------------------------------------------------------------------
> SF.Net email is sponsored by: The Future of Linux Business White Paper
> from Novell.  From the desktop to the data center, Linux is going
> mainstream.  Let it simplify your IT future.
> http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- 

Niels Nes, Centre for Mathematics and Computer Science (CWI)
Kruislaan 413, 1098 SJ Amsterdam, The Netherlands
room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl




More information about the users-list mailing list