May 2014 - users-list

[MonetDB-users] Books CDs
by CHARTS LICENSING 11 Aug '24

11 Aug '24

Everything you want to know this dynamic artist check. At blueberry hills duck, roomfull story gtgt getting. Intriguing, life in biography. Here music looking for lyrics maybe, youre find which! Download free wallpapers featuring business cmg worldwide is exclusive. Videos latest products been easier. Exclusive estate we consider request use name voice, image. List covered berries charts licensing message! Reelin and rockin about, has everything you? Many exciting, photos him stage click! Artist check, out some quotes read all intriguing. It alive he, did on occasion birthday wednesday? Has everything you want to know this? Quotes read all intriguing life, in biography or. Gtgt getting licks years reelin and. Click here, music looking for lyrics maybe, youre. News celebrates his th rock will never! Chuck berry news celebrates his th rock, will. Browse through many exciting, photos him stage click here. He did on occasion birthday wednesday night at blueberry. Wallpapers featuring business cmg worldwide. Worldwide is exclusive estate we consider, request use name. Want to know this dynamic artist check out. Awards fast, facts clips list covered. Music looking for lyrics maybe youre. Message board downloads tour dates fan, club inquiries. Other sites, download free wallpapers featuring, business cmg worldwide. Request use, name, voice.

2 1

Multi-column update based on joined table
by Lee Hachadoorian 11 Aug '24

11 Aug '24

I need to update multiple columns (~250) from one table to another based on a two-column key. In Postgres I would write this using the FROM clause, but since MonetDB doesn't support the FROM clause, I've successfully updated one column with: UPDATE census.acs2010_5yr SET b07401001 = ( SELECT CAST(b07401001 AS int) FROM acs2010_5yr.tmp_seq0001 WHERE acs2010_5yr.stusab = tmp_seq0001.stusab AND acs2010_5yr.logrecno = tmp_seq0001.logrecno ); My question is whether there is any benefit to writing a multiple column update into a single UPDATE statement: UPDATE target_table SET column1 = (SELECT column1 FROM source_table WHERE target_table.key = source_table.key), column2 = (SELECT column2 FROM source_table WHERE target_table.key = source_table.key), ..., columnN = (SELECT columnN FROM source_table WHERE target_table.key = source_table.key); On Postgres I believe the multi-column update using a table join would only have to do the sequential scan of each table once, but with the necessity of sub-selects in MonetDB, what is the backend doing? Is it already doing an index scan once for each row in the single column case, then doing it once for each row * column in the multi-column case? Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/

3 3

[MonetDB-users] XQuery API and XLink/XUpdate support
by Alexandra Mart�nez 27 Jun '24

27 Jun '24

Hello all, I was wondering if there is any documentation about the API for XQuery (i.e., how to write xqueries from a Java program). Also, I am interested in using some features of XLink and XUpdate on top of my XML database. Is support for any of these in progress? Thanks in advance, Alexandra ______________________________________________________ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/

4 3

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

odbc (64-bit) problems in Windows?
by László Sándor 06 Jun '14

06 Jun '14

Hi, I reinstalled MonetDB as well as the ODBC driver after last week's update to SP2. How could I troubleshoot my system if I get a Memory allocation error SQLSTATE=HY001 if Stata would query the database? Otherwise I do see the data source listed, both in Stata (with -odbc query-) and Microsoft ODBC Data Source Administrator. (The latter lists the database under System DSN with the right name, platform and driver.) Thanks, Laszlo

1 1

Announcement: New Jan2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 02 Jun '14

