- users-list - monetdb.org

information about compression/decompression techniques of monetdb
by bhuvan Mitra 19 Jan '15

19 Jan '15

I have been studying about various column-oriented databases and found monetdb to be very interesting as it is efficient and open source. Please tell me in which file/folder does source code of compression/decompression techniques written in monetdb. I am interested to learn the implementations of light weight compression algorithms and cache-decompression. I have source code of monetdb downloaded, just let me know the path of requested codes.

1 0

regarding source code
by bhuvan Mitra 19 Jan '15

19 Jan '15

Hello, I am very interested to know and learn about implementations of compression/decompression techniques that monetdb is following. I have seen the monetdb's light weight compression algorithms(pfor, pfor-delta, pdict), cache involved decompression techniques and excited to know how actually are they implemented in form of codes. Kindly tell me in which folder/file is the source code of this compression/decompression algorithms present. I have downloaded the source tarball from the website and could not find these code snippets. Plese help me to identify the path. Regards, mg

2 1

analyse performance slow query
by Pierre-Adrien Coustillas 16 Jan '15

16 Jan '15

Hi, I try to analyze the slowness of a query with EXPLAIN and stethoscope The query is : SELECT count (*) FROM ( SELECT id_unique, siteniveau1, siteniveau2, '0000-00-00', 0, 0, MAX( debutvisite ), 0, 0 FROM tmp __ at __ lm __visites_ agregees _id_unique GROUP BY id_unique,siteniveau1,siteniveau2 ) tbl ; the beginning of explain is ( there is 272 tuples ) +============================================================================================================================================================================================+ | function user.s2_5{ autoCommit = true }(A0: str ,A1: bte ,A2: bte ,A3: bte ,A4: bte ): void ; | | barrier X_429 := language . dataflow (); | | X_7 := sql . mvc (); | | X_55:bat[: oid ,: oid ] := sql . tid (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique",0,8); | | X_70:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","id_unique",0,0,8); | | (X_78:bat[: oid ,: oid ] ,X_79:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","id_unique",2,0,8); | | X_161 := sql .delta(X_70,X_78,X_79); | | X_169 := algebra . leftfetchjoin (X_55,X_161); | | X_96:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau1",0,0,8); | | (X_111:bat[: oid ,: oid ] ,X_112:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau1",2,0,8); | | X_184 := sql .delta(X_96,X_111,X_112); | | X_193 := algebra . leftfetchjoin (X_55,X_184); | | X_129:bat[: oid ,: int ] := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau2",0,0,8); | | (X_144:bat[: oid ,: oid ] ,X_145:bat[: oid ,: int ] ) := sql . bind (X_7," sys "," tmp __ at __ lm __visites_ agregees _id_unique","siteniveau2",2,0,8); | | X_201 := sql .delta(X_129,X_144,X_145); | | X_209 := algebra . leftfetchjoin (X_55,X_201); | | (X_224,X_225,X_226) := group. subgroup (X_209); | | (X_248,X_249,X_250) := group. subgroup (X_193,X_224); | | (X_272,X_273,X_274) := group. subgroupdone (X_169,X_248); | | X_275 := algebra . leftfetchjoin (X_273,X_169); | | X_304 := algebra . leftfetchjoin (X_273,X_248); | | X_331 := algebra . leftfetchjoin (X_304,X_249); group. subgroupdone (X_169,X_248); set 10 minutes to run . Why ? how to understand this function ? Thank Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http ://1g6. biz Tél : 06 64 63 70 35

3 2

MSVCR80.dll Error Question
by Zola Javzmaa 16 Jan '15

16 Jan '15

Hello All, I am having trouble installing Monetdb on to my computer. Would anybody have any suggestions to fixing it? I'm on a Window 7, 64 bit operating system and I get the following error message when I try to load the server program: "Error message: This application has failed to start because MSVCR80.dll was not found. Re-installing the application might fix the problem." Thanks, Zola

3 2

rename database
by Pierre-Adrien Coustillas 15 Jan '15

15 Jan '15

Hi, Is it possible to rename a database ? I thought to do like this: monetdb stop database mv /var/lib/monetdb/database /var/lib/monetdb/new_database monetdb start new_database it's okay ? Thank Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

4 3

MonetDB Usage for spherical clustering
by Jiří Nádvorník 14 Jan '15

14 Jan '15

