[MonetDB-users] Profiling queries

Niels Nes Niels.Nes at cwi.nl
Wed Dec 5 08:15:30 CET 2007


On Tue, Dec 04, 2007 at 11:58:55PM -0700, m h wrote:
> On Dec 4, 2007 12:56 PM, m h <sesquile at gmail.com> wrote:
> > Since the query is confidential I can't really put it here, but I can
> > describe it.  I'm pulling stuff out of my fact table and two other
> > dimension tables.  The select clause sums a few columns, and on one
> > sums the product of one column and the sum of a few others.  The
> > coalesce function is used to convert NULLs to 0.  The where statement
> > joins the tables using the = operator and then has two "IN" clauses.
> > Then there is a group by (since I'm aggregating in the select) and an
> > order by.
> >
> > Any hints on how to increase performance?  Do I need to tune my query
> > or tweak other things?  (Yes I know the laptop machine is not apples
> > to apples to the vmware machine)
> >
> > Am open to suggestions.
> >
> 
> In the meantime I've done the following in an attempt to make the
> query faster all to no avail (ie the speed stays the same):
> 
>   * Remove the calculation doing summing and coerce'ing
	(this was in the selection part were I guess you do not have much
	data left)
>   * Change IN statement to =
>   * Remove primary KEY for date table and join date table with between
> clause (actually surprised this didn't make it slower)
	Only if the otherside has a foreign key it would slow down.

	Single column primary key's don't help, as these are handled
	by in memory hash tables which are created when needed (ie
	without influence of the user).
>   * Added indexes for non-PK columns I'm joining on.  No difference!
	So your query involves many join expressions ?

> 
> Does this have something to do with the tables in the "tmp" schema?
> Does it make queries slower?
We never use the 'tmp' schema for columns other then really temporaries 
ourselfs, so I have know real data on this, but known how the queries
are translated I would expect any impact.

Niels
> 
> cheers,
> 
> -matt
> 
> -------------------------------------------------------------------------
> 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