The LEFT JOIN query is very slow when the volume of hsql data is large

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.