Dear MonetDB support, My name is Jiri Nadvornik and I am working for Czech astronomical institute (http://www.asu.cas.cz/en/about/about-the-institute) For more than a year I’m trying to generate identifiers for astronomical objects used when creating light curves for these objects. In general, I’m working with spherical coordinates and based on Euclidean parameters (and possibly other parameters, like magnitude) I am clustering these objects to assign multiple observations to one real object. A bottleneck when doing this in a relational database (like PostgreSQL) is not the finding of the neighbors as such (~ ms in a loop), but the inefficiency of working with disk IO when doing so. So after a little analysis of what could be done, I am quite interested into storing the data (mainly spherical coordinates) in an array DB, physically partition regions of the sky to multiple disks and then use the clustering on these partitions in parallel. My question no. 1 for you is whether MonetDB has some kind of support for spherical indexing (PgSphere, Q3C, HealPix, …) to ingest and correctly distribute the astronomical data. I need to control the way of how the data will be distributed to physical partitions. If question no.1 can be somehow answered as true, then question no 2. is: What is the easiest way to install MonetDB, ingest my data and test them? P.S.: Our processed dataset contains now cca 3e8 observations (entries for clustering) which resolve to about 6e6 real objects. We expect that number to double when we process all of our data. Thank you for your answer. Kind regards, Jiri Nadvornik Astronomical Institute of the Academy of Sciences of the Czech Republic nadvornik.ji(a)gmail.com

3 2

Bug in Query Plan??
by Vijay Krishna 09 Jan '15

09 Jan '15

Hi, I have been studying on multiple join performances in MonetDB. I am running the latest version of MonetDB Oct2014 - SP1 (MonetDB-11.19.3) on a CentOS machine with 128 GB RAM. All my tables have 100 to 300 columns and around 35 lakh rows. I have the following query with 5 joins and a few criteria. SELECT * FROM table1 INNER JOIN table2 on table1.col1=table2.col1 LEFT JOIN table3 ON table1.col2=table3.col1 LEFT JOIN table3 ON table1.col3=table3.col1 LEFT JOIN table4 ON table2.col2=table4.col1 LEFT JOIN table5 ON table2.col3=table5.col1 WHERE (((table2.col10 like '%a%' OR table5.col11 like '%s%') and ((table2.col12 like '%w%' *and table4.col13 like '%k'*) OR table2.col14 like '%h%') and (table1.col21 = 1) AND (table1.col22 IN (1,2,3))) AND (((table1.col23 >= 15842000000000000) AND (table1.col23 <= 15842999999999999)) OR ((table1.col23 >= 0) AND (table1.col23 <= 999999999999)))) ORDER BY 1 DESC LIMIT 10 OFFSET 0; This query has only 2000 matching rows and it returns the result in *2.9 seconds*. In the same query, if I remove one criteria alone (the bolded part), the result would still match only 2000 rows, but the result would come in *300 milliseconds* even. I used the PLAN statement and found that the query plan that the executor takes for the first query is a much complex path and *it performs 3 joins for all the 35 lakh rows* (i.e. before executing the criteria even) and hence the time has spiked to 3 seconds. May be the hot data of 35 lakh tuples would have not fit into the memory?? Not sure. But if I remove the highlighted criteria, the executor evaluates the criteria first and *it performs the joins for only 7000 rows* and hence it is faster 1000 times. Is this a bug? Or is there any way to optimise my query to match the executor to choose the right plan? Any help much appreciated. I could even demo this. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0

Is there a way to limit worker threads on Windows
by Bryan Senseman 06 Jan '15

06 Jan '15

All, I have tried various approaches, but nothing changes the ‘using 8 threads’ message displayed at server startup. I have a client (with a client) that is running MonetDB on a 2cpu windows box and getting various errors, I’m trying to duplicate them but cannot and am concerned it might be that my hardware (read laptop) is too “powerful” to create the error condition. I really do not want to light up a virtual machine just to reduce threads. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

5 8

no_mitosis_optimizer - is it advisable?
by Vijay Krishna 05 Jan '15

05 Jan '15

Hi, I have been working on join performances with MonetDB. I tried using various optimizer pipelines. Few costly queries which took 15 seconds with the default pipeline, returned results as fast as 5 seconds with the 'no_mitosis_optimizer' pipeline. I am looking to study more on mitosis optimizer. Is there any reference on what does it do? >From the mserver5 man page, I got this - "forcefully activate mitosis even on small tables, i.e., split small tables in as many (tiny) pieces as there are cores (threads) available;" So, does this mean with the mitosis optimizer, the tables are split and processed? If so, then why are queries slower with mitosis optimizer in the pipeline? Also, from the monetdb man page, I was alerted that "Changing this setting is discouraged at all times." What is the disadvantage of changing the optimizer pipeline to something other than the 'default_pipe'? Though the 'no_mitosis_optimizer' is stable, is it worth for production? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 3

EXPLAIN/TRACE statements reference
by Vijay Krishna 28 Dec '14

28 Dec '14

Hi, I have been studying on the slow query behavior with certain joins in MonetDB. I use explain & trace statements to understand the flow. But, I couldn't actually understand the MAL statements that appear as the output of EXPLAIN/TRACE statements. I tried learning MAL with this reference - https://www.monetdb.org/Documentation/Manuals/MonetDB/MALreference But it had no explanations for system defined MAL statements that appear in trace. For example, from the trace statement, I could find that a lot of time was spent in the MAL expression "language.dataflow()", but without actually knowing what does this step actually do, I am unable to optimize the query. - *Is there any reference guide to understand the output of EXPLAIN/TRACE? Or should we refer to the source code to understand this? * - *Is it possible to know the query plan that Monet arrives to execute a query? * - *Is it possible to make the output of EXPLAIN/TRACE to be in SQL format, like in MySQL?* Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

Monetdb mserver5 performance flag
by Jose Noel Mendoza Bastidas 24 Dec '14

24 Dec '14

Hello, If it is not too much trouble, could someone explain in detail what happens when you work with mserver5 with flag −−performance (JOINPROPMASK | DEADBEEFMASK)? My intention is to execute several queries and measure their performance in time using the structures provided by MonetDB in order to optimize, at the moment I have documented myself about the Optimizer Pipelines and I would like to know if somebody knows about other structures offered by MonetDB to optimize queries. The purpose of my research is to show the advantages of MonetDB, taking advantage of every resource provided to optimize queries. Thanks in advance.

4 3

Monetdb user-defined optimizer pipeline
by Jose Noel Mendoza Bastidas 23 Dec '14

23 Dec '14

Hi, I was measuring the execution time of some queries using different optimizers that provides MonetDB (minimal, default, no_mitosis, sequential and recycler). Now I want to measure these times but using user-defined optimizer pipeline For this I am testing with the following command: MonetDB Septemberoptpipe='optimizer.inline();optimizer.remap();optimizer.deadcode();optimizer.multiplex();optimizer.garbageCollector();' tesisdb According to the documentation that would be equivalent to using minimal_pipe, the fact is that when I use the expressed command above and run the query throws me the following error: Function '@ 7 ?.' not defined I wonder if someone has happened something like this or have any idea how to create different pipes to those provided by the system Thanks in advance

2 2

Enabled and disabled cracking option on monetdb
by Jose Noel Mendoza Bastidas 17 Dec '14

17 Dec '14

Hi, I am a student at Simon Bolivar University in Venezuela , i' ve been performing an experimental study using MonetDB - v11.17.21 (Jan2014-SP3). I was reading two papers by MonetDb developers about Database Cracking where authors got better results with cracking enabled than without it. In these papers, authors stated that they were able to turn on and off the cracking facilities in monetdb. However, I haven' t been able to find information on how this can be done. In my experimental study, I also need to run my queries and study the performace differences obtained using thes two options. My actual question: do you know how I can turn on and off the cracking option on monetdb? Do you have documentation that explain this? Thanks in advance,

2 1

speed query with 2 columns group by
by Pierre-Adrien Coustillas 15 Dec '14

15 Dec '14

Hello, I have an query with the same source data on Jan2014 -SP3 version of monetddb and Oct2014 - SP1 : CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees_lmd_pierre GROUP BY id_unique, Device ) WITH DATA; On version Jan2014 -SP3 : Welcome to mclient , the MonetDB / SQL interactive terminal (Jan2014-SP3) Database : MonetDB v11.17.21 (Jan2014-SP3), 'mapi : monetdb :// lemondedev :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql > CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees _ lmd _pierre GROUP BY id_unique, Device ) WITH DATA; operation successful (6m 30s) On version Oct2014-SP1 : Welcome to mclient , the MonetDB / SQL interactive terminal (Oct2014-SP1) Database : MonetDB v11.19.7 (Oct2014-SP1), 'mapi : monetdb :// monetdb :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql > CREATE TABLE tmp __pierre5 AS ( SELECT id_unique, Device , COUNT (*) AS NBVISISTES FROM donnees _ lmd _pierre GROUP BY id_unique, Device ) WITH DATA; operation successful (383m 20s) explain query is different , you want to explain the results of the query ? Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 7

