db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From TXVanguard <brett.den...@lmco.com>
Subject Slow inserts in tables
Date Wed, 01 Aug 2012 21:41:24 GMT

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.


Mime
View raw message