Huge string makes Java class irresponsive in Netbeans

Question

I have a very big SQL query with the following structure in a Java String.

BEGIN;
 couple of thousand of INSERT INTO
END;

The class that holds this String is nearly not responsive in Netbeans IDE due to the file size. What is the best way to store, load and run such big queries?

Answer 1

that is not a big query, it is a lot of little queries. There isn't much you're going to be able to do about it unless you try a different approach. Write out a file and use a bulk load utility to get the data into the database might be your best bet.

Answer 2

Assuming you want to run the command from within Java (not use the db's bulk load binaries), you can store the SQL in a separate (resource) file, read the file into a string form Java, and then execute it against the database. One other benefit (besides the fact that it's no longer in the Java file) is that you can name the resource file in such a way as to have your IDE fontify it as SQL for you (assuming your IDE does such).

Answer 3

I would highly recommend using an ETL tool to accomplish this. They have bulk loader components that will make your life much easier. I have used DataStage and Pentaho (Kettle) in the past for things like this. Kettle is open source. You can download it from here. For example here is a brief info on the oracle bulk loader.

Answer 4

You may find the following is fast (on the assumption that all the inserts are into the same table)

BEGIN
   INSERT INTO <my_table>
             SELECT <blah1>
   UNION ALL SELECT <blah2>
   UNION ALL SELECT <blah3>
   ...
   UNION ALL SELECT <blah1000>
END

This is one big insert instead of 1000 small inserts. This reduces many of the overheads and often runs significantly faster.

As a pain, however, this also means the entry into the transaction log is bigger. When you get to millions of inserts, you might find it becomes faster to do it in batches.

• UNION to combine many small operations in to one.

• Several smaller operations to have less impact on the transaction log.
  [Probably talking tens/hundred of thousand of records, or more]

Answer 5

If I understand your question correctly you have a very large Java source file containing of a string (or many) with thousands of small insert statements? In that case as a first step you should consider externalizing those statements to a separate text file. In your Java code then use a template engine to load the file the statements. However, if you want to run many small inserts efficiently you may want to consider using a batch loader or etl tool (Kettle or Apache Camel come to mind).