Recently encountered using Hsql query two tables, found that once the left join is very slow, separate check quickly, do not know why.
Then heard that hsql as long as the amount of data is slightly larger, and then join a bit slow, racked his brains to think of a day finally think of ways.
The split SQL will still use the left join, but the result set of the two tables is smaller in advance to the left join. Examples are as follows
Select p.parent_id,
P.id,
P.pid,
P.c_name,
P.path,
P.params,
P.p_type,
P.area_code,
P.appid,
SUM (DECODE (a.name, ' CPU ', convert (a.value,sql_double), NULL)) as CPU,
SUM (DECODE (a.name, ' mem ', convert (a.value,sql_double), 0)) as Mem
From P
Left Join a
On a.id = P.id
where p.id =?
Optimized SQL
SELECT * FROM (select P.parent_id,p.id, P.pid, P.c_name, P.path, P.params, P.p_type, P.area_code, P.appid, from IMS_NW.P Rocess p where p.id=?) Pp
Left join (SELECT d.sub_res_id,
SUM (DECODE (d.name, ' mem ', convert (d.value,sql_double), NULL)) as Mem,
SUM (DECODE (d.name, ' CPU ', convert (d.value,sql_double), 0)) as CPU
From D
WHERE d.id=?
GROUP by D.id) DD on pp.id=dd.id
Query speed from the original 4 seconds into 0.4 seconds, to meet the user's requirements. The main is to divide the query into two parts, each part of the query out, and then related, so the query speed is fast, we can learn from it.