- users-list - monetdb.org

Accessing MonetDB form Oracle DB
by baraa Mohamad 26 Oct '13

26 Oct '13

Dear MonetDB users, I have a question about accessing MonetDB via Oracle using ODBC. I have oracle on machine1 (ubuntu 64bit) and MonetDB on machine2(ubuntu 64bit)I installed Monetdb odbc driver using apt-get install libmonetdb-client-odbc I have done all the needed configurations for the listner, tnsnames, sqlnet, initservice.ora and I used UnixODBC. After creating the database link, I get this error when I try to connect MonetDB: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[unixODBC][MonetDB][ODBC Driver 11.11.11]Client unable to establish connection{08001}ORA-02063: preceding 2 lines from MONETDB I don't know if the use of (64bit) needs some special configurations?? or its du to something else. So I need your help to figure out what is the problem.... Best regards,Baraa

3 4

Primary and Foreign keys
by Hilario Fernandes 22 Oct '13

22 Oct '13

Hi, I've been trying to understand if there is any performance benefit in using primary and foreign key constraints when performing join queries. I've tested joining a table with 1 million rows with one with 60k. Tryed the same queries with and without constraints and couldn't see a significant change in execution time. The tables are not that big but they are a sample of what we'll be working with in average. Should i be seeing any difference? Hows do PK and FK constraints affect query performance in Monetdb? Will it only be noticeable with larger datasets? Thank you Hilario Fernandes

2 1

Are joins really this expensive?
by Bryan Senseman 20 Oct '13

20 Oct '13

I have created a traditional star-schema in MonetDB, using both Linux and Windows. We are using Mondrian OLAP on top so it generates the standard OLAP-y type of query using group-bys of dimensional values. What I’m finding is that doing joins in MonetDB is extremely expensive, for example: The following query against a fact table with 261 million rows returns in 9-11seconds consistently select date_hour_id/10000 as date_month_id, avg(seconds) from fact_utilization_hourly group by date_month_id; returning 41 rows, the query includes a “Trick” to get to a month_year value because the time_ids are smart keys… yyyyMMddhh. With the following query, using standard OLAP-y syntax, the query takes over 5 mins consistently. select year_month_value, avg(seconds) from fact_utilization_hourly f join dim_date_hour d on f.date_hour_id = d.date_hour_id group by year_month_value; returning the same 41 rows. I’m at a loss to figure out why this join causes a 3200% percent increase in response time. And this seems to have gotten worse in SP5 from anecdotal evidence. Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

3 3

Monetdb python error in connection
by Rose Beck 19 Oct '13

19 Oct '13

