AppletTalk.com

Guest · Posted: Mon Apr 10, 2006 8:12 pm Post subject: Speed UP JDBC Statements.

I am trying to do an insert or update on about 60,000 data items, but I
can not get the execution speed to perform this. I am accessing a sql
server 2000.
1) selectStoredProc ;
Based on the result of teh select statemnt I perform either an update
or an insert
updateStoredProc;
insertStoredProc. I use threading and the classes shares a static
Connection objecct, But I can not get more than 120 pieces of data a
minute.
I do bassed on this
int rowCount = results.getInt(1);
based rowCount either
insertStoredProc.setString(2,accPeriod);
insertStoredProc.setString(3,primKey); .. up to 12 data items inside
the sql statemnt.

This method is accessed using a thread
line = _reader.readLine() ;
tempThread.setLine(line) ;
Thread thread = new Thread(tempThread);
thread.start() ; // passes line to method and put in DB.
What would be the fastest way to do this, I use a static connection
taht is shared among the threaded object.
Appreciate all teh help I can get!!!
thank-you

Joe Weinstein · Guest

PythonAnimal (AT) gmail (DOT) com wrote:

Robert Klemme · Guest

Joe Weinstein wrote:

Guest · Posted: Tue Apr 11, 2006 3:12 pm Post subject: Re: Speed UP JDBC Statements.

Here is the code, thanks for checking it out

