Re: [MonetDB-users] Bad SELECT performance after deletion of 1 record

10 May 2012

      Hi,

I have tested the same DB on Windows 7 and Ubuntu 10.04. The performance
after deletion of 1 record slows down on both OS. Tested version is Apr2012.

Here is an example of my table:
            CREATE TABLE t (
                s_id int DEFAULT NULL,
                time int DEFAULT NULL,
                a_id int DEFAULT NULL,
                x bigint DEFAULT NULL,
                y bigint DEFAULT NULL,
                z bigint DEFAULT NULL
            );

And here is my query:
            SELECT
                t.a_id, SUM(t.x), SUM(t.y), SUM(t.z)
            FROM
                (
                SELECT
                    MAX(time) AS max_time, a_id
                FROM
                    t
                WHERE
                    time <= 1345282581 AND
                    a_id IN (1, 5, 9, 12) AND
                    s_id = 3
                GROUP BY
                    a_id
                ) AS tmp
            INNER JOIN
                t ON 3 = t.s_id AND tmp.max_time = t.time AND tmp.a_id =
t.a_id
            GROUP BY
                t.attr_id
            ;

Notes: number of different a_id's is about 100, number of different s_id's
is about 50,000.

If table t consists of 15,000,000 records the execution of query above takes
about 50ms. Right after the deletion of 1 record the execution takes about
330ms (I run query 5 times and took the average value). So the difference is
considerable. What can I do to get back the former performance?

Any ideas?
Thanks for any advance.

suncpp wrote:
...
Hi everyone,
I had added about 30,000,000 records in one MonetDB table. The performance
of executing SELECT-queries was really great. But after the deletion of
only 1 record from the table, the performance had slowed down
considerably.
I have seen the topics here with alike problems but I have not found the
solution.
Could anyone help me to understand what the problem is?
-- 
View this message in context: http://old.nabble.com/Bad-SELECT-performance-after-deletion-of-1-record-tp33...
Sent from the monetdb-users mailing list archive at Nabble.com.