db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From TXVanguard <brett.den...@lmco.com>
Subject Speeding up hideous insert
Date Tue, 03 Jul 2012 22:20:00 GMT

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.


Mime
View raw message