• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to improve performance of sql merging with webmacro

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Martin,

I will work on based on your suggestion.Thanks If I feel anything confused I will ask you thank you very much
 
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The bottleneck is:
1. you need a PreparedStatement to replace replacingWebMacroStatement
2. your csv parse code is slow
 
balamurugan velliambalam
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Wei Dai,

How you tell my csv parse code is slow can you please explain..It will be helpful
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic