File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Performance of a 10000-word prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Performance of a 10000-word prepared statement" Watch "Performance of a 10000-word prepared statement" New topic

Performance of a 10000-word prepared statement

David Jason

Joined: Aug 21, 2012
Posts: 27
Vishal Shaw wrote:Hi,
I did not get it, why do you want to work with 10,000+ String all at a time. If they are not going to be used all at once, try using a few ,work with them make the objects null, notify gc() , then take another bunch.

Still if you want to work with a huge bunch of String object, I would suggest you to use either StringBuffer / StringBuilder. For concat, you can use append() while for replace you can follow the given steps
1> convert the object to String using toString() ,
2> perform replace() on the converted String
3> reinitialize the Builder/Buffer object with the replaced String.
4> Make the String object/s null and notify gc().

Now you have to choose.


Thanks. I think I will do that. Looks like there is no class for my requirements.
I want to execute some test SQL statements in a batch vs one at a time. When done as a single statement,
Strings can be used. What about a huge 10000 word statement all executed at once ?
Campbell Ritchie

Joined: Oct 13, 2005
Posts: 45251
Don’t go round thinking 10000 is a lot. 10 million might be a lot, but not 10000.
Don’t know about the 10000 word SQL statement. Because of network limitations, it is best to keep SQL statements small, but I am not sure what the optimum size would be. If you update one word at a time, you will have 10000 statements, and that will have a severe performance overhead. At 100 words a time, you have 100 statements, which will be much faster. Don’t know how a single 10000‑word statement will perform.

Shall try moving this discussion to the databases forum, and see whether people there can help.
Campbell Ritchie

Joined: Oct 13, 2005
Posts: 45251
This thread was split from a Beginning Java thread.
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3728

The fastest way to do 'X' in a database may differ in different databases.

If you're writing database agnostic code, my advice is to lean toward the safer way, which is usually also the simpler way. In this case it would be one statement per call, probably using JDBC batching. I believe there are databases which won't accept multiple statements in a single DB call.

If you're targeting a specific database, you can try various approaches and see what works best.
I agree. Here's the link:
subject: Performance of a 10000-word prepared statement
jQuery in Action, 3rd edition