2013/9/7 Cecil Westerhof <[email protected]>
>
> At the moment I am using a MySQL database where I have the following
> definitions:
>
> CREATE TABLE IF NOT EXISTS userProcesses (
> `id` int(11) NOT NULL auto_increment,
> `dateTime` varchar(25) NOT NULL,
> `user` varchar(25) NOT NULL,
> `noOfProcesses` varchar(25) NOT NULL,
>
> PRIMARY KEY (`id`),
> UNIQUE KEY `dateUser` (`dateTime`, `user`),
> );
>
> CREATE OR REPLACE VIEW totalProcesses AS
> SELECT
> dateTime,
> SUM(noOfProcesses) AS total
> FROM
> userProcesses
> GROUP BY
> dateTime
> ;
>
> The problem is that in the view the index from userProcesses is not used
> because the GROUP BY. (I now do not use the view, makes my query about 40
> times faster.) Would H2 not have this problem? That would be a good excuse
> to migrate from MySQL to H2.
>
In H2 I can use the view. It is a little less fast as without the view, but
it makes the code more readable. The strange thing is that MySQL is a lot
faster (when not using the view) and I understood that H2 should be faster
as MySQL.
The following query takes 4.5 seconds in MySQL:
SELECT
SUBSTR(dateTime, 1, 10) AS thisDate,
MIN(total) AS min,
MAX(total) AS max,
ROUND(AVG(total), 2) AS avg,
COUNT(*) AS total
FROM (
SELECT
dateTime,
SUM(noOfProcesses) AS total
FROM
userProcesses
WHERE
SUBSTR(dateTime, 1, 10) >= '2013-09-01' AND
SUBSTR(dateTime, 1, 10) <= '2013-09-06'
GROUP BY
dateTime
) totalProcesses
GROUP BY
thisDate
ORDER BY
thisDate
;
In H2 I made the following table and view:
CREATE TABLE IF NOT EXISTS userProcesses (
`dateTime` varchar(25) NOT NULL,
`user` varchar(25) NOT NULL,
`noOfProcesses` int(11) NOT NULL,
PRIMARY KEY (`dateTime`, `user`)
);
CREATE OR REPLACE VIEW totalProcesses AS
SELECT
dateTime,
SUM(noOfProcesses) AS total
FROM
userProcesses
GROUP BY
dateTime
;
I removed id there it is not needed and changed noOfProcesses to an int.
But that should not make things slower I would think.
I imported the MySQL data into H2.
The query without the view takes 18,5 seconds:
SELECT
SUBSTR(dateTime, 1, 10) AS thisDate,
MIN(total) AS min,
MAX(total) AS max,
ROUND(AVG(total), 2) AS avg,
COUNT(*) AS total
FROM (
SELECT
dateTime,
SUM(noOfProcesses) AS total
FROM
userProcesses
WHERE
SUBSTR(dateTime, 1, 10) >= '2013-09-01' AND
SUBSTR(dateTime, 1, 10) <= '2013-09-06'
GROUP BY
dateTime
) totalProcesses
GROUP BY
thisDate
ORDER BY
thisDate
;
The query with the view takes 38 seconds:
SELECT
SUBSTR(dateTime, 1, 10) AS thisDate,
MIN(total) AS min,
MAX(total) AS max,
ROUND(AVG(total), 2) AS avg,
COUNT(*) AS total
FROM totalProcesses
WHERE
SUBSTR(dateTime, 1, 10) >= '2013-09-01' AND
SUBSTR(dateTime, 1, 10) <= '2013-09-06'
GROUP BY
thisDate
ORDER BY
thisDate
;
Am I doing something wrong? The table contains almost 2,5 million records.
--
Cecil Westerhof
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
