How to improve query performance on a single node in MonetDB?
Hello list,
recently I came across MonetDB as a columnar database and I have some issues with getting it perform well. I posted my question on StackOverflow https://stackoverflow.com/questions/46558842/how-to-improve-query-performance-on-a-single-node-in-monetdb, idealy post your answers there but, mailing it to the list is fine as well.
Thank you
Tomas
TL;DR I've installed the latest (MonetDB 5 server v11v.27.5 "Jul2017-SP1") on Windows 2012 Server and I'm trying to query large table 1,4 billion rows in a reasonable time 2-3s. Is this even possible with MonetDB? What could I do to improve the performance? Detailed description of what I've done so far: Created table: CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT, a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8 TINYINT, a9 TINYINT); Loaded the data: COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\es_export\file.csv' USING DELIMITERS ',' NULL AS '' LOCKED; Run the query: SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22 AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007 AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2) AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14); It took *14m 52s* to get the result.
Hi Tomas,
this should not take so much time. I can answer at StackOverflow and/or here, but before doing that, is your data something that you can share with me so I can trace/profile this query?
thank you,
lefteris
On Wed, Oct 4, 2017 at 9:52 AM, Tomas Repik | Instarea tomas.repik@partnering.sk wrote:
Hello list,
recently I came across MonetDB as a columnar database and I have some issues with getting it perform well. I posted my question on StackOverflow, idealy post your answers there but, mailing it to the list is fine as well.
Thank you
Tomas
TL;DR I've installed the latest (MonetDB 5 server v11v.27.5 "Jul2017-SP1") on Windows 2012 Server and I'm trying to query large table 1,4 billion rows in a reasonable time 2-3s. Is this even possible with MonetDB? What could I do to improve the performance? Detailed description of what I've done so far: Created table: CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT, a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8 TINYINT, a9 TINYINT); Loaded the data: COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\es_export\file.csv' USING DELIMITERS ',' NULL AS '' LOCKED; Run the query: SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22 AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007 AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2) AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14); It took 14m 52s to get the result.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Tomas, et al.,
please see StackOverflow [1] for some comments of mine.
Best, Stefan
[1] https://stackoverflow.com/questions/46558842/how-to-improve-query-performanc...
----- On Oct 4, 2017, at 10:24 AM, Lefteris lsidir@gmail.com wrote:
Hi Tomas,
this should not take so much time. I can answer at StackOverflow and/or here, but before doing that, is your data something that you can share with me so I can trace/profile this query?
thank you,
lefteris
On Wed, Oct 4, 2017 at 9:52 AM, Tomas Repik | Instarea tomas.repik@partnering.sk wrote:
Hello list,
recently I came across MonetDB as a columnar database and I have some issues with getting it perform well. I posted my question on StackOverflow, idealy post your answers there but, mailing it to the list is fine as well.
Thank you
Tomas
TL;DR I've installed the latest (MonetDB 5 server v11v.27.5 "Jul2017-SP1") on Windows 2012 Server and I'm trying to query large table 1,4 billion rows in a reasonable time 2-3s. Is this even possible with MonetDB? What could I do to improve the performance? Detailed description of what I've done so far: Created table: CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT, a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8 TINYINT, a9 TINYINT); Loaded the data: COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\es_export\file.csv' USING DELIMITERS ',' NULL AS '' LOCKED; Run the query: SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22 AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007 AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2) AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14); It took 14m 52s to get the result.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Lefteris -
Stefan Manegold -
Tomas Repik | Instarea