- users-list - monetdb.org

[MonetDB-users] Querying date type column in MonetDB
by Freddy Priyatna 25 Oct '10

25 Oct '10

Dear All, I am new to MonetDB, so most likely my question will look obvious. I would like to ask, how can columns with date type is queried in monetdb. Based on my experience, it seems that date type columns need special treatment. It seems that the only way to query them is with the syntax "column_name" while other columns seems to work with the format table_name.column_name without the need of double quote. When I tried to query date column with the syntax table_name.column_name, it gave me an error "no such column" So my question is, is it true that the only way to query date columns is using "column_name" format? Another question will be, what happen if I need to query same name columns coming from different table? Thanks in advance, Freddy

1 0

[MonetDB-users] calc.date in SQL to MAL.
by Guillaume Yziquel 22 Oct '10

22 Oct '10

Hi. I have resumed work on the OCaml binding for MonetDB. I have been playing a bit with it and with MonetDB, and noticed something rather weird when trying to explain a SQL statement to MAL. Compilation, and launching a small test in an OCaml toplevel: yziquel@seldon:~/data$ make ocamlbuild -use-ocamlfind import.cma Finished, 3 targets (0 cached) in 00:00:00. yziquel@seldon:~/data$ make test cp ocamlinit _build/ cd _build; ledit ocaml -init ocamlinit Objective Caml version 3.12.0 Findlib has been successfully loaded. Additional directives: #require "package";; to load a package #list;; to list the available packages #camlp4o;; to load camlp4 (standard syntax) #camlp4r;; to load camlp4 (revised syntax) #predicates "p,q,...";; to set these predicates Topfind.reset();; to force that packages will be reloaded #thread;; to enable threads /usr/lib/ocaml/csv: added to search path /usr/lib/ocaml/csv/csv.cma: loaded /usr/lib/ocaml/unix.cma: loaded /usr/lib/ocaml/monetdb5: added to search path /usr/lib/ocaml/monetdb5/monetDB5.cma: loaded /usr/lib/ocaml/dynlink.cma: loaded /usr/lib/ocaml/camlp4: added to search path /usr/lib/ocaml/pcre: added to search path /usr/lib/ocaml/pcre/pcre.cma: loaded /usr/local/lib/ocaml/3.12.0/tophide: added to search path /usr/local/lib/ocaml/3.12.0/tophide/tophide.cmo: loaded /usr/local/lib/ocaml/3.12.0/mikmatch_pcre: added to search path /usr/local/lib/ocaml/3.12.0/mikmatch_pcre/run_mikmatch_pcre.cma: loaded So much for the prologue, and dynamic loading. So the test starts here. Everytime you see "Asking", it is the statement that is fed to the connection handler in order to retrieve the query handler. (With mapilib). Asking: select name from tables; Asking: create table indu (Date date, Open decimal(15,2), High decimal(15,2), Low decimal(15,2), Close decimal(15,2), Volume decimal(15,2), OI decimal(15,2)); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-02-01', 108.80, 108.80, 108.80, 108.80, 1156, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-05-01', 108.90, 108.90, 108.90, 108.90, 1305, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-06-01', 107.40, 107.40, 107.40, 107.40, 1251, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-07-01', 107.60, 107.60, 107.60, 107.60, 771, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-08-01', 107.20, 107.20, 107.20, 107.20, 843, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-09-01', 106.60, 106.60, 106.60, 106.60, 736, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); Asking: insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0); And on this last statement, the server doesn't seem happy at all: Exception: MonetDB5.Mapi.Error (Some (MonetDB5.Mapi.MSERVER, None)). So I've been trying to 'explain' the SQL to MAL. The first two work fine and output a very similar MAL statement. The third SQL statements, however, is getting translated to something somewhat different: You see a call to 'calc.date' on the 4th MAL line: # ask "explain insert into indu (Date, Open, High, Low, Close, Volume, # OI) values ('1920-07-01', 107.60, 107.60, 107.60, 107.60, 771, 0);";; Asking: explain insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-07-01', 107.60, 107.60, 107.60, 107.60, 771, 0); - : string option list list = [[Some "function user.s0_1{autoCommit=true}():void;"]; [Some " _2 := sql.mvc();"]; [Some "barrier _32 := language.dataflow();"]; [Some " _3 := sql.append(_2,\"sys\",\"indu\",\"date\",1920-07-01);"]; [Some " _7 := sql.append(_3,\"sys\",\"indu\",\"open\",10760:lng);"]; [Some " _9 := sql.append(_7,\"sys\",\"indu\",\"high\",10760:lng);"]; [Some " _11 := sql.append(_9,\"sys\",\"indu\",\"low\",10760:lng);"]; [Some " _13 := sql.append(_11,\"sys\",\"indu\",\"close\",10760:lng);"]; [Some " _15 := sql.append(_13,\"sys\",\"indu\",\"volume\",77100:lng);"]; [Some " sql.append(_15,\"sys\",\"indu\",\"oi\",0:lng);"]; [Some "exit _32;"]; [Some " sql.affectedRows(1:wrd,\"\");"]; [Some "end s0_1;"]] # ask "explain insert into indu (Date, Open, High, Low, Close, Volume, # OI) values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0);";; Asking: explain insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-12-01', 104.20, 104.20, 104.20, 104.20, 1049, 0); - : string option list list = [[Some "function user.s1_1{autoCommit=true}():void;"]; [Some " _2 := sql.mvc();"]; [Some "barrier _32 := language.dataflow();"]; [Some " _3 := sql.append(_2,\"sys\",\"indu\",\"date\",1920-12-01);"]; [Some " _7 := sql.append(_3,\"sys\",\"indu\",\"open\",10420:lng);"]; [Some " _9 := sql.append(_7,\"sys\",\"indu\",\"high\",10420:lng);"]; [Some " _11 := sql.append(_9,\"sys\",\"indu\",\"low\",10420:lng);"]; [Some " _13 := sql.append(_11,\"sys\",\"indu\",\"close\",10420:lng);"]; [Some " _15 := sql.append(_13,\"sys\",\"indu\",\"volume\",104900:lng);"]; [Some " sql.append(_15,\"sys\",\"indu\",\"oi\",0:lng);"]; [Some "exit _32;"]; [Some " sql.affectedRows(1:wrd,\"\");"]; [Some "end s1_1;"]] # ask "explain insert into indu (Date, Open, High, Low, Close, Volume, # OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0);";; Asking: explain insert into indu (Date, Open, High, Low, Close, Volume, OI) values ('1920-13-01', 104.50, 104.50, 104.50, 104.50, 908, 0); - : string option list list = [[Some "function user.s2_1{autoCommit=true}():void;"]; [Some " _2 := sql.mvc();"]; [Some "barrier _33 := language.dataflow();"]; [Some " _3 := calc.date(\"1920-13-01\");"]; [Some " _4 := sql.append(_2,\"sys\",\"indu\",\"date\",_3);"]; [Some " _8 := sql.append(_4,\"sys\",\"indu\",\"open\",10450:lng);"]; [Some " _10 := sql.append(_8,\"sys\",\"indu\",\"high\",10450:lng);"]; [Some " _12 := sql.append(_10,\"sys\",\"indu\",\"low\",10450:lng);"]; [Some " _14 := sql.append(_12,\"sys\",\"indu\",\"close\",10450:lng);"]; [Some " _16 := sql.append(_14,\"sys\",\"indu\",\"volume\",90800:lng);"]; [Some " sql.append(_16,\"sys\",\"indu\",\"oi\",0:lng);"]; [Some "exit _33;"]; [Some " sql.affectedRows(1:wrd,\"\");"]; [Some "end s2_1;"]] # Why is that happening? The SQL statements seem so similar that I do not see why it gets compiled differently to MAL. Best regards, -- Guillaume Yziquel http://yziquel.homelinux.org

