Re: Slow performance with SQL and stmt.executeUpdate()

On May 25, 2:35 pm, John Gordon <gor...@xxxxxxxxx> wrote:
I have an application that writes to an Oracle SQL database. The main
loop reads one data record from an external source, splits it into
fields then writes it to a database table with a Statement.ExecuteUpdate
method call, using SQL insert.

The whole program is too big to post here, but this is the gist of it:

Connection conn = DriverManager.getConnection(...);
Statement stmt = conn.createStatement();

while(externalSourceHasData)
{
MyData md = readRecordFromExternalSource();

String name = md.getName();
String phone = md.getPhone();
String address = md.getAddress();

stmt.executeUpdate("insert into mytable values ('" + name +
"','" + phone + "','" + address + "')");
}

conn.close();

Performace is somewhat of an issue. It's marginally acceptable, but would
be nice if it were faster. Is there anything obvious that I'm doing wrong
here?

One thing I considered was turning off auto-commit, then doing a commit
after the loop is finished. Would this make a noticable difference on
200k records?

Also, is there a way to write more than one record at a time? Some sort
of batch-writing capability?

Thanks for any suggestions.

--
John Gordon A is for Amy, who fell down the stairs
gor...@xxxxxxxxx B is for Basil, assaulted by bears
-- Edward Gorey, "The Gashlycrumb Tinies"

Hi, yes. Look at the Statement addBatch() and executeBatch() calls.

Joe Weinstein at BEA Systems

.

Relevant Pages

  • Re: Dynamic SQL Execution
    ... the string: ... > @rOUT OUTPUT ... > SQL Server MVP ... >> In a cursor, as I loop through, I create a dynamic SQL each time. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can you use dynamic data in a request form
    ... I am am trying to write some code to perform a INSERT in SQL ... > table using a loop. ... How that string is built is irrelevant. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: Loop through names of controls
    ... Didn't scroll down to the string of the sql. ... thought he was having trouble getting an object in his loop. ... >> I'm running some SQL to insert the value in each control into a table. ...
    (microsoft.public.access.formscoding)
  • Newbee - how to loop through table and delete unwanted records
    ... Note it is always the 1st record of the new groupNo ... So I have decided to try to loop though the table and compare the ... Then the next line of code runs the SQL delete string I made in step 1. ...
    (microsoft.public.access.gettingstarted)
  • extension_pack
    ... It is used to set upper loop -- limits for non-deterministic values thus avoiding the use of access -- types and enabling the functions to be used for synthesizeable code. ... DivisorVal: integer) return std_logic_vector; function "/"(DividendVal: string; DivisorVal: integer) return std_logic_vector; ... for loopVar in 0 to slvVal'length/4-1 loop ... end loop; if then return not resultVar; -- "width mismatch" errors here are due to improper sizing of the vector that this function is assigned to else return resultVar; -- "width mismatch" errors here are due to improper sizing of the vector that this function is assigned to end if; ...
    (comp.lang.vhdl)