Support of string function in Monetdb
by Leon-Constantin Fopa 14 Dec '14

14 Dec '14

Hi every body, Does Monetdb supports string function like concat_ws in Postgresql (http://www.postgresql.org/docs/9.1/static/functions-string.html) ? Best regards, Fopa Léon Constantin,

2 1

RAM usage of MonetDB
by Vijay Krishna 13 Dec '14

13 Dec '14

Hi, I have been testing MonetDB for quite a while and I am a bit quizzed by its RAM utilisation. I tested MonetDB with some complex data and queries as per my requirement in 2 machines - *one with 16 GB RAM and the other with 128 GB RAM *with no memory restrictions for applications. There are no applications other than MySQL and MonetDB running in those machines. The puzzling part was *all the queries took the same time with both the machines*. I have read quite a few documentations wherein I found that the MonetDB performance heavily depends upon RAM memory available. What am I missing? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Inserting arrays with SciQL
by Dimitar Misev 11 Dec '14

11 Dec '14

Hi, I've been experimenting with loading arrays into SciQL, but hit a wall at approx. 200MB TIFF size. Not sure if I'm doing something wrong or if there is a better way to import arrays? sql>CALL rs.attach('grey2.tif'); sql>CALL rs.import(2); Connection terminated Here's some metadata of the file: $ gdalinfo grey2.tif Driver: GTiff/GeoTIFF Files: grey2.tif Size is 21600, 10800 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] GeoTransform = 291.065568377342, 3.938632460246877e-06, 8.68784597348722e-07 -22.27513940185346, 8.250100004406028e-07, -3.669324727326387e-06 Metadata: AREA_OR_POINT=Point Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left ( 291.066, -22.275) (291d 3'56.05"E, 22d16'30.50"S) Lower Left ( 291.075, -22.315) (291d 4'29.82"E, 22d18'53.17"S) Upper Right ( 291.151, -22.257) (291d 9' 2.31"E, 22d15'26.35"S) Lower Right ( 291.160, -22.297) (291d 9'36.09"E, 22d17'49.01"S) Center ( 291.113, -22.286) (291d 6'46.07"E, 22d17' 9.76"S) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray I managed to import a similarly sized file previously, but copying it to another file and importing again fails with "Connection terminated" as well. $ gdalinfo grey.tif Driver: GTiff/GeoTIFF Files: grey.tif Size is 21600, 10800 Coordinate System is `' Origin = (-180.016666666666652,90.016666666666666) Pixel Size = (0.016666666666665,-0.016666666666665) Metadata: TIFFTAG_DATETIME=2008:06:22 09:01:55 TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch) TIFFTAG_SOFTWARE=Adobe Photoshop CS3 Macintosh TIFFTAG_XRESOLUTION=72 TIFFTAG_YRESOLUTION=72 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left (-180.0166667, 90.0166667) Lower Left (-180.0166667, -89.9833333) Upper Right ( 179.9833333, 90.0166667) Lower Right ( 179.9833333, -89.9833333) Center ( -0.0166667, 0.0166667) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray Thanks, Dimitar

1 1

Re: inner join very slow
by Stefan Manegold 08 Dec '14

08 Dec '14

Of what type(s) are the join attributes? How big is the expected join result? If you don't know, select count(*), sum(cnt) from (select count(*) as cnt from tmp__pierre2 group by siteniveau1, chapitreniveau1) as x; select count(*), sum(cnt) from (select count(*) as cnt from ref__at_lm_chapitres group by site, idchapitre1) as x; might help to give some estimation / idea ... Best, Stefan On December 1, 2014 6:16:11 PM CET, Pierre-Adrien Coustillas <pcoustillas(a)1g6.biz> wrote: >Hello, > > >With this query : >SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres >SUB ON SRC.siteniveau1=SUB.site AND >SRC.chapitreniveau1=SUB.idchapitre1; > > >SELECT count(*) FROM tmp__pierre2; >+------------+ >| L1 | >+============+ >| 1 061 964 254 | > >SELECT count(*) FROM ref__at_lm_chapitres; >+---------+ >| L1 | >+=========+ >| 1 331 867 | > > >after several hours of running the query is still not finished and I >stopped > > >how to find out where the problem? > > >thanks > > >Pierre > > >-- >1G6 >52 route de bischwiller >67300 Schiltigheim >Société de Services et de Formations en Logiciels Libres >http://1g6.biz >Tél : 06 64 63 70 35 > > > >------------------------------------------------------------------------ > >_______________________________________________ >users-list mailing list >users-list(a)monetdb.org >https://www.monetdb.org/mailman/listinfo/users-list

2 3

COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead
by Pierre-Adrien Coustillas 08 Dec '14

08 Dec '14

Hello, is this a bug or a feature I do not understand ? I have a script bash : ------------------------------------ pcoustillas@monetdb:~$ cat bug_commit.sh #!/bin/bash i=1 while [ $i -le 3 ] do create="CREATE TABLE tmp_schampy_odc__lemondeclient_$i AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA " echo $create mclient lemonde --statement="$create" & i=$(($i+1)) done ------------------------------------ I monetdb restarts before : service monetdb5-sql stop ; sleep 10 ; service monetdb5-sql start And i run script : pcoustillas@monetdb:~$ ./bug_commit.sh CREATE TABLE tmp_schampy_odc__lemondeclient_1 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_2 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_3 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA pcoustillas@monetdb:~$ operation successful operation successful COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead operation successful why is there a conflict while they are different tables that are created? I use the last version Database: MonetDB v11.19.7 (Oct2014-SP1) I have the same bug on : MonetDB v11.17.21 (Jan2014-SP3) Thank Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 4

Grant select on all tables
by Dennis Pallett 08 Dec '14

08 Dec '14

Hi all, I'm playing around with the authorisation/privileges functionality in MonetDB and I'm wondering if it's possible to grant a user the SELECT privilege on ALL tables (present and future tables) in a schema? At the moment I'm only able to grant this privilege on specific tables and I'd ideally like to create a user with full SELECT privileges and nothing more or less. Best regards, Dennis

1 1

SciQL-2 branch
by Dimitar Misev 04 Dec '14

04 Dec '14

Hi, I wanted to try SciQL so I got the SciQL-2 branch, but unfortunately it doesn't seem to be working. Following the tutorial at https://www.monetdb.org/Documentation/UserGuide/Tutorial I got stuck at $ mclient -u monetdb -d voc password: 3F000!CREATE PROCEDURE: no such schema 'rs' ParseException:SQLparser:3F000!CREATE PROCEDURE: no such schema 'rs' The default branch works ok. Any ideas on this problem, is there maybe a specific commit known to work? Thanks, Dimitar

4 9

Release Disk Space
by baraa Mohamad 03 Dec '14

03 Dec '14

Hello, I'm using MonetDB (on ubuntu) for doing some performance tests. I had added and deleted a lot of tables and right now I have no more free disk space since it seems that MonetDB does not clean up disk space after dropping tables. I would like to know the best way to clean up (as much as possible) disk space on MonetDB. Thank you for your help,Baraa

2 2

Error in mserver5 standalone
by Qing Guo 02 Dec '14

02 Dec '14

Hello, I was doing a performance profiling on MonetDB server so I run mserver5 in standalone. The command line I used is: mserver5 --dbpath=[path_to_your_db] --set mapi_open=false --set mapi_port=0 --set single-user=yes --set gdk_nr_threads=1 --set max_clients=1 --set sql_optimizer=default_pipe --set monet_daemon=no < [input] It worked for previous releases but not for the Oct2014. Instead, it gives the following message: "error initSQLreferences" The problem appears that "algebraRef" is not assigned a value before calling "initSQLreferences(void)". Any ideas to solve this problem? Much appreciate, thanks! Kevin

2 2

GROUP BY with JOIN produces unexpected results
by Jonas Goerlich 02 Dec '14

02 Dec '14

Hello, I am using MonetDB in a university project dealing with analysis of twitter data. "stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....) What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query: SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long; The result set however still contains rows with duplicate values for "lat" and "long", e.g. userid lat long ----------------------------- 19703 41 -74 19703 41 -74 If I remove the INNER JOIN part from the query, the grouping works as expected. To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...) Can anybody point me in the right direction? Am I doing something wrong here? Best regards and thanks in advance, Jonas

2 2

inner join very slow
by Pierre-Adrien Coustillas 01 Dec '14

01 Dec '14

Hello, With this query : SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres SUB ON SRC.siteniveau1=SUB.site AND SRC.chapitreniveau1=SUB.idchapitre1; SELECT count(*) FROM tmp__pierre2; +------------+ | L1 | +============+ | 1 061 964 254 | SELECT count(*) FROM ref__at_lm_chapitres; +---------+ | L1 | +=========+ | 1 331 867 | after several hours of running the query is still not finished and I stopped how to find out where the problem? thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

1 0