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