hi,
h2 version 1.3.172
linux/android
got slow execution of the following query
select d1.id ,ifnull(translation,name) from d1 d
left join d2 on source=name and category='desc'
where archived=0 order by sort_order,d1.id;
d1
id bigint
name varchar(50)
archived smallint
d2
id bigint
category varchar(60)
source varchar(100)
translation varchar(500)
d1xd2 450x1500 rows
execution takes near 4 sec.
if only this query is run repetitive then first time it takes 4 sec and
after less then 200-300ms.
so quite clear cache is used, but in app cache is replaced frequently and i
have same 4 sec
for execution. attempt to play with cache size was unsuccessful.
other issue . h2 doesn't want to use indexes, created few of them single
column and
combined 2, 3 columns (btw is it supported and used?) anyway h2 perform
tablescan.
analyze
select d1.id ,ifnull(translation,name) from d1 d
left join d2 on source=name and category='desc'
where archived=0 order by sort_order,d1.id;
SELECT
d1."id",
IFNULL("translation", "name")
FROM PUBLIC."d1" "d"
/* PUBLIC."districts".tableScan */
/* WHERE "archived" = 0
*/
/* scanCount: 450 */
LEFT OUTER JOIN PUBLIC."d2"
/* PUBLIC.SOURCE: "source" = "name" */
ON ("category" = 'desc')
AND ("source" = "name")
/* scanCount: 492 */
WHERE "archived" = 0
ORDER BY ="sort_order", 1
any suggestion here?
--
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.