updateStoredProc = _devConnection.prepareCall("{ call
UpdateGenDataBreak(?,?,?,?,?,?,?,?,?,?,?,?) }");
selectStoredProc = _devConnection.prepareCall("{ call
SelectCount(?,?,?,?,?) }");
insertStoredProc = _devConnection.prepareCall("{ call
InsertGenDatabreak(?,?,?,?,?,?,?,?,?,?,?,?) }");
if(rowCount == 1)
{updateStoredProc.setString(1,portfNumber); // 12 strings per insert
/ update SetString.... (12
updateStoredProc.setString(2,accPeriod);
updateStoredProc.setString(3,primKey);
updateStoredProc.executeUpdate()
}
else {
insertStoredProc.setString(1,portfNumber);
insertStoredProc.setString(2,accPeriod);
insertStoredProc.setString(3,primKey);

insertStoredProc.executeUpdate()
}

I put this in the constructor
updateStoredProc = _devConnection.prepareCall("{ call
UpdateGenDataBreak(?,?,?,?,?,?,?,?,?,?,?,?) }");
selectStoredProc = _devConnection.prepareCall("{ call
SelectCount(?,?,?,?,?) }");
insertStoredProc = _devConnection.prepareCall("{ call
InsertGenDatabreak(?,?,?,?,?,?,?,?,?,?,?,?) }");

I am unsure if maybe using a tokenizer to get 12 tokens and put them
into a string var is slowing me down just enough. But I can not get
more than 3 insert/update statements to work a second (although each
update/insert also requires a select statement to see if it is in the
table)

Thanks so much,
Appreciate it

Joe Weinstein · Guest

PythonAnimal (AT) gmail (DOT) com wrote:

Guest · Posted: Tue Apr 11, 2006 4:12 pm Post subject: Re: Speed UP JDBC Statements.

Here is my entire code - This method is called after a line is read
from the file. How would I go about doing it all on the DB? Use
PL/SQL to iterate through all the lines after passing entire contents
of file to the DB, what object would I send to teh DB which would hold
all the lines I must eitehr insert/update??

selectStoredProc.setString(1,portfNumber);
selectStoredProc.setString(2,accPeriod);
selectStoredProc.setString(3,primKey);
selectStoredProc.setString(4,repName);
selectStoredProc.setString(5,field); //field

ResultSet results = selectStoredProc.executeQuery();
results.next();

int rowCount = results.getInt(1);
results.close();
System.out.println("ROWCOUNT:" + rowCount) ;
if(rowCount == 0) // Query returned no matching rows
{
// if there is o data insert into database
insertCounter++;
insertStoredProc.clearParameters();
insertStoredProc.setString(1,portfNumber);
insertStoredProc.setString(2,accPeriod);
insertStoredProc.setString(3,primKey);
insertStoredProc.setString(4,repName);
insertStoredProc.setString(5,field);
insertStoredProc.setString(6,gen5val);
insertStoredProc.setString(7,gen6val);
insertStoredProc.setString(8,differences);
insertStoredProc.setString(9,comments);
insertStoredProc.setInt(10,displayInteger);
insertStoredProc.setString(11,misc1);
insertStoredProc.setString(12,misc2);
System.out.println("INSERT: " + insertStoredProc.executeUpdate());
}
else // query returned a matching row - update data
{
String tempReportName = this._reportName;
CallableStatement checkComments = _devConnection.prepareCall("{
call GetGenBreakValues(?,?,?,?,?) }");
checkComments.setString(1,portfNumber);
checkComments.setString(2,accPeriod);
checkComments.setString(3,primKey);
checkComments.setString(4,tempReportName);
checkComments.setString(5,field); //field
ResultSet checkCommentsResults = checkComments.executeQuery();
checkCommentsResults.next();

String dbGenFiveVal = checkCommentsResults.getString(1);
String dbGenSixVal = checkCommentsResults.getString(2);
String dbComments = checkCommentsResults.getString(3);
String dbDisplay = checkCommentsResults.getString(4);
String dbMisc1 = checkCommentsResults.getString(5);
String dbMisc2 = checkCommentsResults.getString(6);

// if there were no comments to overwrite simply overwrite - Value
may h ave changed!!!
if((dbComments.equals("") == false) || (dbMisc1.equals("") ==
false) || (dbMisc2.equals("") == false))
{
updateCounter++;
updateStoredProc.setString(1,portfNumber);
updateStoredProc.setString(2,accPeriod);
updateStoredProc.setString(3,primKey);
updateStoredProc.setString(4,repName);
updateStoredProc.setString(5,field);
updateStoredProc.setString(6,gen5val);
updateStoredProc.setString(7,gen6val);
updateStoredProc.setString(8,differences);
updateStoredProc.setString(9,dbComments);
updateStoredProc.setInt(10,displayInteger);
updateStoredProc.setString(11,dbMisc1);
updateStoredProc.setString(12,dbMisc2);
System.out.println("UPDATE: " +
updateStoredProc.executeUpdate());
}
}
Thanks guy I really appreciate this.

Guest · Posted: Tue Apr 11, 2006 4:12 pm Post subject: Re: Speed UP JDBC Statements.

Hello,
Yes the same DBMS I read a line parse it into tokens then send it to a
stored proc. rowCount is set by the Select stored proc ( call
SelectCount(?,?,?,?,?) }") Wink

Rest of the stored proc are a simple
query which takes the 12 paramaters. Either updates or inserts. All
stored procs are on the same DBMS which is SQL Serv 2000. I Dont know
how to speed this up although the database server is a few states away.
Thanks

Robert Klemme · Guest

PythonAnimal (AT) gmail (DOT) com wrote:

Joe Weinstein · Guest

PythonAnimal (AT) gmail (DOT) com wrote:

Mladen Adamovic · Guest

You said that you have 3 insert/update statements per second when you do
use JDBC. How many insert/update statements per second do you have when
you try it from console (without JDBC)?

PythonAnimal (AT) gmail (DOT) com wrote:

Guest · Posted: Wed Apr 12, 2006 9:12 pm Post subject: Re: Speed UP JDBC Statements.

We recently had some major performance improvements (400%), by upping
the memory to the JVM -XMx option and also ensuring that the commit
count was being implemented. Can you batch up these requests, so
you're not commiting changes every run?

(21% was from Memory settings)
(380% from setting commit count to 10,000 from 100)

Roedy Green · Guest

On 12 Apr 2006 14:08:21 -0700, jeffrey.h.hill (AT) gmail (DOT) com wrote, quoted
or indirectly quoted someone who said :

Guest · Posted: Fri Apr 14, 2006 9:12 am Post subject: Re: Speed UP JDBC Statements.

Changed rows will reside in some database specific cache area
associated with the session that changed them until a commit is issued
on that connection. Committing less often (higher commit count) saves
time because the database engine has to flush the session's changed
data cache into the permanent (committed) data store less often.
Committing data usually results in physical file writes and can result
in contention between sessions, so the less often you do it, the higher
your throughput will be.

On the other hand: there will be some limit to how much uncommitted
data a session can hold based on database server resources; the more
data processed in each transaction, the more that must be reprocessed
in the event of an error; transactions that run too long increase the
chance of contention between sessions by holding locks on modified
rows.

Like everything else, this is a trade off.