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. Can we build index on the column3 and column5 in fact table to speed it up? Or any other approach? 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

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