When I create a table like this:
CREATE TABLE table1 (C1 INTEGER, C2 INTEGER, C3 INTEGER, C4 INTEGER, C5
INTEGER, C6 INTEGER)
and then run this INSERT statement:
INSERT INTO table1 (C1,C2,C3,C4,C5,C6)
SELECT R.ASM, S.NID, S.CID, S.IN, D.NID, EMN.ASM
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.DIN) AND (D.CID=R.DCID) )
LEFT JOIN
(SELECT DISTINCT ASM FROM table4 WHERE Ex = True) AS EMN
ON R.ASM = EMN.ASM
the SELECT query produces 33,000 rows and the INSERT takes over two minutes
to run.
I have run the query standalone using
statement.executeQuery("SELECT R.ASM, S.NID, S.CID, S.IN, D.NID, EMN.ASM
FROM ...")
(the "..." stands for the rest of the query--I'm not using literal dots),
which only takes a couple seconds to run, so I don't think the problem is
the query.
I have tried inserting into the table with a PreparedStatement, setting
AutoCommit to false, using batch mode, etc., etc., etc., and I can't seem to
make the inserts go any faster. I get similar poor performance any time I
have other tables with several thousand records. I have also played with
pageCacheSize, pageSize, durability=test, and various JVM settings such as
-mx, -Xms, -Xmx, -Xss, et al.
Does anyone have any suggestions on how to speed up these inserts?
--
View this message in context:
http://old.nabble.com/Slow-inserts-in-tables-tp34242943p34242943.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
