ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Changing result set metadata on the fly.
Date Fri, 10 Dec 2004 07:46:39 GMT
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 <kevin_405@yahoo.com> 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 b
> where
> 
>                              <isNotNull
> property="moduleStatusId">
> 
> b.module_status_id = #moduleStatusId#
> 
>                              </isNotNull>
> 
>                              and a.sched_id =
> b.sched_id
> 
>                              ) a, course c where rank
> = 1 and a.course_id = c.course_id ) cS
> 
>                 on
> 
>                                  e.emp_employee_id =
> cS.emp_employee_id
> 
>                              where
> 
>                              <isNotNull
> property="rownum">
> 
>                                               rownum
> >= #rownum#
> 
>                              </isNotNull>
> 
>                              <isNotNull prepend="AND"
> property="managerName">
> 
>                                               and
> e.manager_name like #managerName#
> 
>                              </isNotNull>
> 
>                              <isNotNull prepend="AND"
> property="employeeName">
> 
>                                               and
> e.emp_name like #employeeName#
> 
>                              </isNotNull>
> 
>                              <isNotNull prepend="AND"
> property="managementUnitName">
> 
>                                               e.name
> like #managementUnitName#
> 
>                              </isNotNull>
> 
>                              <isNotNull prepend="AND"
> property="managerId">
> 
> e.emp_manager = #managerId#
> 
>                              </isNotNull>
> 
>                              <isNull
> property="countQuery">
> 
>                                               <isNull
> property="orderByColumn">
> 
>         order by employeeName
> 
> </isNull>
> 
> <isNotNull property="orderByColumn">
> 
>                         order by #orderByColumn#
> 
> </isNotNull>
> 
>                                               <isNull
> property="orderByDesc">
> 
>         asc
> 
> </isNull>
> 
> <isNotNull property="orderByDesc">
> 
>                         desc
> 
> </isNotNull>
> 
>                              </isNull>
> 
>                              )
> 
>                              <isNull
> property="countQuery">
> 
>        WHERE
> 
>            rownumber between
> 
>            #startRownum# and #endRownum#
> 
>                              </isNull>
>                 </select>
> 
> 
> 
> --- Brandon Goodin <brandon.goodin@gmail.com> wrote:
> 
> > I'm not completely sure what you are asking. But, if
> > you want to count
> > your results you could simply check the .size()
> > method on your
> > returned list. Maybe you could provide a clearer
> > example.
> >
> > Brandon
> >
> >
> > On Thu, 9 Dec 2004 22:15:44 -0800 (PST), Kevin
> > <kevin_405@yahoo.com> wrote:
> > >
> > > Hi
> > >
> > > I just found out from someone that Ibatis does not
> > > allow changing the resultset metadata on the fly.
> > >
> > > This does not allow me to have the same query for
> > > count of records as well as getting the result
> > set. Is
> > > there any alternate way ibatis does something like
> > > this.
> > >
> > > It is not a big issue considering the power of the
> > > what Ibatis actually provides , but was curious to
> > > learn the reason for leaving this out.
> > >
> > > Thanks
> > > Kevin
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail - Find what you need with new enhanced
> > search.
> > > http://info.mail.yahoo.com/mail_250
> > >
> >
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

Mime
View raw message