2 4

[MonetDB-users] installation corrupted
by Nafees Ur Rehman 20 Oct '10

20 Oct '10

Hi, I am using monetDB/SQL for windows on an xp machine. Two days ago, the server stopped responding. Tried to repair and change it...but didn't work. Also got it formally un-installed and then tried re-installation...but no luck. Any hints. The server does not mount at all. Regards Nafees Ur Rehman Konstanz, Germany

1 0

[MonetDB-users] MonetDB performance question
by Tudor-Ioan Salomie 18 Oct '10

18 Oct '10

Hi, I have recently tried doing some experiments with MonetDB, measuring its performance for the following dataset+workload: * 1 table (50 attributes), 1GB, comprising 3.000.000 tuples * the attributes are fixed length strings and integer types * the workload is query (SELECT) only, where each query has the form: SELECT <list_of_projections> FROM MyTable WHERE <list_of_predicates>; * the list of predicates is a conjunction of equality operators * more than 99% of the queries predicate on the same attribute (let's call it A1) that has a *very* good selectivity * the average number of predicated attributes per query is 8 * for this workload, averaging over 4000 queries, there is 1 result tuple per query (actual result tuples per query are in the range 0-10) I have installed MonetDB on a 64-bit Debian machines, using the .deb files provided on the internet: * libmonetdb1_1.38.5-20100824_amd64.deb * libmonetdb5-server5_5.20.5-20100824_amd64.deb * libmonetdb5-sql2_2.38.5-20100824_amd64.deb * libmonetdb-client1_1.38.5-20100824_amd64.deb * monetdb5-server_5.20.5-20100824_amd64.deb * monetdb5-sql_2.38.5-20100824_amd64.deb * monetdb-client_1.38.5-20100824_amd64.deb For running the queries I use the SQL front end. I have run the queries both through "mclient" (directly on the server) and through the JDBC driver (from another client machine). The MonetDB server is bound to run on only one core on the machine (though I have not seen any difference if running it on multiple cores). To my surprise, the throughput for MonetDB for this workload is very low. I get about 3 queries per second, which seems to indicate that MonetDB does full table scanning only. Am I correct in this assumption? Is there anything that can be done to speed it up? Is this not a workload suitable for MonetDB? Looking forward to your input! Regards, Tudor.

2 1

[MonetDB-users] FW: Comment via CWI webform MonetDB
by Peter Boncz 15 Oct '10

15 Oct '10

Narayan Sujay, Thanks for your interest in MonetDB. Rather than directing technical questions to the CWI organization webmaster, you are supposed to post on one of the relevant mailing lists such as this one (monetdb-user(a)lists.sourceforge.net) Peter -----Original Message----- From: Minnie Middelberg [mailto:Minnie.Middelberg@cwi.nl] Sent: Friday, October 15, 2010 9:50 AM To: Peter Boncz Subject: Fwd: Comment via CWI webform MonetDB Hallo Peter, Onderstaand is binnengekomen via het CWI Comment formulier en het betreft MonetDB. Wetende dat jij en Marting Kersten achter MonetDB zitten forward ik het maar naar jou. Mocht er iemand zijn die dit soort vragen belast is dan hoor ik het graag van je. Mvg Minnie ----- Forwarded Message ----- From: "Narayan Sujay" <316sujay(a)gmail.com> To: voorlichting(a)cwi.nl, webmaster(a)cwi.nl Sent: Thursday, 14 October, 2010 6:15:15 PM Subject: Comment via CWI webform Submitted on 14.10.2010 - 18:15 Submitted by anonymous user: [198.134.51.12] Submitted values are: Name sender: Narayan Sujay Email address: 316sujay(a)gmail.com Telephone number: 4193780532 Your comment: Hello, I have downloaded MonetDB and the native client and server work well. I can write simple queries in the client cmd window. I am trying to connect MonetDB with one of the SQL frontends like DB visualizer etc and I am not able to do this. Please help me out with instructions. I followed the instructions on the webpage but still getting errors connecting through db visualizer. I think it is the problem with the JDBC drivers. Please let me know excatly where to find the driver on the local computer after I have installed Monet DB. My Main goal here is to load large Data sets in MonetDB. Please tell me a way to do this for Monet DB. The error I get in DB Visualizer is: Long Message: Cannot connect to ORB Details: Type: javax.naming.CommunicationException Stack Trace: javax.naming.CommunicationException: Cannot connect to ORB [Root exception is org.omg.CORBA.COMM_FAILURE: vmcid: SUN minor code: 201 completed: No] at com.sun.jndi.cosnaming.CNCtx.setOrbAndRootContext(Unknown Source) at com.sun.jndi.cosnaming.CNCtx.initOrbAndRootContext(Unknown Source) at com.sun.jndi.cosnaming.CNCtx.<init>(Unknown Source) at com.sun.jndi.cosnaming.CNCtxFactory.getInitialContext(Unknown Source) at javax.naming.spi.NamingManager.getInitialContext(Unknown Source) at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source) at javax.naming.InitialContext.init(Unknown Source) at javax.naming.InitialContext.<init>(Unknown Source) at com.onseven.dbvis.db.A.G.Ą(Z:3155) at com.onseven.dbvis.db.A.G.Ĭ(Z:2675) at com.onseven.dbvis.db.A.E.Ĭ(Z:98) at com.onseven.dbvis.db.A.D.ā(Z:2056) at se.pureit.swing.A.O$2.run(Z:1192) at java.lang.Thread.run(Unknown Source) Caused by: org.omg.CORBA.COMM_FAILURE: vmcid: SUN minor code: 201 completed: No at com.sun.corba.se.impl.logging.ORBUtilSystemException.connectFailure(Unknown Source) at com.sun.corba.se.impl.logging.ORBUtilSystemException.connectFailure(Unknown Source) at com.sun.corba.se.impl.transport.SocketOrChannelConnectionImpl.<init>(Unknown Source) at com.sun.corba.se.impl.transport.SocketOrChannelConnectionImpl.<init>(Unknown Source) at com.sun.corba.se.impl.transport.SocketOrChannelContactInfoImpl.createConnection(Unknown Source) at com.sun.corba.se.impl.protocol.CorbaClientRequestDispatcherImpl.beginRequest(Unknown Source) at com.sun.corba.se.impl.protocol.CorbaClientDelegateImpl.request(Unknown Source) at com.sun.corba.se.impl.resolver.BootstrapResolverImpl.invoke(Unknown Source) at com.sun.corba.se.impl.resolver.BootstrapResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.resolver.CompositeResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.resolver.CompositeResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.resolver.CompositeResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.orb.ORBImpl.resolve_initial_references(Unknown Source) ... 14 more Caused by: java.net.ConnectException: Connection refused: connect at sun.nio.ch.Net.connect(Native Method) at sun.nio.ch.SocketChannelImpl.connect(Unknown Source) at java.nio.channels.SocketChannel.open(Unknown Source) at com.sun.corba.se.impl.transport.DefaultSocketFactoryImpl.createSocket(Unknown Source) ... 25 more The results of this submission may be viewed at: http://www.cwi.nl/en/node/757/submission/3152

