March 2009 - users-list

[MonetDB-users] Combined table is not really combined after all
by Yue Sheng 25 Mar '09

25 Mar '09

two tables: table_1 and table_2 both have exactly the same schema. table_1 has 2.15MM rows of data, table_2 has 3.35MM row of data. No indexing created a new table: table_combined using same schema as table_1 insert into table_combined select * from table_1; (30 minutes later) insert into table_combined select * from table_2; (40 minutes later) select count(*) * 1 from table_combined; this give ONLY 2.15MM rows with same rows as table_1 What happened to table_2?!? Yes, I've check dfarm size. It equals 2*(table_1 + table_2 ) So it's there but "just not seeing it"

5 14

Re: [MonetDB-users] Combined table is not really combined after all
by Jens Oehlschlägel 23 Mar '09

23 Mar '09

Yue, > select count(*) from table_combined; What do you get if you update one of your double columns to 1.0 and do a SUM() over this column? Jens

2 1

[MonetDB-users] Adding interval month problem
by Alfred Nordman 23 Mar '09

23 Mar '09

Hi, Consider the following table: create table "table1" ("Pdate" timestamp, "Price" double); insert into "table1" values ('2007-04-21 00:00:00.000000', 7555); insert into "table1" values ('2007-04-22 00:00:00.000000', 7834); insert into "table1" values ('2007-04-23 00:00:00.000000', 5631); insert into "table1" values ('2007-04-24 00:00:00.000000', 7555); insert into "table1" values ('2007-04-25 00:00:00.000000', 7555); I tried using the following query: SELECT ("table1"."Pdate") AS "PurchaseaDate", ("table1"."Pdate" + interval '1' month) AS "PurchaseaDate2", ("table1"."Price") AS "Price" FROM "table1" And got the error: !TypeException:user.s6_2[19]:'mtime.addmonths' undefined in: _61:any := mtime.addmonths(_59:timestamp, A0:int) !TypeException:user.s6_2[20]:'bat.insert' undefined in: _62:any := bat.insert(_53:bat[:oid,:timestamp], _58:oid, _61:any) Any ideas? Thanks, Alfred. -- View this message in context: http://www.nabble.com/Adding-interval-month-problem-tp22657190p22657190.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] sql characters
by dariuszs 22 Mar '09

22 Mar '09

Hi, How do I insert this value into a varchar(20) column? I get an error. "MERS'' Thanks. Dariusz.

3 6

[MonetDB-users] Row_Number() over problem
by Alfred Nordman 22 Mar '09

22 Mar '09

Hi, Given the following table: create table "table1" (col1 varchar(20), col2 int) insert into "table1" values ('a1', 1) insert into "table1" values ('a2', 3) insert into "table1" values ('a3', 5) insert into "table1" values ('a4', 6) insert into "table1" values ('a5', 3) insert into "table1" values ('a6', 7) The following query: select col1, "SumCol2", "rank" from ( select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank" from ( select col1, (sum(col2)) as "SumCol2" from "table1" group by col1) as "temp1") as "temp2" Results: col1 SumCol2 rank a1 1 1 a2 3 3 a3 5 4 a4 6 5 a5 3 2 a6 7 6 This is good result set. But if one adds this query a where clause that involves the rank column, empty result set is returned. Example: select col1, "SumCol2", "rank" from ( select col1, "SumCol2", (row_number() over(order by "SumCol2")) as "rank" from ( select col1, (sum(col2)) as "SumCol2" from "table1" group by col1) as "temp1") as "temp2" where rank = 1 any ideas? Thanks Alfred -- View this message in context: http://www.nabble.com/Row_Number%28%29-over-problem-tp22577176p22577176.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 7

[MonetDB-users] sql characters
by dariuszs 21 Mar '09

21 Mar '09

Hi, How do I insert this value into a varchar(20) column? I get an error. "MERS'' Thanks. Dariusz.

1 0

[MonetDB-users] !FATAL: GDKlockHome: Database lock '.gdk_lock' denied
by Yue Sheng 20 Mar '09

20 Mar '09

System hanged for some reason. Cannot kill the process: user 2389 0.0 0.0 0 0 ?? E 6:33PM 0:00.00 (mserver5) Hence cannot restart server. Short of restarting the whole machine, is there a better way to handle this please?

1 0

[MonetDB-users] SQL DATEADD equivalent
by Yue Sheng 20 Mar '09

20 Mar '09

MonetDB doesn't seem to support DATEADD function. I need it to convert Unix time on-the-fly (700milliion times for each query!) . Could anyone recommend a fast way to do this please? Thanks

4 11

[MonetDB-users] speed sanity check
by Yue Sheng 20 Mar '09

20 Mar '09

I have a table with 700million rows, and 14 columns. All doubles. Each column of data have non-unique entries. No indexing. My query: SELECT * FROM bigtable WHERE col4 < 1000 limit 100; took 35 minutes to run. (a) Is this about the average time to run? (b) Should I explicitly index col4? (Website seems to suggest MonetDB takes care of indexing automatically) If so, how much benefit should I expect? Thanks.

2 2

Re: [MonetDB-users] Open source Column based DB ... for timeseries
by Jens Oehlschlägel 20 Mar '09

20 Mar '09

Yue, > I really like "ff", haven't used "bit" yet. I use ff, together with biglm for large scale regression. Thanks > I need a database to stored all my data, in the billions of rows, so that I can keep analysis within R and data retrieval in database. ff under 32 bit has a limit on vector length of integer.max ~ 2 billion elements we have not compiled under 64bit so far - you might want to try this on a machine with much RAM and a fast RAID. > You think "ff" is just as fast as MonetDB when it comes to dealing with billions of rows, and from different "ff" files (the equivalent of tables) 1) ff does not have tables (or data.frames) with columns of different types in the current version. This will happen during this year. Of course you can just work with a bunch of vectors 2) Speed is a stubborn beast which behaves very different depending on what you actually are doing. Taking your example "give me the total traded volume of IBM between 10:05am and 10:09am" If we assume that your tick data comes in in in a fixed time rythm and you are looking on historical data, then it should be possible to CALCULATE the positions of those elements falling in your time window. In this case you would get away with disk reading just those = no reading of an index or scanning a complete column. In ff you can access vector elements just by their position. Hard to believe that any DB access could be faster than that. If by contrast you need to scan many columns to decide which records you want, a column- or even row-based DB with indexes might be faster. Details matter. Let me know in case you tried compiling ff under 64 bit. Jens

1 0