Speeding up hideous insert

[TXVanguard <br...@lmco.com>]

I have (what is to me, at least) a hideous SQL statement that takes forever
to run:

INSERT INTO Table1(ASM, SNID, DNID, SC)
SELECT ASM, SNID, DNID, Count(SIID)
FROM (
    SELECT DISTINCT R.ASM, S.NID AS SNID, S.CID AS SCID, S.IN AS SIID, D.NID
AS DNID
    FROM
        (Table2 AS D INNER JOIN (Table2 AS S INNER JOIN Table3 AS R ON
(S.IN=R.SIN) AND (S.CID=R.SCID)) ON (D.IN=R.DIN2) AND (D.CID=R.DCID))
        LEFT JOIN
        ( SELECT DISTINCT ASM FROM Table4 WHERE EX = True) AS EMN ON R.ASM =
EMN.ASM
    WHERE (((S.NID<40 OR S.NID>42) AND (D.NID<40 OR D.NID>42)) OR
(R.ASM<10000 OR R.ASM>=40000)) AND EMN.ASM IS NULL
    ) AS T
GROUP BY ASM, SNID, DNID

Don't worry too much about the details: just look at the SELECT DISTINCT,
the WHERE, the GROUP BY, etc.  What are some general strategies for speeding
up this kind of statement?  I've tried indexing everything in sight,
compressing tables, etc, but nothing seems to work.

-- 
View this message in context: http://old.nabble.com/Speeding-up-hideous-insert-tp34110428p34110428.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Speeding up hideous insert

[TXVanguard <br...@lmco.com>]

Thanks.  Good info for me to chew on.

-- 
View this message in context: http://old.nabble.com/Speeding-up-hideous-insert-tp34110428p34136827.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Speeding up hideous insert

[Bryan Pendleton <bp...@gmail.com>]

On 07/03/2012 03:20 PM, TXVanguard wrote:
> Don't worry too much about the details: just look at the SELECT DISTINCT,
> the WHERE, the GROUP BY, etc.  What are some general strategies for speeding
> up this kind of statement?

What the community knows about this sort of thing is mostly collected here:
http://db.apache.org/derby/docs/10.9/tuning/

A good place to start is to (a) break down your big query into smaller queries,
so you can analyze each part separately, and (b) learn how to read Derby's
query execution plan output, which you can read about here:
http://db.apache.org/derby/docs/10.9/tuning/ctundepth853133.html

thanks,

bryan