Thanks Stefan for your benevolent response.
It's true the time consumption gradually decreased on
subsequent query on same table since it takes the caching benefits. however I have
made an experiment: I executed a select query with multiple selection (which
have some aggregated function & where clause) which took 113.656ms. And later I ran
all the selection separately with same conditions for same task and took total 490.53ms,
which is almost five times of consolidated query. Here those output
The table has 26962306 records and index also.
Consolidated Query at beginning
===============================
sql>select
sum(vol),count(1),avg(price),max(price),min(price) from ….
+-----------+--------+--------------------------+--------------+--------------+
|
L1 | L2 | L3 | L4 | L5 |
+===========+========+==========================+==============+==============+
|
116474353 | 300020 | 140.91221848785318 | 141.660004 | 139.630005 |
+-----------+--------+--------------------------+--------------+--------------+
1 tuple
(113.656ms)
Individual Query
=============
sql>select
sum(vol) from …..
;
+-----------+
|
L1 |
+===========+
|
116474353 |
+-----------+
1 tuple
(116.877ms)
sql>select
count(1) from …
;
+--------+
|
L1 |
+========+
| 300020
|
+--------+
1 tuple
(95.246ms)
sql>select
avg(price) from ….
+--------------------------+
|
L1 |
+==========================+
|
140.91221848785318 |
+--------------------------+
1 tuple
(88.853ms)
sql>select
max(price) from ….
+-----------------+
|
L1 |
+=================+
|
141.660004 |
+-----------------+
1 tuple
(88.083ms)
sql>select
min(price) from …..
+-----------------+
|
L1 |
+=================+
|
139.630005 |
+-----------------+
1 tuple
(101.471ms)
Consolidated Query after individual query
============================================
sql>select
sum(vol),count(1),avg(price),max(price),min(price) from…
+-----------+--------+--------------------------+--------------+--------------+
|
L1 | L2 | L3 | L4 | L5 |
+===========+========+==========================+==============+==============+
|
116474353 | 300020 | 140.91221848785318 | 141.660004 | 139.630005 |
+-----------+--------+--------------------------+--------------+--------------+
1 tuple
(103.222ms)
Total Records
===========
sql>select count(1) from …
+----------+
| L1 |
+==========+
| 26962306 |
+----------+
1 tuple (8.041ms)
sql>
Respectfully,
Shajon
-----Original Message-----
From: Stefan Manegold [mailto:Stefan.Manegold@cwi.nl]
Sent: Wednesday, October 17, 2012 2:16 PM
To: Shajon
Cc: users-list@monetdb.org; info@monetdb.com
Subject: RE: problem with monetdb functionality
Shajon,
1.
Do you have any evidence in terms of experiments that
shows that MonetDB is slow(er) due to not allowing to set multiple variables in
a select clause?
Can you give example queries and their performance?
2.
From you rather short description, we cannot say much
about this. Beware that your systems state may have a significant influence on
performance, e.g.,
- for point (single value select) predicates, the first
query builds a has table, while subsequent queries benefit from this hash
table;
- the first query needs to load the data from disk, while
subsequent queries might benefit from (parts of) the data already/still being
in memory; in MonetDB itself while the server is running, or in the OS's
filesystem cache even after a server restart;
- etc.
Best,
Stefan
Shajon <alam@finEngine.com> wrote:
From: Shajon [mailto:alam@finEngine.com]
Sent: Tuesday, October 09, 2012 1:47 PM
To: 'users-list@monetdb.org'
Subject: FW: problem with monetdb functionality
From: Shajon [mailto:alam@finEngine.com]
Sent: Tuesday, October 09, 2012 12:49 PM
To: 'info@monetdb.com'
Subject: problem with monetdb functionality
Hi,
I am using monetdb to process millions of records. now I
am getting two
problem
1. I could not set multiple variable in select
query in a function or
procedure. For example: select @var1=col1, @var2=col2
from table_name. as
like sql server or Sybase database. If I could do ,it
would reduce
considerable amount of time for some function execution.
So do I need to
write MAL or any other way?
2. Sometimes same query takes different time in
different moment. For
example a query at the starting time of server takes 2ms
and 10 hours later
it takes 14s. if I close & restart server & client
its ok.
Can anyone suggest me what can I do?
Thanks
Shajon