Re: problem with monetdb functionality

Dear Shajon, As Stefan mentioned, the context of your execution state is not specified enough to draw any conclusion. Machine load (including open browsers), cold/hot system file caches, mapped database files,...many factors can affect the performance and which are beyond the DBMS control. To explain more about what to expect, you should report the exact version of MonetDB, your OS parameters(RAM) and run the same query multiple times directly after each other. This under the assumption that you have stopped any concurrent work, e.g. the system cpu load was low. Furthermore, the index statement in SQL is accepted but does not lead to creation of an index. That is decided at runtime, when an index needed and effective. regards, Martin On 10/17/12 11:39 AM, Shajon wrote:

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 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

_______________________________________________ Info mailing list Info@monetdb.org http://mail.monetdb.org/mailman/listinfo/info

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list