permaculture playing cards*
The moose likes JDBC and the fly likes How to improve performance of sql merging with webmacro Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to improve performance of sql merging with webmacro" Watch "How to improve performance of sql merging with webmacro" New topic
Author

How to improve performance of sql merging with webmacro

balamurugan velliambalam
Greenhorn

Joined: Jul 13, 2010
Posts: 20
I have attached below code

Functionality

Reading csv and insert in db after replacing values with webmacro.

Reading values from csv @ first header information NO,NAME next to that read one by one values and put into webmacro context context.put("1","RAJARAJAN") next webmacro replace $(NO) ==>1 and $(NAME)==>RAJARAJAN and add in statment batch once it reached 1000 execute the batch.

Code is running as per functionality but it takes 4 minutes to parse 50,000 records need performance improvement or need to change logic ....kindly let me know if any doubts. Any change to drastic performance...

Note: I use webmacro because to replace $(NO) in merge query to values read in CSVwebmacro

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

There are several possibilities to improve the performance:

1) Use PreparedStatement instead of Statement. Not only it protects your code from possible SQL injection, it allows you to pass values read from the CSV file directly to the database, avoiding the text processing on Java side, and saves some time on DB side too (Oracle already knows your statement and only executes it with new values, whereas with your current approach, every statement you run is new to the database and needs to be parsed). See also our page on PreparedStatement.

2) Create a temporary table (a special kind of table in Oracle, whose contents is specific to each user/session; search for Oracle global temporary table for details). Insert the data you read from the file into the table using simple insert into (use PreparedStatement mentioned above and create batches of reasonable size). Then run one merge statement which will merge all data from the temp table in one batch. I believe this will be much more efficient than merging every row separately.

3) Use Oracle 11g JDBC driver. This version of the driver provides the topmost performance when using PreparedStatements in a batch. See also this.

4) Perhaps adding some indexes, or cardinality to the temp table hints might help further. This is actually SQL query tuning, which is a different question (and generally one too complicated for a single post like this).

In my opinion, these modifications will speed up your processing considerably. Further speedups might be possible, eg. with external tables, but that would require some setup on the DB server side, which might not be possible. My advice is to try these improvements first and see whether it gets good enough.
balamurugan velliambalam
Greenhorn

Joined: Jul 13, 2010
Posts: 20
Dear Martin,

I will work on based on your suggestion.Thanks If I feel anything confused I will ask you thank you very much
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
The bottleneck is:
1. you need a PreparedStatement to replace replacingWebMacroStatement
2. your csv parse code is slow
balamurugan velliambalam
Greenhorn

Joined: Jul 13, 2010
Posts: 20
Dear Wei Dai,

How you tell my csv parse code is slow can you please explain..It will be helpful
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to improve performance of sql merging with webmacro
 
Similar Threads
javax.naming.NoInitialContextException: Need to specify class name in environment or system property
writing in and appending to a file
xml creation
JSP Design - Basics
XSLT/JSP/Servlets ?? what to cook