02 Jun '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-SP2 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/>. Jan 2014-SP2 bugfix release Build Environment * Lots of minor fixes were made for potential defects found by Coverity Scan. * On Windows we now build the geom module against version 3.4.2 of the geos library. Client Package * ODBC: Implemented {call procedure-name(...)} escape. The version {?=call ...} is not implemented. Bug Fixes * 3424: numeric values at the front of strings determines whether CAST works successfully * 3439: Python driver drops milliseconds from timestamps * 3446: SET READ ONLY forgets previous changes * 3455: String columns unusable from 64-bit .NET via ODBC * 3456: Insert fails * 3457: When kernel of remote client crashes, the connection remains established on server side * 3458: mserver5 crash on SQL: SELECT COUNT(*) FROM SYS.TABLES HAVING COUNT(*) > 0 * 3461: mserver5 crash on SQL: SELECT * FROM SYS.ARGS WHERE FUNC_ID NOT IN (SELECT ID FROM SYS.FUNCTIONS) OR FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS) * 3462: Invalid SQL (IN with subquery which returns multiple columns) is accepted * 3463: Crash on SELECT with SERIAL aggregation and GROUP BY column alias's * 3468: Local temporary table persists across sessions * 3469: Absolute network paths considered invalid for COPY INTO ... FROM statement. * 3473: Various memory leaks in SQL compilation * 3477: ODBC driver raises "unexpected end of input" for prepared string parameter from .NET application * 3481: Cannot run multiple COPY INTO statements in one 's'-command * 3482: Crossproduct error

3 9

how to count the number of query results in monetdb
by Bing Zhang 29 May '14

29 May '14

Hi: Is there a fast way to get the number of results, instead of looping on the ResultSet.next()? Thanks.

1 0

Processing web logs in monetdb
by Christopher Nelson 27 May '14

27 May '14

Hello, I've been looking at monetdb for a while, and I've been very impressed with the project. I would like to use monetdb to analyze web log data from a very active site. I have no doubt that it can handle the quantity of data, but I'm not sure how to perform some of the queries that I have used in other systems. For example, I would like in particular to be able to perform click-stream analysis of the data. On other systems I've used proprietary extensions which allowed me to create a window by pattern matching a certain column. Do you have any suggestions about where I should look to perform similar kinds of queries? Thanks! -={C}=-

2 1

question: efficient use of partitioned tables
by László Sándor 25 May '14

25 May '14

Hi, This might be less of MonetDB-specific question about features than about RDBMS (or just SQL) in general, but I would still appreciate your advice in these matters. I have data with a few "cross-sectional" tables (e.g. the parents of an individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately). I wonder if it is a good idea to merge/union the annual tables into long panels. The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary steps. However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. The merged tables could be too big anyway — if it matters in MonetDB (think of tables ~70 GB in some cases, while my server has 128 GB RAM), while many use cases would focus on a subset of years anyway. Do you have any thoughts about this? Maybe more concrete questions would be: — How cumbersome would it be (for a novice!) to code joining data from the annual tables separately, for multiple series of tables? (E.g. union all tax returns for 1997-2008, then join all residential data for these 12 years year-by-year, then employment records year-by-year etc.) — How much slower would this *run* than simply selecting years from the longitudinal tables? — Shall I be worried about the longitudinal tables having the size on the same order of magnitude as my physical memory, or MonetDB handles it equally efficiently (it would need to access many of the small annual tables anyway, that also takes memory). Thanks, Laszlo

1 0

Strange query performance
by N.H. Schuiling 24 May '14

24 May '14