1 0

[MonetDB-users] upgrade notes: Feb2010 --> Jun2010
by Mark Bucciarelli 07 Oct '10

07 Oct '10

Hi, I'd like to upgrade to the June release. I'm running Feb 2010. Can I just build the June release and expect it will just work with my database? Or do I need to upgrade the data somehow? I poked around dev.monetdb.org/hg, looking for release notes, changelist, etc. but didn't find anything. Thanks, m

2 3

[MonetDB-users] bug #2634 in Jun2010? [was: upgrade notes: Feb2010 --> Jun2010]
by Mark Bucciarelli 07 Oct '10

07 Oct '10

On Thu, Oct 7, 2010 at 9:15 AM, Sjoerd Mullender <sjoerd(a)acm.org> wrote: > On 2010-10-07 14:41, Mark Bucciarelli wrote: > >> I poked around dev.monetdb.org/hg, looking for release >> notes, changelist, etc. but didn't find anything. > > http://monetdb.cwi.nl/Development/Releases/Jun2010/ > Thanks. Was the commit in bug #2634 in the release? I don't see it in the SQL notes. He closed as NEEDS MORE INFO (I think---I can't tell from ticket history) because I did not include a test case. But he did commit some change that fixed the issue (I was able to upload a test case this week, and verified his fix solved my case). Thanks, m http://bugs.monetdb.org/show_bug.cgi?id=2634

2 1

[MonetDB-users] Performance improvements over equal queries limits to operators?
by Stefan de Konink 06 Oct '10

06 Oct '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hello, I was quite surprised about the performance of some queries, that I expected to be heavy weighted, but actually performed pretty well. To show some things: sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (259.000ms) sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (259.000ms) sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (258.000ms) sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (257.000ms) sql>select * from kvk where postcode = '2265CA' and adres like '%7%'; 3 tuples (11.000ms) sql>select * from kvk where postcode = '2265CA' and adres like '%7%'; 3 tuples (6.000ms) sql>select * from kvk where postcode = '2265CA' and adres like '%7%'; 3 tuples (6.000ms) sql>select * from kvk where kvk = 273121520000; 1 tuple (473.000ms) sql>select * from kvk where kvk = 273121520000; 1 tuple (5.000ms) I wonder: what makes the 11ms to 6ms possible in the like vs = scenario? While a repeat of the like operator itself doesn't give a true improvement? As you can observe the like argument doesn't have any modifiers, so it should actually become a normal equal. (I noticed this later, goes beyond my original point, will file a bug for enhancement.) Also I'm quite surprised that the primary key lookup still takes a fair amount of time, could be i/o related obviously, but the performance on the second run is again huge, even for other arguments. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.16 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkysWw8ACgkQYH1+F2Rqwn19AgCgk9lkRBLcWdylxxz9M4B8a8f5 5JUAn1TOU1VxpvVfVWJMLwlwp9Pz4a0x =IqCY -----END PGP SIGNATURE-----

2 1

[MonetDB-users] Dump schema w/ mclient?
by Mark Bucciarelli 05 Oct '10

05 Oct '10

Is there a way to user mclient to dump the db schema without dumping the data? I tried creating a one line SQL file with \d <tablename> and piping to mclient stdin, but it didn't work. Thanks, m

3 2

[MonetDB-users] add a unique constraint, need to create index?
by Mark Bucciarelli 04 Oct '10

04 Oct '10

Hi, I added a unique constraint on two fields, and noticed there was not an index created on those two fields. Do I need to do a CREATE INDEX or is it implicit? I will be using those two fields a lot in WHERE clauses. Thanks, m

1 1