- users-list - monetdb.org

script-friendly prepare statement
by Roberto Cornacchia 13 Jul '17

13 Jul '17

Hi there, I'm trying to script the execution of (generated) prepared queries. It does not look possible, because the identifier to use for the EXEC statement is returned dynamically by the PREPARE statement: sql>prepare select 1; execute prepared statement using: EXEC 73(...) This can obviously not be scripted. If I tried to parse the output of the prepare statement to retrieve the identifier, this would split PREPARE and EXEC in two different mclient sessions, losing the prepared statement. As far as I know, there is at the moment no syntax available for providing the identifier, something like: sql>prepare myid as select 1; (that wouldn't be a bad idea, would it?) Besides syntax extensions, I was wondering if there is any trick I could use at runtime with the current syntax to retrieve that identifier. Like, is it stored in some system table? Is it available as any undocumented variable? I could not find anything like that, but I might have missed it. Cheers, Roberto

3 4

adding an "insert into" completely changes output from explain
by Anderson, David B 13 Jul '17

13 Jul '17

All, Is it expected behavior if adding "insert into xxxx" at the beginning of a sql query completely alters the output of explain? Without the "insert into", explain returns 1490 tuples, but when I add it, explain only returns 88. I was expecting that the number would go up, i.e. that the original plan would be wrapped in some additional commands which would implement the "insert into" part. Here is my sql: explain insert into aggr select 'CHANNEL', s.product, s.coupon, s.productionyear, s.tpoflag, sum(1), sum(d.scheduled)/1000000, sum(d.realized )/1000000, round(100*(1-power(sum(d.realized)/sum(d.scheduled),12)),3) as cpr from stt s, dyn d where s.loanseqnum = d.loanseqnum group by s.product, s.coupon, s.productionyear, s.tpoflag having sum(d.scheduled) > 0 ;

2 1

Extremely slow order by queries
by Manish gupta 13 Jul '17

13 Jul '17

Hi, I have a scenario where data size is ~3M rows. My query is select C1, C2, C3 from T1 group by C1,C2,C3 This returns result in 30 Sec ( number of groups around 1 M ) While, if I modify the query to select C1, C2, C3 from T1 group by C1,C2,C3 Order by C1, C2 on the same dataset, it takes ~ 1 hour to get result. The platforms I tried is both widows 64 bit and Linux 64 bit, with latest monet installed. Both the machines have 4 cores. And the behavior is same on both the machines. I am really surprised to see overhead of second query. One more observation is that this query occupies only single core, while monet is free to utilize 4 cores. Regards, Manish

2 1

Mserver returns with "memory exhausted in" error if query size exceeds 1.2MB
by Sharma, Sreejith 08 Jul '17

08 Jul '17

Hi, I have encountered scenario where SQL generated exceeds 3mb and it's failing in mserver with an error "memory exhausted in select .......". On further checks I have noticed that the SQL will go through only if SQL size is less than 1.2MB. Has anyone encountered this before? Or is anyone aware of any SQL size limitation in MonetDB? Please help. Regards, Sreejith

1 0

access MapiHdl by using qtag value from C API
by Anton Kravchenko 26 Jun '17

26 Jun '17

Is there a way to access MapiHdl of the query by using its qtag value?

1 0

Re: MonetDB taking lot of storage space
by Stefan Manegold 17 Jun '17

17 Jun '17

Typo in formula:does * columns * (average) width per columnmust readrows * columns * (average) width per column, -------- Original message --------From: Stefan Manegold <stefan.manegold(a)cwi.nl> Date: 6/17/17 12:33 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hi Amir, May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore) I.e. 1 cr = 10 million = 10^7 = 10,000,000 ? The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns. Your 196 GB is far from that. Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest? Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hai, MonetDB doesn’t provide any user definable compressions. What do you mean with “5cr rows”? Compressed Rows? 198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column). Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages. Did you happen to have put larges BLOBs or CLOBs in some columns? Which MonetDB version and OS do you use? Regards, Jennie > On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote: > > Hi, > > i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ?? > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

Re: MonetDB taking lot of storage space
by Stefan Manegold 17 Jun '17

17 Jun '17

Hi Amir, May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore) I.e. 1 cr = 10 million = 10^7 = 10,000,000 ? The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns. Your 196 GB is far from that. Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest? Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hai, MonetDB doesn’t provide any user definable compressions. What do you mean with “5cr rows”? Compressed Rows? 198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column). Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages. Did you happen to have put larges BLOBs or CLOBs in some columns? Which MonetDB version and OS do you use? Regards, Jennie > On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote: > > Hi, > > i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ?? > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

MonetDB taking lot of storage space
by Amit Ambekar 17 Jun '17

17 Jun '17

Hi, i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ??

2 1

SQLsetTrace assumes in-place append
by Roberto Cornacchia 14 Jun '17

14 Jun '17

Background: I'm working on a side-effect-free branch of MonetDB. No in-place operations allowed. With bat.append() already changed this way, everything seemed to work so far, except TRACE. Even TRACE SELECT 1 would fail. I looked at it and the reason is that SQLsetTrace assumed in-place appends, thus first adding new empty bats to resultset, then filling in those bats. Everywhere else this is not assumed. The return bat of append is always created and used, even if it is the same of the first operand. The diff in attachment makes it work for both branches and is in my opinion more correct and consistent with the rest of the code. Best, Roberto

1 0

Announcement: New Dec2016-SP5 Bugfix release of MonetDB suite
by Sjoerd Mullender 12 Jun '17

12 Jun '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP5 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/>. Dec 2016-SP5 bugfix release Bug Fixes * 6290: Crash (and assertion failure) with a correlated subquery with NOT IN in the select-list * 6291: crash if executes a function with sample operator * 6294: Sqlitelogictest crash * 6296: Another sqllitelogictest crash :( * 6297: 7th sqllitelogictest crash :( * 6300: Protect against missing BATs (sqlsmith) * 6314: Lateral crash report (sqlsmith) * 6315: Exist operator on type bigint missing (sqlsmith) * 6316: Coalesc and limit error (sqlsmith) * 6317: Two-column integer aggregation extremely slow * 6318: Daemon crashes if .merovignian_properties for a database contains more than 42 entries * 6320: The daemon ignores all but the last entry in the .merovignian_properties file when spawning mserver5

1 0