Re: [MonetDB-users] Profiling queries

On Thu, Dec 06, 2007 at 12:45:27AM -0700, m h wrote:

On Dec 5, 2007 3:33 PM, m h 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=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=bat[:oid,:str]{32}) ] [ 1883 usec # _121 := algebra.joinPath(_112=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=bat[:oid,:str]{32}) ] [ 1885 usec # _122 := algebra.joinPath(_112=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=bat[:oid,:str]{32}) ] [ 1900 usec # _120 := algebra.joinPath(_112=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=bat[:oid,:str]{32}) ] [ 1906 usec # _119 := algebra.joinPath(_112=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=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=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=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=bat[:oid,:str]{32}) ] [ 3256 usec # sql.exportResult(_197=0, _242="") ] [ 4921 usec # _39 := algebra.join(_38=bat[:oid,:oid]{210}, _31=<~tmp_21452>bat[:oid,:oid]{176871}) ] [ 73885 usec # _23 := algebra.semijoin(_16=<~tmp_13761>bat[:oid,:oid]{4000000}, _20=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@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@cwi.nl