db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Göschl,Siegfried" <Siegfried.Goes...@drei.com>
Subject RE: Torque Interactions with Village -- db connection question
Date Thu, 12 Feb 2004 18:54:11 GMT
Hi Daniel,

performance is a tricky question:

+) if your are using a connection pool the connection is normally not closed but returned
to the pool
+) does the 20 or 9 milleseconds occur for each call of resultlist.get(i) in your loop or
for the entire loop 

Cheers,

Siegfried Goeschl

-----Original Message-----
From: Daniel O'Neil [mailto:greenphoenix1189@yahoo.com]
Sent: Thursday, February 12, 2004 7:43 PM
To: torque-user@db.apache.org
Subject: Torque Interactions with Village -- db connection question


Hi,

I'm trying to optimize performance on a fairly large join to an Oracle 8i database.  In order
to
speed up the joing I return a List of Record objects and then use the row2Object method to
extract
them with some default offsets.  The join is fairly slow, but the real shocker comes when
I come
to this line:

Record row = (Record)resultlist.get(i);

it takes TWENTY milliseconds to build that row across a sqlnet connection in-house.  If I
to it on
my mysql database on my box, it takes nine, which is still huge on a box as fast as my dev
environment (dual 1.25Ghz G4).  

I've looked at the BasePeer source code and it returns the record using the
workingdogs.village.QueryDataSet class, which seems to set up a DB connection, execute the
query,
return the list, then close the connection.  So I'm kind of stumped here.  Does anyone have
any
idea what's going on?  Do you have any suggestions for optimizing the query?  Below is the
full
code.

Thanks in advance,
Daniel
-----------------------------

Criteria criteria = new Criteria();

try {

// the actual query:  joins and select columns
criteria.addJoin(TaskTypePeer.TKTP_ID, TaskPeer.TASK_TKTP_ID);
criteria.addJoin(TaskPeer.TASK_WKFL_ID, WorkflowPeer.WKFL_ID);
criteria.addJoin(WorkflowPeer.WKFL_LOAN_ID, LoanPeer.LOAN_ID);
criteria.addJoin(TaskPeer.TASK_WRKQ_ID, WorkquePeer.WRKQ_ID);
criteria.add(TaskPeer.TASK_STATUS, "open");
criteria.add(WorkquePeer.WRKQ_WKGP_ID, workgroupID);
criteria.addAscendingOrderByColumn(TaskPeer.TASK_TKTP_ID);
TaskPeer.addSelectColumns(criteria);
TaskTypePeer.addSelectColumns(criteria);
WorkflowPeer.addSelectColumns(criteria);
ReducedLoanPeer.addSelectColumns(criteria);

// return the results as a list of Village Records
List resultlist = BasePeer.doSelect(criteria);

// Load the OM objects
Task task = new Task();
TaskType tasktype = new TaskType();
Workflow workflow = new Workflow();
ReducedLoan loan = new ReducedLoan();

// instantiate classes for the row2Object call
Class taskClass = TaskPeer.getOMClass();
Class taskTypeClass = TaskTypePeer.getOMClass();
Class workflowClass = WorkflowPeer.getOMClass();
Class loanClass = ReducedLoanPeer.getOMClass();

for (int i = 0; i < resultlist.size(); i++) {
	row = (Record) resultlist.get(i);
	task = TaskPeer.row2Object(row, TASK_OFFSET, taskClass);
	tasktype = TaskTypePeer.row2Object(row, TASK_TYPE_OFFSET, taskTypeClass);
	workflow = WorkflowPeer.row2Object(row, WORKFLOW_OFFSET, workflowClass);
	loan = ReducedLoanPeer.row2Object(row, LOAN_OFFSET, loanClass);
}

} catch (Exception e) {
}


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message