I am using python-monetdb 11.16.0.7. I created my database farm and database according to instructions given below (source: http://www.monetdb.org/Documentation/monetdbd) % monetdbd start /home/my-dbfarm % monetdb create my-first-db Then I tried to connect to the database using the below mentioned command in python(https://pypi.python.org/pypi/python-monetdb/). Upon doing so I am getting the below mentioned error: >import monetdb.sql >connection=monetdb.sql.connect(username="monetdb",password="monetdb",hostname="localhost",database="my-first-db"); File "/usr/local/lib/python2.7/dist-packages/monetdb/sql/__init__.py", line 28, in connect return Connection(*args, **kwargs) File "/usr/local/lib/python2.7/dist-packages/monetdb/sql/connections.py", line 58, in __init__ unix_socket=unix_socket) File "/usr/local/lib/python2.7/dist-packages/monetdb/mapi.py", line 93, in connect self.socket.connect((hostname, port)) File "/usr/lib/python2.7/socket.py", line 224, in meth return getattr(self._sock,name)(*args) socket.error: [Errno 111] Connection refused Can someone please help me with this? Also my my-first-db resides inside my db-farm? I am not getting as to what should I do?

1 0

Re: nulls last
by Klaudiusz 18 Oct '13

18 Oct '13

On 18.10.2013 12:05, Hilario Fernandes wrote: > O made the same test in SP4 and the results were the same, sum(null) kept > returning 0 and no results for the case. My tests in SP5 were done with a > clean database with no data imported, so according to your tests i should > at least have the correct value for the sum... > > These are the queries i've done and what i believe would be the expected > results: > > create table test (number int, name varchar(50)); > > insert into test values(1,'jonh'); > insert into test values(2,'mike'); > insert into test values(3,'mary'); > insert into test values(null,'austin'); > insert into test values(null,'jonh'); > insert into test values(null,'mary'); > > select name, sum(number) > from test > group by name; > > Result: > austin | 0 > jonh | 1 > mary | 3 > mike | 2 > > Expected: > austin | null > jonh | 1 > mary | 3 > mike | 2 > Hi Hilario, I've tested your query using default branch (11.16.0) and I can confirm that this bug also exist. So probably it depends on type of value by which you group. Probably if you try my example from http://bugs.monetdb.org/show_bug.cgi?id=3388 on fresh database, results should be as expected. So group by int works, but group by varchar doesn't :-(. I'm not sure if this is another bug than 3388 or it is the same. It's rather another one. Klaudiusz

1 0

nulls last
by Hilario Fernandes 18 Oct '13

18 Oct '13

Hello, I'm trying to build a query with order by that puts nulls last always. I tryed to use the "NULLS LAST" expression but apparently it's not supported by monetdb. So i came up with something like this: select name, sum(number) from test group by name order by case when sum(number) is null then 1 else 0 end, sum(number) ASC Thing is, this works when the ordering column (number) is not aggretated, as soon as i throw the sum in there it returns no results. The sum of null values seems to be returning 0, is it possible to change that behaviour trough configuration? Is there a better way to achieve this? thanks

2 1

Announcement: New Feb2013-SP5 Bugfix release of MonetDB suite
by Hannes Mühleisen 16 Oct '13

16 Oct '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP5 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Feb 2013-SP5 bugfix release Client Package * ODBC: Implemented retrieving variable-length data in parts with SQLGetData. See bug 3381. MonetDB Common * Removed the heap cache. Since the fix for bug 3323 which made that the cache was actually getting used, bug 3376 made clear that it didn't work very well. In addition, on Linux at least, the heap cache slows things down. Java Module * Rearranged order of returned columns of certain metadata functions to comply with the JDBC documentation. See bug 3356. Bug Fixes * 3323: Heapcache bugs/performance issues * 3331: SAMPLE will return same result every time. * 3356: DatabaseMetaData.getColumns() doesn't work correctly when using index-based getters * 3367: Fully qualified order by column gives "relational query without result" * 3368: BAT sortedness info ignored on ORDER BY and TOPN * 3370: SQL environment settings are updated even when the set statement fails * 3373: Setting table to read-only (Bug 3362) fails on big-endian * 3375: LIKE join: BATfetchjoin does not hit always * 3376: COPY INTO fails with HEAPextend: failed to extend: MT_mremap() failed * 3377: Query interfering with next query in same transaction, after SP4 * 3380: Python DBAPI driver throws exception when fetching EXPLAIN results * 3381: Windows ODBC driver hangs or crashes on simple queries

1 0

Fwd: Query execution time
by baraa Mohamad 13 Oct '13

13 Oct '13

>> Hello dear MontDB users, >> >> I have a question please, I just need to get the query execution time >> without showing the results of the query (for performance comparaison) . >> Something which corresponds to the "traceonly" in Oracle. that will be >> greate if I can see the query plan but for the moment the most important >> for me is to get the query execution time. >> >> for example: I want to know the query exection time of this query >> (select * from source where a>10) knowing that I have more than one >> hundred million records which correspond to this predicate. >> >> Thank in advace for your appreciated help. >> >> Baraa

4 16

Re: How to transfer monetdb database to another machine
by Rose Beck 13 Oct '13

13 Oct '13

Thanks a lot for helping. I am unable to figure as to where does * <dbfarm>/<dbname>* directory reside within ubuntu 12.04. Can you please help me with this. On Sun, Oct 13, 2013 at 6:06 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: Query execution time (baraa Mohamad) > 2. Re: Query execution time (Martin Kersten) > 3. Re: How to transfer monetdb database to another machine > (Martin Kersten) > 4. Re: Query execution time (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 13 Oct 2013 12:31:35 +0200 > From: baraa Mohamad <bem85(a)hotmail.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Cc: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: Query execution time > Message-ID: <BAY406-EAS1557A9804AABD08B94A7905A8190(a)phx.gbl> > Content-Type: text/plain; charset="utf-8" > > That's great! Thank you very much for your appreciated help. > > I just could't really understand the output of this command. > > This is my output: > Real: 1m23 > User: 0m24s > Sys: 0m2.3s > > I'm a little bit confused because when I execute the same query directly > without the time option i get 733,28ms. > > So i couldn't know what is the real execution time. > > > > > Le 13 oct. 2013 ? 11:29, "Martin Kersten" <Martin.Kersten(a)cwi.nl> a > ?crit : > > > > How about (time mclient -d db <inputfile >/dev/null) > > > >> On 10/13/13 11:24 AM, baraa Mohamad wrote: > >> Thank you for your answer. > >> But in general I would like to know how to get the query exexution time > >> without printing the results. It's for performance comparaison. I want > >> to evaluate MonetDB for my research and i'm working with large tables. > >> > >> Please any idea?? > >> > >> Greetings, > >> Baraa > >> > >> > >> > >> > >> Le 13 oct. 2013 ? 11:02, "Martin Kersten" <Martin.Kersten(a)cwi.nl > >> <mailto:Martin.Kersten@cwi.nl>> a ?crit : > >> > >>> Hi > >>> > >>> > >>>> On 10/13/13 10:55 AM, baraa Mohamad wrote: > >>>> > >>>>>> Hello dear MontDB users, > >>>>>> > >>>>>> I have a question please, I just need to get the query execution > time > >>>>>> without showing the results of the query (for performance > >>>>>> comparaison) . > >>>>>> Something which corresponds to the "traceonly" in Oracle. that will > be > >>>>>> greate if I can see the query plan but for the moment the most > >>>>>> important > >>>>>> for me is to get the query execution time. > >>>>>> > >>>>>> for example: I want to know the query exection time of this query > >>>>>> (select * from source where a>10) knowing that I have more than one > >>> This is not the ind of query you fire when you expect a large outcome. > >>> Better use SELECT COUNT(*).... > >>> > >>>>>> hundred million records which correspond to this predicate. > >>>>>> > >>>>>> Thank in advace for your appreciated help. > >>>>>> > >>>>>> Baraa > >>>> _______________________________________________ > >>>> users-list mailing list > >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>> https://www.monetdb.org/mailman/listinfo/users-list > >>> _______________________________________________ > >>> users-list mailing list > >>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>> https://www.monetdb.org/mailman/listinfo/users-list > >> > >> > >> _______________________________________________ > >> users-list mailing list > >> users-list(a)monetdb.org > >> https://www.monetdb.org/mailman/listinfo/users-list > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list >

2 1

How to transfer monetdb database to another machine
by Rose Beck 13 Oct '13

13 Oct '13

I created monetdb database using the python library https://pypi.python.org/pypi/python-monetdb/11.16.0.7 on my ubuntu 12.04 lts server. Now I want to transfer this database to another ubuntu 12.04 lts server. However, I am unable to understand as to where(directory name) does monetdb create its database (within ubuntu 12.04) and what is the procedure which I must follow to transfer this database to another machine (so that i may query the database on another machine)?

2 1