Hi all, Can someone explain me why this simple query takes so long to execute on MonetDB 5 server v11.17.13 "Jan2014-SP1"? sql>select * from fp_tree limit 1; +-----------------------------+ | path | +=============================+ | ;8;60;379;519;1241;174;692; | +-----------------------------+ 1 tuple (1.2s) Table fp_tree has one column path with data type string. Some useful statistics of table fp_tree: sql>select count(*) from fp_tree; +--------+ | L1 | +========+ | 416499 | +--------+ 1 tuple (2.801ms) sql>select max(length(path)) from fp_tree; +------+ | L1 | +======+ | 1275 | +------+ 1 tuple (254.742ms) sql>select min(length(path)) from fp_tree; +------+ | L1 | +======+ | 3 | +------+ 1 tuple (272.186ms) sql>select avg(length(path)) from fp_tree; +--------------------------+ | L1 | +==========================+ | 202.73579288305606 | +--------------------------+ 1 tuple (331.306ms) Query trace: +---------+-------------------------------------------------------------------+ | ticks | stmt | +=========+===================================================================+ | 3 | X_2 := sql.mvc(); | | 29 | X_35:bat[:oid,:oid] =<tmp_440>[104124] := sql.tid(X_2=0,"sys","fp | : : _tree",0,4); : | 6 | X_8=<tmp_3501>[0] := sql.bind(X_2=0,"sys","fp_tree","path",1); | | 40 | (X_52:bat[:oid,:oid] =<tmp_2110>[0],X_53:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,3,4); : | 8 | X_45:bat[:oid,:str] =<tmp_1357>[104127] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,3,4); : | 3 | X_57=<tmp_1357>[104127] := sql.delta(X_45=<tmp_1357>:bat[:oid,:st | : : r][104127],X_52=<tmp_2110>:bat[:oid,:oid][0],X_53=<tmp_3501>:bat[ : : : :oid,:str][0],X_8=<tmp_3501>[0]); : | 4 | X_41:bat[:oid,:oid] =<tmp_1256>[104127] := sql.tid(X_2=0,"sys","f | : : p_tree",3,4); : | 8 | X_61=<tmp_302>[104127] := algebra.leftfetchjoin(X_41=<tmp_1256>:b | : : at[:oid,:oid][104127],X_57=<tmp_1357>[104127]); : | 10 | X_65=<tmpr_1256>[1] := algebra.subslice(X_61=<tmp_302>[104127],0: | : : wrd,0:wrd); : | 6 | X_71=<tmp_1357>[1] := algebra.leftfetchjoin(X_65=<tmpr_1256>[1],X | : : _61=<tmp_302>[104127]); : | 1 | language.pass(X_61=<tmp_302>[104127]); | | 24 | (X_50:bat[:oid,:oid] =<tmp_2110>[0],X_51:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,2,4); : | 5 | X_44:bat[:oid,:str] =<tmp_1560>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,2,4); : | 3 | X_56=<tmp_1560>[104124] := sql.delta(X_44=<tmp_1560>:bat[:oid,:st | : : r][104124],X_50=<tmp_2110>:bat[:oid,:oid][0],X_51=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_39:bat[:oid,:oid] =<tmp_1760>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",2,4); : | 5 | X_60=<tmp_302>[104124] := algebra.leftfetchjoin(X_39=<tmp_1760>:b | : : at[:oid,:oid][104124],X_56=<tmp_1560>[104124]); : | 8 | X_64=<tmpr_1760>[1] := algebra.subslice(X_60=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 4 | X_70=<tmp_1560>[1] := algebra.leftfetchjoin(X_64=<tmpr_1760>[1],X | : : _60=<tmp_302>[104124]); : | 1 | language.pass(X_60=<tmp_302>[104124]); | | 21 | (X_48:bat[:oid,:oid] =<tmp_2110>[0],X_49:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,1,4); : | 5 | X_43:bat[:oid,:str] =<tmp_1660>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,1,4); : | 1 | X_55=<tmp_1660>[104124] := sql.delta(X_43=<tmp_1660>:bat[:oid,:st | : : r][104124],X_48=<tmp_2110>:bat[:oid,:oid][0],X_49=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_37:bat[:oid,:oid] =<tmp_1054>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",1,4); : | 5 | X_59=<tmp_302>[104124] := algebra.leftfetchjoin(X_37=<tmp_1054>:b | : : at[:oid,:oid][104124],X_55=<tmp_1660>[104124]); : | 8 | X_63=<tmpr_1054>[1] := algebra.subslice(X_59=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 5 | X_69=<tmp_1660>[1] := algebra.leftfetchjoin(X_63=<tmpr_1054>[1],X | : : _59=<tmp_302>[104124]); : | 1 | language.pass(X_59=<tmp_302>[104124]); | | 23 | (X_46:bat[:oid,:oid] =<tmp_2110>[0],X_47:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,0,4); : | 9 | X_42:bat[:oid,:str] =<tmp_462>[104124] := sql.bind(X_2=0,"sys","f | : : p_tree","path",0,0,4); : | 2 | X_54=<tmp_462>[104124] := sql.delta(X_42=<tmp_462>:bat[:oid,:str] | : : [104124],X_46=<tmp_2110>:bat[:oid,:oid][0],X_47=<tmp_3501>:bat[:o : : : id,:str][0]); : | 6 | X_58=<tmp_764>[104124] := algebra.leftfetchjoin(X_35=<tmp_440>:ba | : : t[:oid,:oid][104124],X_54=<tmp_462>[104124]); : | 8 | X_62=<tmpr_440>[1] := algebra.subslice(X_58=<tmp_764>[104124],0:w | : : rd,0:wrd); : | 10 | X_72=<tmp_462>[1] := mat.packIncrement(X_62=<tmpr_440>[1],4); | | 3 | X_74=<tmp_462>[2] := mat.packIncrement(X_72=<tmp_462>[2],X_63=<tm | : : pr_1054>[1]); : | 1 | language.pass(X_63=<tmpr_1054>[1]); | | 2 | X_75=<tmp_462>[3] := mat.packIncrement(X_74=<tmp_462>[3],X_64=<tm | : : pr_1760>[1]); : | 1 | language.pass(X_64=<tmpr_1760>[1]); | | 3 | X_66=<tmp_462>[4] := mat.packIncrement(X_75=<tmp_462>[4],X_65=<tm | : : pr_1256>[1]); : | 1 | language.pass(X_65=<tmpr_1256>[1]); | | 9 | X_11=<tmpr_1206>[1] := algebra.subslice(X_66=<tmp_462>[4],0:wrd,0 | : : :wrd); : | 5 | X_68=<tmp_1760>[1] := algebra.leftfetchjoin(X_62=<tmpr_440>[1],X_ | : : 58=<tmp_764>[104124]); : | 1 | language.pass(X_62=<tmpr_440>[1]); | | 6 | language.pass(X_58=<tmp_764>[104124]); | | 1019824 | X_77=<tmp_1256>[1] := mat.packIncrement(X_68=<tmp_1760>[1],4); | | 8 | X_78=<tmp_1256>[2] := mat.packIncrement(X_77=<tmp_1256>[2],X_69=< | : : tmp_1660>[1]); : | 7 | X_79=<tmp_1256>[3] := mat.packIncrement(X_78=<tmp_1256>[3],X_70=< | : : tmp_1560>[1]); : | 18 | X_67=<tmp_1256>[4] := mat.packIncrement(X_79=<tmp_1256>[4],X_71=< | : : tmp_1357>[1]); : | 18 | X_12=<tmp_1357>[1] := algebra.leftfetchjoin(X_11=<tmpr_1206>[1],X | : : _67=<tmp_1256>[4]); : | 1095479 | barrier X_89 := language.dataflow(); | | 13 | X_13 := sql.resultSet(1,1,X_12=<tmp_1357>[1]); | | 7 | sql.rsColumn(X_13=7,"sys.fp_tree","path","clob",0,0,X_12=<tmp_135 | : : 7>[1]); : | 2 | X_17 := io.stdout(); | | 33 | sql.exportResult(X_17=="104d2":streams,X_13=7); | | 0 | end s2_2; | | 1095725 | function user.s2_2(); | | 1095772 | X_5:void := user.s2_2(); | +---------+-------------------------------------------------------------------+ 56 tuples (1.1s) Why does mat.packIncrement takes so much time? Thanks in advance, Nik

1 0