users-list January 2011

users-list@monetdb.org

14 participants
14 discussions

[MonetDB-users] collation - sorting order

by Ramkumarlak

Hi, Can we control the sorting order in MonetDB? Is there any way we can specify collation ...? Regards Ram -- View this message in context: http://old.nabble.com/collation---sorting-order-tp30786899p30786899.html Sent from the monetdb-users mailing list archive at Nabble.com.

11 years, 6 months

[MonetDB-users] date functions in monetdb

by Ramkumarlak

Hi, Let me know whether the following MYSQL functions are available in MonetDB. 1) concat() 2) date() 3) from_unix_time() 4) interval() 5) Weekday() If not, are there any equivalent functions available ? Regards Ram -- View this message in context: http://old.nabble.com/date-functions-in-monetdb-tp30775963p30775963.html Sent from the monetdb-users mailing list archive at Nabble.com.

11 years, 6 months

[MonetDB-users] Performance question between schema

by brien colwell

With Monet DB I've encountered a counter- intuitive performance characteristic. I have a dataset of 33554432 (2^25) triples, (a, b, c). The values a and b are uniformly distributed on [0, 16), and c is unique to each triple, on [0, 2^25). I store them in two ways: Schema 1: One table with three columns (a, b, c) Schema 2: Two tables with two columns each, (a, c) and (b, c) I'm running SQL queries to select the "c" value from the triples based on a=X and b=Y. With schema 1, this is a single select. With schema 2, this is an inner join between two selects. What surprised me is that the query against schema 2 is on average >50 ms faster to return than the query against schema 1. To me this was counter- intuitive since schema 1 stores less data, and the data are pre-joined (in a row-oriented sense) for the type of filter applied. However, I'm guessing this has to do with how MonetDB stores data in columns. Is the query against schema 1 actually doing three joins, "a" -> "b" -> "c", on an artificial join key? For example, -- Schema 1) create table abc (a bigint, b bigint, c bigint); -- Schema 2) create table ac (a bigint, c bigint); create table bc (b bigint, c bigint); copy 33554432 records into abc from '/home/hadoop/abc.monetdb'; copy 33554432 records into ac from '/home/hadoop/ac.monetdb'; copy 33554432 records into bc from '/home/hadoop/bc.monetdb'; -- Query Schema 1) select abc.c from abc where abc.a=3 and abc.b=7 order by abc.c asc -- Times to return (JDBC) -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.287, SQL query: 0.287, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.288, SQL query: 0.288, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.35, SQL query: 0.35, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.372, SQL query: 0.372, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.405, SQL query: 0.405, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.285, SQL query: 0.285, Building output: 0 -- Query Schema 2) select ac.c from ac inner join bc on bc.b=7 and bc.c = ac.c where ac.a = 3 order by ac.c asc -- Times to return (JDBC) -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.218, SQL query: 0.218, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.216, SQL query: 0.216, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.318, SQL query: 0.318, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.322, SQL query: 0.322, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.218, SQL query: 0.218, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.219, SQL query: 0.219, Building output: 0 -- Query 1 of 1, Rows read: 100, Elapsed time (seconds) - Total: 0.222, SQL query: 0.222, Building output: 0

11 years, 6 months

[MonetDB-users] "similar to" text matching syntax

by brien colwell

I came across a commit for a "similar to" syntax for text columns -- http://www.mail-archive.com/checkin-list@monetdb.org/msg01289.html Very interested. Are there any updates on this commit -- stable, not stable, not tested? Does MonetDB publish nightly builds?

11 years, 6 months

[MonetDB-users] quer cache

by Ramkumarlak

Hi, Does monetdb have query cache ? If so, how do we disable it ? I searched in monetdb5.cong but i did not find any option. Regards Ram -- View this message in context: http://old.nabble.com/quer-cache-tp30747505p30747505.html Sent from the monetdb-users mailing list archive at Nabble.com.

11 years, 6 months

[MonetDB-users] copy select

by Nafees Ur Rehman

Hi, With executing the statement below: COPY SELECT * FROM adv LIMIT 10 INTO 'D:/exp.txt' I get the following error: !TypeException: user.s0_1[4703]:'aggr.count' undefined in : tuples:wrd :=aggr.count(_4695:str) !SQLException:SQLengine:Program contains errors What am I doing wrong? Thanks Nafees

11 years, 6 months

[MonetDB-users] ODBC error 'Function Sequence Error' occurredwhen loaded TPC-H data of MonetDB

by JinBinge

Hi, Benchmark Factory for Database was used to test TPC-H of MonetDB. Errors occurred when creating objects for TPC-H option was selected,such as ODBC error 'Function Sequence Error'. And tables h_nation,h_region,h_supplier' data were loaded successful,the other five tables failed to load data. follows are the test configuration: OS: Linux Hardware: amd64 MonetDB version: Oct2010 Benchmark Factory for Database version: v5.7.1g.504 load data'SF=1G Are there errors in ODBC of MonetDB? Yours sincerely Binge Jin

11 years, 6 months

[MonetDB-users] Query Error Question

by Brett Spurrier

Hi all, I have yet another query that I am having trouble running with Monet, and I am hoping a seasoned expert can help me figure out what is going on here. My query is: SELECT fid, title, D0 AS ed FROM signatures t WHERE fid = ( SELECT fid FROM signatures ti WHERE ti.guid = t.guid ORDER BY D0 ASC LIMIT 1 OFFSET 1) ORDER BY D0 ASC, fid DESC; but I am getting the error: "!syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select fid, title, d0 as ed from signatures t where fid = ( select fid from signatures ti where ti.guid = t.guid order" So it appears to not like the ORDER BY term, and I can't figure out why. I am trying to grab the second element (after ordering by D0) based on distinct GUIDs. The query iteself is based on the answer for a question from stackoverflow that I posted: http://stackoverflow.com/questions/4634068/sql-query-with-order-by-part-2 Can anyone help me figure this out? The syntax looks fine to me. By the way, I have "D0 AS ed" because D0 will eventually be replaced with a simple math operator (i.e., D0/D1). Many thanks, Brett

11 years, 6 months

[MonetDB-users] An error about MonetDB' TPCH power test

by 金炳娥

Hi all, Recently I used the Benchmark Factory tool to test TPCH of MonetDB(SF=100). When running power test,there is an error,such as follows: Started test TPC-H Power Test Agent(bejin) Error: ODBC Error (HY000,0) - [MonetDB][ODBC Driver 1.0]SQLException:convert:too many digits (21 > 19) Do you know the reason and how to reslove the problem? Looking forwards to yours answer. Yours sincerely Binge Jin

11 years, 6 months

[MonetDB-users] Add primary key performance

by paulr135

I've loaded a 27GB (~165M rows) file into a table in MonetDB and now I am trying to add a primary key which consists of 4 fields - 2 varchars and 2 ints. It has been running for a few days (since Friday of last week). I've followed the instructions laid out on this post: http://old.nabble.com/MonetDB-SQL:-bulk-loading-large-data-sets---adding-ke… which involves setting the optimizer variable to "nov2009_pipe". I am unable to log in via mclient - I am able to put in my credentials but it just hangs at the "Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2010-SP2)" line. So I have a few questions: 1. Is there any way I can see the status of the alter table ... add primary key command? 2. Is it possible to stop the alter table gracefully? Or will I have to shut the db down? 3. Is there anything I should do in future to speed up adding of primary keys? Thanks. -- View this message in context: http://old.nabble.com/Add-primary-key-performance-tp30397515p30397515.html Sent from the monetdb-users mailing list archive at Nabble.com.

11 years, 6 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list January 2011