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:[EMAIL PROTECTED] Sent: Thursday, February 12, 2004 7:43 PM To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
