Hello Martin thank you for the quick answer,

I will try to explain you better my situation. I have a big table that will grow around 20-30M rows a day (just inserts, i will create a new CSV and load each 20-30 minutes), then i have a small table that right now is 100K and probably will be 200K in 1-2 years, this one have a lot of updates. The big table is very easy to shard by date, is for reports that all the time user have to choose a > date and < date.

The final reports are made from a big table a join with small table, and have around 100-500 rows (that is sometimes presented as table sometimes as graph).

But because i will shard big table different servers is not easy to keep the small table in all servers, and it have a lot of updates, and i read that updates on monetdb are not really updates, just another structure saying that was updated.

So the idea is to have a PHP HTTP API that will receive a query will query the shards monetdb needed but can bring much more than 100-500 rows, can be up to 10K rows, because is not joined with the small table yet and 1 group by that is one column is just on the small table, is difficult to put that column is the big table to avoid join because needs a lot of updates.

Then it will be done the join in the appplication, or using a table on mysql a memory table writes will be very fast.

The times i gave below is all local, so some networking time will be added, but so slow to get 10K of results is really a show stopper.

2012/11/15 Martin Kersten <martin@monetdb.org>
Hello Eduardo

The core problem might be your query itself. It is a row-store query ;)

It is the extreme case encountered in a column store. There the benefits come
from using (accessing) a limited number of columns or to perform aggregations.

Consider queries such as "SELECT grp, sum(attr) FROM summary_sent WHERE predicate GROUP BY grp"
or "SELECT attr1, attr2 FROM summary_sent WHERE predicate LIMIT 50000;"

Your observation about php client performance most likely has little to do
with the PHP interface to MonetDB. To check it, split the timing in the
actual query execution part in the server and the retrieval loop.
(Also be aware of cold/hot query processing)
Although, I am not sure about the buffering scheme deployed in our PHP code,
which may require some updates.
In general, sending twice the amount of data over the wire will take twice
the amount of time to process.

regards, Martin


On 11/15/12 5:53 PM, Eduardo Oliveira wrote:
Hello,

I'm finding php client too slow returning big result sets. I'm using php client i i downloaded from http://dev.monetdb.org/downloads/sources/Latest/  MonetDB-11.13.3.tar.bz2

Some benchmark down, anyway of get the results faster?

LIMIT 50K

[root@monetdb examples]# php simple_query.php
*9*


[root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 50000' > res.txt
password:

real    0m4.566s <- already counting with some time to password input
user    0m1.272s
sys    0m0.132s

----

LIMIT 100K

[root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 100000' > res.txt
password:

*real    0m7.184s*

user    0m2.545s
sys    0m0.266s

[root@monetdb examples]# php simple_query.php
*18*


----------

Code:

[root@monetdb examples]# cat simple_query.php
<?php
require '../lib/php_monetdb.php';

$db = monetdb_connect("sql", '127.0.0.1', 50000, 'adctest', 'adctest', 'adctest') or trigger_error(monetdb_last_error());

$start_query_monetdb = time();
$res = monetdb_query($db, monetdb_escape_string('SELECT * FROM summary_sent LIMIT 100000')) or trigger_error(monetdb_last_error());

while ( $row = monetdb_fetch_object($res) )
{

}

$time = time() - $start_query_monetdb;
echo $time . "\n";

/* Free the result set */
monetdb_free_result($res);

/* Disconnect from the database */
if (monetdb_connected($db)) {
     monetdb_disconnect($db);
}

--
*Eduardo Oliveira
*
/IT/
***Email:* eduardo.oliveira@adclick.pt <mailto:nuno.morais@adclick.pt>
*Web: *www.adclickint.com <http://www.adclickint.com/>
**<http://www.adclickint.com/>

*
*<http://www.adclickint.com/>


<http://www.adclickint.com/>


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





--
Eduardo Oliveira
IT
Email: eduardo.oliveira@adclick.pt
Web: www.adclickint.com