I'm programming a tool which for test purposes fills a database with
dummy data. I use a JDBC driver. I aim to insert about 1.000.000 datasets
but right now it's taking too long altough my routine is very small.
for (int i= 0 ; i < numberOfJoins ; i ++)
RandomGUID myGUID = new RandomGUID();
statement.setString( 1, myGUID.toString());
statement.setString( 2 , (String)parentkey.get(i));
statement.setString( 3 , (String)objectkeys.get(i) );
What I'm doing is create a GUI and then retrieve to other GUIDS from two
very large LinkedLists.
The Query looks like this
"Insert into " + tableName + " ( PRIMARYKEY, PARENTKEY, OBJECTKEY ) VALUES ( ? , ? , ? ) " ;
Right now it's taking me for 100.000 datasets about 6.5 minutes. For 50.000
it's only 2.2 minutes. Does anyone have any idea how to improve on this?
Originally posted by Pete Neu:
The point is I don't have any indexes in the table.
very first I would suggest that you test how much time your database is taking while inserting 100000 record without any java code.
create a temporary table and insert 100000 rows into table something like
insert into myTempTable select * from My table
and insert 100000 rows in this operation, here you can find out exact time taken by database to perform insert operation.
If insertion time is high at database end then first look at database tuning.
because we can not rule out possibility of what Jeroen said
by the way which database are you using
if data insertion time is very less then problem is somewhere else.
[ March 23, 2005: Message edited by: Shailesh Chandra ]
1) while doing inserts, is the database doing any other operations?
2) Is the server where the database is hosted doing any heavy load operations? (if db is also in local, you can skip this)
3) Is the client which is running the java program doing any heavy duty operations?
4) also check the network speed and see if there are any hassles with network.(if db is also in local, you can skip this)
These are worth checking. Also you can increase the batch update from 1000 to say 5000 and then check the performance.
installed on the same machine. There is no
I can't change anything in the database
because I can't tell if the user is going
to use the same instance. The only option is to
improve on anything related to the Java Code.
The database I have to use is MaxDB.
Originally posted by Pete Neu:
I'm just trying to say that I can't configure the database because the potential user will install his own database.
Whatever. Couldn't you guide the user through DB design/creation. In your case I think you probably need to handover some document/diagrams, like ERD or SQL Scripts etc., for DB creation.
(Nice point Sripathy, really appreciated. I was thinking of select.)
[Edited after sripathys reply]
Moreover, having no indexes is likely results in bad performance upon retrieval. I am not talking about full table scans.
[ March 24, 2005: Message edited by: Adeel Ansari ]
Moreover, having no indexes is likely results in bad performance.
If there are 100000 rows to be inserted to a table and the table has index for a column, then the insert will be slow.(I cant really say how slow, but I am sure it will be slow since it has to create index and update to the index file for each row)
So how can having no index result in bad performance?