Hello Martin,
Thank you for your reply.
Yes, we’ve noticed the difference in time between hot and cold runs. We are only talking about hot runs. BTW, forgot to mention we created the database on a RAMDISK for improved performance.
Query time measurements are accurate, I mean, it’s observable when a query takes 50ms and 500ms, the time it takes to retrieve the results is noticeable since you have to wait to see the result, normally the queries should be fast enough so that such things wouldn’t matter and results would be delivered instanously. We are also talking about empty result sets, cause we know these take the longest.
Also the time varies with the number of conditions, for instance take the query SELECT id FROM table WHERE a IN (1,2,3,4) AND b IN (4,5,6,) AND c IN (2,5,6) ...... AND i = 5 LIMIT 1 etc. With only one IN condition the query time is 10-20 ms, add 10 IN conditions and it goes to 100 ms or more. I suppose this is normal given the LIMIT 1 condition.
Also these timing are with sys.querylog_disable(); with it enabled it will get to 400ms .
As mentioned earlier, from what I read MonetDB shines at analytic queries, while we are basically trying to retrieve one id from a large haystack based on a certain parameters. But still I wanted to give my feedback on the matter.
All the best, Silviu From: Martin Kersten Sent: sâmbătă, 26 noiembrie 2016 23:00 To: Communication channel for MonetDB users Subject: Re: MonetDB and IN search
Hi
You should understand the difference between hot and cold runs. Hash indices are automatically created the first time you run such a query. Furthermore, your intermediates are still large.
For details on where the time is spent in the query you can look at advice in https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryTiming
regards, Martin
On 26/11/2016 20:28, blacky_2k2@yahoo.com wrote:
Hello,
We’ve been battling for some time with what is fundamentally a simple query.
We have a databases with about 15 columns which store int and float values.
The query searches in each column with an IN condition , where the possible test values in the IN condition may vary from a 1 to 200 - where these 200 conditions are all the possible values of that column.
So normally a query would look something like:
SELECT id FROM table WHERE a IN (1,2,3,4) AND b IN (4,5,6,) AND c IN (2,5,6) ...... AND i = 5 etc.
The database has about 2 mil rows but will grow to potentially 10 mil rows.
A query similar to the one above takes about 80-100 ms. With about 50 test values for each IN.
So far, the best results we got with MariaDB and proper indexing using Engine Memory where the above query takes about 2ms.
We suspect MonetDB is not optimised for such queries, but since we don’t know much about its workings can you please confirm this? Otherwise, is there a way to improve the above query? Say using multiple JOINs instead of IN ?
By the way, we are experimenting with MonetDB out of curiosity, we heard a lot of good things about it.
Thank you very much,
Silviu
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list