0

To everyone everyone we are using monetdb for storing considerably large amount of data, It works fine with complex queries but failed in performance to simple select statement when it comes to present data. HERE is a simple command RUN on table having 34 columns and it takes 58 seconds.

SELECT * FROM "mydb"."calls" WHERE id > -1 AND id<= 10000;

Now let to check if there is any issue with Query, bit modified and tested query given below, it returned response in 317ms.

SELECT COUNT(*) FROM (SELECT * FROM "mydb"."calls" WHERE id > -1 AND id<= 10000) cdrs;

Can Anyone suggest configuration settings to resolve this issue?

Database: MonetDB v11.20.0 (unreleased)
Database Shell : mclient
Python Lib: monetdb.sql

mserver5 --version
MonetDB 5 server v11.20.0 (64-bit, 64-bit oids, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 31.4GiB available memory, 24 available cpu cores
Libraries:
  libpcre: 8.36 2014-09-26 (compiled with 8.36)
  openssl: OpenSSL 1.0.1m 19 Mar 2015 (compiled with OpenSSL 1.0.1m 19 Mar 2015)
  libxml2: 2.9.2 (compiled with 2.9.2)
Compiled by: root@cdrs (x86_64-unknown-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe     -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
sharafjaffri
  • 2,134
  • 3
  • 30
  • 47
  • Given that the second query has to access as many rows as the first, I'd say that the problem is with the amount of data you are retrieving. 34 columns * 10,000 rows could be quite a lot. The first thing to do is to only select the required columns. – Turophile Aug 06 '15 at 05:54
  • All Columns are required for Presenting comparison report to user. More ever this data is still too low. Even same data from postgres takes less then a second. There must be a Configuration settings to resolve this Issue. – sharafjaffri Aug 06 '15 at 06:12
  • Are negative values for `id` actually stored? If not, you can dispense with that part of the `WHERE` clause. Is there an index on the `id` column? I'm not familiar with the monetdb, but does it allow you to store data in a particular physical order to optimize access? Also, are we measuring database performance here, or time to return the data into your python program - because it could be the python code that is the problem. – Turophile Aug 06 '15 at 06:26
  • Both Cases, So I have mentioned both Mclient and python lib. There is no index issue, neither processing delay as 2nd Query shows performance case, it not only process all data with filter(Where) and shows result fine. Only Difference is transfer of data. – sharafjaffri Aug 06 '15 at 06:47
  • is this on a slow network connection or something? – Hannes Mühleisen Aug 25 '15 at 05:46
  • No network connection involved, we are working on server. – sharafjaffri Aug 27 '15 at 07:43
  • 1
    If these two queries are run directly after each other, and if it is the first use of the table, then an imprints index may be constructed in the first round. Please, correctly measure hot/cold use of the database. Furthermore, the TRACE command could tell you if there are specific expensive operators. Overall, a SELECT *... is not column stores favor. – mkersten Sep 23 '15 at 13:04

0 Answers0