[prev in list] [next in list] [prev in thread] [next in thread]
List: monetdb-users
Subject: Re: [MonetDB-users] Query speed up
From: "Allen Zhang" <AZhang () pmci-usa ! com>
Date: 2009-08-10 22:50:12
Message-ID: B403BA71F7C2ED4591CAABF8C4053675309CD6 () mail ! pmci-usa ! com
[Download RAW message or body]
Hi, Stefan,
Check the attached file, we are using MonetDB server v5.10.4 (64-bit), based on \
kernel v1.28.4 (64-bit oids)
the first run of the query took about 37 sec. but 2nd run just take 2 sec.
that's really huge difference if we can pre-build index.
Thanks
Allen
-----Original Message-----
From: Stefan Manegold [mailto:Stefan.Manegold@cwi.nl]
Sent: Mon 8/10/2009 10:53 AM
To: Communication channel for MonetDB users
Subject: Re: [MonetDB-users] Query speed up
On Mon, Aug 10, 2009 at 10:17:01AM -0700, Allen Zhang wrote:
> Thanks Stefan,
>
> We have a sun server with 32 cores and 64GB memory; the fact table t0 had
> around 200M rows, and grows every day, the query below returns about 25
> sec. the monetdb only use one core when we look at the CPU status.
Thanks for the info.
Could you please tell us which version of MonetDB you're using and which OS
you're running on?
`mserver5 --version` should give an informative summary ...
> Can we build index on the column3 and column5 in fact table to speed it up? Or any \
> other approach?
MonetDB does not use "classical" indices as usually created by a `create
index` statement. Instead, MonetDB builds (hash-based) (join-) indices
on-the-fly whenever they are considered beneficial. Moreover, we're working
on an adaptive self-managing partial ("(only) as much as required") indexing
technique called "Database Cracking" (see our respective research papers for
details). However, these techniques are not quite "production-ready", yet,
and hence for the time being by default disabled in the released
"production" versions of MonetDB.
To see where time goes with your query, you might want to run your query
with performance tracing enabled, i.e., simply prefix you query with keyword
"trace", and send us the output for a brief analysis (once and as far as our
time and human resources allow ...).
Stefan
>
> Best Regards
> Allen
>
> -----Original Message-----
> From: Stefan Manegold [mailto:Stefan.Manegold@cwi.nl]
> Sent: Monday, August 10, 2009 4:15 AM
> To: Communication channel for MonetDB users
> Subject: * Re: [MonetDB-users] Query speed up
>
> Hi Allen,
>
> which "performance issue" are your referring to?
>
> Stefan
>
> On Sun, Aug 09, 2009 at 05:13:54PM -0700, Allen Zhang wrote:
> > Hi, All,
> >
> > I have a client program generates the following query against monetdb, can I
> > create index on fact columns to speed thing up? What¹s the best approach to
> > resolve this kind performance issue?
> >
> > SELECT DISTINCT t1.column1
> > FROM (fact t0
> > LEFT JOIN dimension1 t1
> > ON t0.header_id = t1.header_id)
> > WHERE t0.column3 = 'xxxxxx' And t0.column5 IN ('item1¹, OEitem2¹, OEitem3¹,
> > OEitem4¹);
> >
> > Thanks in advance.
> > Allen
> >
> > ------------------------------------------------------------------------------
> > Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> > trial. Simplify your report design, integration and deployment - and focus on
> > what you do best, core application coding. Discover what's new with
> > Crystal Reports now. http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > MonetDB-users mailing list
> > MonetDB-users@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/monetdb-users
>
> --
> > Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
> > CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ |
> > 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 |
> > The Netherlands | Fax : +31 (20) 592-4312 |
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>
--
> Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
> CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ |
> 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 |
> The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
["indexcompare" (application/octet-stream)]
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic