April 2014 - users-list

Scope of local temporary tables
by Alastair McKinley 05 Apr '14

05 Apr '14

Hi all, I was wondering if I could clarify something about the scope of local temporary tables in MonetDB. I imagined that these would be local to a single client session but it seems that I am wrong about this. If I execute the following Python script multiple times in succession, it fails as the table name is already in use. import monetdb.sql import sys dbh = monetdb.sql.Connection(port=int(sys.argv[1]),database=sys.argv[2],hostname=sys.argv[3],autocommit=True) cursor = dbh.cursor() cursor.execute('create local temporary table input (term int, p float) on commit preserve rows;'); cursor.execute('insert into input values ( 1,0.1);'); cursor.execute('select * from input;'); print(cursor.fetchall()) dbh.close() How is the local temporary table different from a normal table in this case? Best regards, Alastair

2 3

Fastest ('standard") way to load data into MonetDB
by Masood Mortazavi 04 Apr '14

04 Apr '14

What is the fastest way to load data into MonetDB, using its "standard" user APIs? -m.

2 3

How to update one table from another table or from aggregation in subqueries?
by George Kollias 04 Apr '14

04 Apr '14

Hello, On a related note, would it be possible to have a "with" statement inside the subquery, .i.e. "update t1 set data = *(with tmp_table as (select ...) select ... from ... where ...)* where exists (select * from t2 where t1.id= t2.id);"? According to my tests it's not possible. What would be the best way to simulate it in MonetDB? Using a View/Temporary Table? Thank you, George > Thanks for your reply and clarification! > With Jan2014 release, the two functions can be expressed in the following > working forms: > 1) update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) > where exists (select * from t2 where t1.id=t2.id); > 2) update t1 set data = (select sum(t2.data) from t2 where t2.id=t1.idgroup > by t2.id) where exists (select * from t2 where t1.id=t2.id);

1 0

MonetDB Performance and Data Schema
by Dino Veritas 02 Apr '14

02 Apr '14

Hi, My firm is investigating the use of MonetDB as a potential market data store. I am interested in the opinion of those on this list as to how best to structure the data for optimum performance. Basically, our data consists of ~50,000 instruments. One use we are looking at is for the storage of bar data, one for each minute.The resulting data for each bar is like <Timestamp,Open,High,Low,Close,Volume,...>, with one observation per minute. While not all symbols will have an entry for each minute, many observations will share the same Timestamp. Is this an appropriate use for MonetDB? One schema I tried was to put all symbols into the same table, and to separate the tables by month. So for 5 years that would be 5*12=60 tables. A query along the lines of "SELECT * FROM "200801" WHERE symbol='IBM' UNION ALL SELECT * FROM "200802" WHERE symbol='IBM' UNION ALL ...)" The reads for a single table were quite good, however, when reading across multiple tables I encountered a lot of swap space and disk IO which drastically slowed down the query. I also tried one table per symbol but found this to be simply untenable as INSERTS became extremely slow (on the order of minutes) and once again, massive swap space usage. How would you partition this data? We will be inserting once per day in bulk, and I am not super concerned with the insert performance. However, query performance is paramount, with some users querying data very frequently for long time periods for single or groups of symbols. The machine I am testing on has 16GB of RAM and is running Windows. However, the machine MonetDB would ultimately be deployed on would have much more RAM. Even so, the size of the results of a single query are less than 16GB and should fit into memory without issue. So please help me, MonetDB gurus, with any suggestions as to how you would partition and store this data. I would appreciate any input. Thank you!

5 8

Cycling through cursor in a procedure
by Radovan Bičiště 01 Apr '14

01 Apr '14

Hello, Please is there a flow control syntax for cycling through rows in a cursor in a procedure? I can not find any other solution to how to select highest numbers from a table which total falls under certain threshold. :( I have a function that returns sorted rows and can be used as a subquery and I did not get further ... Thank you, Radovan

4 5