Hi,
I am trying to improve performance of SQL queries on a H2 database.
One query remains slow though: a select with a IN clause on a composite
primary key.
Testscript to create/fill/select:
> DROP TABLE IF EXISTS TestTable;
>
> CREATE TABLE IF NOT EXISTS TestTable (
> FirstCol VARCHAR(2048) NOT NULL ,
> SecCol VARCHAR(2048) NOT NULL ,
> PRIMARY KEY (FirstCol, SecCol));
>
> INSERT INTO TestTable select CONCAT('VAL_X_',X) AS XX, 'VAL_Y_2000' AS YY
> from SYSTEM_RANGE(1, 2000);
> INSERT INTO TestTable select CONCAT('VAL_X_',X) AS XX, 'VAL_Y_4000' AS YY
> from SYSTEM_RANGE(2001, 4000);
> INSERT INTO TestTable select CONCAT('VAL_X_',X) AS XX, 'VAL_Y_6000' AS YY
> from SYSTEM_RANGE(4001, 6000);
> INSERT INTO TestTable select 'VAL_X_400' AS XX, CONCAT('VAL_Y_',X) AS YY
> from SYSTEM_RANGE(1, 1980);
>
> explain analyze SELECT * FROM TestTable where FirstCol = 'VAL_X_400' AND
> SecCol IN ('VAL_Y_2000', 'VAL_Y_3000')
>
The query plan is:
PLAN
<http://127.0.1.1:8082/query.do?jsessionid=ea09b5232afea7d0e97720dfdc206a44#>
SELECT
TESTTABLE.FIRSTCOL,
TESTTABLE.SECCOL
FROM PUBLIC.TESTTABLE
/* PUBLIC.PRIMARY_KEY_60E: SECCOL IN('VAL_Y_2000', 'VAL_Y_3000')
AND FIRSTCOL = 'VAL_X_400'
*/
/* scanCount: 1982 */
WHERE (SECCOL IN('VAL_Y_2000', 'VAL_Y_3000'))
AND (FIRSTCOL = 'VAL_X_400')
Notice the large scanCount.
Experiments:
- when changing the test query to "SELECT * FROM TestTable where
FirstCol = 'VAL_X_400' AND SecCol IN ('VAL_Y_2000')", the scanCount drops
to 2.
- when changing the test query to "SELECT * FROM TestTable where SecCol
IN ('VAL_Y_2000', 'VAL_Y_3000')" the scanCount drops to 1000.
Thinking about this query, the scanCount should be 2, because I can rewrite
the query to the following form which should use the primary key index:
SELECT * FROM TestTable where (FirstCol = 'VAL_X_400' AND SecCol =
'VAL_Y_2000') OR (FirstCol = 'VAL_X_400' AND SecCol = 'VAL_Y_3000')
But in that case the scanCount increases to a whopping 7981.
Can anyone please point out what I am doing wrong here?
Thanks, Jan
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
