Hi Thanks for answering my questions, thanks for forwding it to the forum.
What u are suggesting is exactly what i am doing i.e using 2 queries 1 for count and 1 for the result set. But i was hoping to use 1 dynamic sql instead of 2 to achieve the above result. U can look at my sql example and u will see that the condition after the Select does not work and that is actually my question. Why does not work ? Thanks Navin --- Brandon Goodin <[EMAIL PROTECTED]> wrote: > Paginated list only returns a subset of the results. > This is > determined by the pageSize. It does not retain a > total count. > > If you perform a count using the resultset it will > force the cursor to > traverse the whole resultset. Most databases do not > load up large > results sets completely. So, when you perform your > count using the > resultset you are already impacting your system in a > negative way and > not saving yourself any performance. > > My suggestion would be to use two sql calls. One > using a count (as a > count sql statement would be more performant than a > resultset > traversal) and the other sql statement to retrieve > the results you > want from the resultset. The PaginatedList in iBatis > will only > traverse the rows in the resultset that it needs to > in order to > populate the corresponding objects with. Then when > you call the next > page on the paginated list it retains the > appropriate information to > reconnect to the database and grab the next section > of the results > that you need. > > With this strategy you save yourself on memory and > cpu. > > Brandon > > > On Thu, 9 Dec 2004 23:31:15 -0800 (PST), Kevin > <[EMAIL PROTECTED]> wrote: > > Thanks for taking time to respond to my question. > > > > Here is an example of what i am trying to do. > > > > I have a query that serves a dual purpose > > 1> > > getting me a count of all the records that meet a > > certain criteria > > 2> > > return a subset of the records of whole list i > used > > for getting the count as i donot need the whole > result > > set for display. > > > > The type of query executed depends on a field in > the > > parameter class. > > > > I have attached my query below , if u look at it > > carefully u will see that the Select clause has a > > condition on "countQuery" and so does the where > > clause. > > > > Essentially the resultset columns change on the > fly > > depending on the query selected. > > > > This as far as i am told is not supported in > Ibatis. > > > > I was wondering why this is not supported and what > are > > the alternatives rather than having 2 queries. > > > > Your suggestion of doing a size on the list will > not > > work because the result set is only a subset. i.e > i > > only display 100 out lets say 10000 records on the > web > > layer to make it more effecient , so i set my > where > > clause to give me the range of records whose list > size > > will be 100 but the count should return 10000. The > > reson i need 10000 is for creating a web layer > based > > paginated list. > > > > Does the paginated list in Ibatis support this? > > > > <select > > id="getEmployeeAndLastCourseCount" > > > > > parameterClass="com.performixtech.emvolve.devmgr.trainingmanager.sqlmap.p > > aram.EmployeeAndLastCourseParam" > > > > > resultClass="com.performixtech.emvolve.devmgr.trainingmanager.model.Train > > ingRequestListModel"> > > Select > > > > <isNotNull > > property="countQuery"> > > > > > count(*) > > as count > > > > </isNotNull> > > > > <isNull > > property="countQuery"> > > > > rownumber, > > > > employeeId, > > > > employeeName, > > > > managerId, > > > > managerName, > > > > managementUnitId, > > > > managementUnitName, > > > > courseId, > > > > courseTitle, > > > > courseDate > > > > </isNull> > > > > FROM > > ( > > SELECT > > rownum as rownumber, > > e.emp_employee_id as > employeeId, > > e.emp_name as employeeName, > > e.emp_manager as managerId, > > e.manager_name as managerName, > > e.muid as managementUnitId, > > e.name as managementUnitName, > > cS.course_Id as courseId, > > cS.course_title as courseTitle, > > cS.start_datetime as > courseDate, > > > > cS.module_status_id > > FROM > > (select e.emp_employee_id, > > e.emp_name, > > em.emp_employee_id > > > > as emp_manager, > > em.emp_name as manager_name, mu.muid, mu.name > > > > from employee e, > > employee em, management_unit mu, > > > > mgmt_unit_association ma > > > > where > > e.emp_manager = > em.emp_employee_id > > and > > e.emp_employee_id = > ma.employee_id > > and > > mu.muid = ma.muid > > > > ) e > > > > left outer join > > > > (select > emp_employee_id, > > start_datetime, a.course_id, a.sched_id, > > > > module_status_id, > > c.course_title, rank from > > (select b.emp_employee_id, > > a.start_datetime, > > a.course_id, > > > > a.sched_id, > > b.module_status_id, dense_rank() over > > > > (partition by b.emp_employee_id > > > > order by b.emp_employee_id, > > a.start_datetime) rank from > > > > course_schedule a, > course_assignment === message truncated === __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
