You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@wicket.apache.org by Frank Prins <Fr...@copernicus.nl> on 2010/09/07 08:40:06 UTC
DB lookup time-out within DataTable Iterator
Hi guys!
Breaking my head on the following, hopefully someone might be able to
give me a hint?
In a page which serves a view on a log database with quite a lot of
rows, I am using a DataTable to show the rows. The data for the table is
being served by a SortableDataProvider which looks a bit like this:
<...>
public SortableSystemlogDataProvider(Map searchParams) {
// creating new instance, set default sorting, Search parameters,
counting dataset
this.dao = new H2InterfaceDaoImpl();
setSort("sl_id", false);
this.searchParams = searchParams;
this.systemlogListSize = dao.countAllSystemlogRows(searchParams);
}
public Iterator iterator(int first, int count) {
SortParam sp = getSort();
if (sp.getProperty() == null) {
setSort("sl_id", false);
sp = getSort();
}
// return iterator on search list by params
paginatedList = dao.selectSystemlogs(this.searchParams,
sp.getProperty(), sp.isAscending(), first, count);
return paginatedList.iterator();
}
<...>
The issue I am running into at this moment is this: in case of large
datasets, the db call from within the iterator gives a timeout, and
returns null. In this case I would like to give the user a warning
modalwindow and the possibility to change the search options. Of course
I am able to catch the timeout, and return an empty set, but I cannot
see how to move on from that, as I am already within the DataTable.
Anyone has an idea on this?
Thanks in advance, regards,
Frank Prins
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
RE: DB lookup time-out within DataTable Iterator
Posted by Frank Prins <Fr...@copernicus.nl>.
Hey!
@Ernesto,
Thx for the idea, but I am afraid that won't be the way to go. The issue
is coming with the initial page having the table, I am not having a set
of search parameters at that moment. The thing is that I was looking for
some way to interact with the table 'after' giving the dataprovider to
the table.
@Mike,
Aha, you're so close to the problem we're having here! Exactly that is
happening: the H2 database we are using has some problems with the
combination of the SELECT/WHERE/ORDER. It is not using an index in that
case, a known problem with this database we just found out :-(
So, a solution might be switching to another DB, or solving it in the
user interface, just as you are mentioning. Can you shine a light on way
to go for having another page component render based on the provider? I
Thx!
-----Oorspronkelijk bericht-----
Van: Michael O'Cleirigh [mailto:michael.ocleirigh@rivulet.ca]
Verzonden: dinsdag 7 september 2010 20:43
Aan: users@wicket.apache.org
Onderwerp: Re: DB lookup time-out within DataTable Iterator
Hi Frank ,
Are you sure that your dao is setting max results on the underlying
query? The provider.iterator(...) should only be returning the current
page values and typically the page size would be small like 25 to 100.
Also it depends on your backend database since I know that some (older
version of PostgreSQL) have issues with doing a SELECT ... ORDER BY X
LIMIT Y without looking at all of the values in the unlimited select.
If the total number of rows is reasonable (say several 1000) then just
return the full list into Java and then use a Comparator to sort the
loaded data according to the sort property.
In terms of handling this issue in your interface you can just have
another component render based on the value from the provider. If you
can cache the results of the provider like the loadable detachable model
does then there will only be 1 hit on the db and then you can show zero
rows in the table and an error message label.
Regards,
Mike
>
> Breaking my head on the following, hopefully someone might be able to
> give me a hint?
>
> In a page which serves a view on a log database with quite a lot of
> rows, I am using a DataTable to show the rows. The data for the table
> is being served by a SortableDataProvider which looks a bit like this:
>
> <...>
> public SortableSystemlogDataProvider(Map searchParams) {
> // creating new instance, set default sorting, Search parameters,
> counting dataset
> this.dao = new H2InterfaceDaoImpl();
> setSort("sl_id", false);
> this.searchParams = searchParams;
> this.systemlogListSize = dao.countAllSystemlogRows(searchParams);
> }
>
> public Iterator iterator(int first, int count) {
> SortParam sp = getSort();
> if (sp.getProperty() == null) {
> setSort("sl_id", false);
> sp = getSort();
> }
> // return iterator on search list by params
> paginatedList = dao.selectSystemlogs(this.searchParams,
> sp.getProperty(), sp.isAscending(), first, count);
> return paginatedList.iterator();
> }
> <...>
>
> The issue I am running into at this moment is this: in case of large
> datasets, the db call from within the iterator gives a timeout, and
> returns null. In this case I would like to give the user a warning
> modalwindow and the possibility to change the search options. Of
> course I am able to catch the timeout, and return an empty set, but I
> cannot see how to move on from that, as I am already within the
DataTable.
>
> Anyone has an idea on this?
>
> Thanks in advance, regards,
> Frank Prins
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: DB lookup time-out within DataTable Iterator
Posted by James Carman <ja...@carmanconsulting.com>.
It could be returning only 25 rows, but the criteria itself is what
makes the query slow. In that case, I'd recommend looking at your
indexes. Sometimes indexing doesn't help very much, though.
On Tue, Sep 7, 2010 at 2:43 PM, Michael O'Cleirigh
<mi...@rivulet.ca> wrote:
> Hi Frank ,
>
> Are you sure that your dao is setting max results on the underlying query?
> The provider.iterator(...) should only be returning the current page values
> and typically the page size would be small like 25 to 100.
>
> Also it depends on your backend database since I know that some (older
> version of PostgreSQL) have issues with doing a SELECT ... ORDER BY X LIMIT
> Y without looking at all of the values in the unlimited select.
>
> If the total number of rows is reasonable (say several 1000) then just
> return the full list into Java and then use a Comparator to sort the loaded
> data according to the sort property.
>
> In terms of handling this issue in your interface you can just have another
> component render based on the value from the provider. If you can cache the
> results of the provider like the loadable detachable model does then there
> will only be 1 hit on the db and then you can show zero rows in the table
> and an error message label.
>
> Regards,
>
> Mike
>
>
>>
>> Breaking my head on the following, hopefully someone might be able to
>> give me a hint?
>>
>> In a page which serves a view on a log database with quite a lot of
>> rows, I am using a DataTable to show the rows. The data for the table is
>> being served by a SortableDataProvider which looks a bit like this:
>>
>> <...>
>> public SortableSystemlogDataProvider(Map searchParams) {
>> // creating new instance, set default sorting, Search parameters,
>> counting dataset
>> this.dao = new H2InterfaceDaoImpl();
>> setSort("sl_id", false);
>> this.searchParams = searchParams;
>> this.systemlogListSize = dao.countAllSystemlogRows(searchParams);
>> }
>>
>> public Iterator iterator(int first, int count) {
>> SortParam sp = getSort();
>> if (sp.getProperty() == null) {
>> setSort("sl_id", false);
>> sp = getSort();
>> }
>> // return iterator on search list by params
>> paginatedList = dao.selectSystemlogs(this.searchParams,
>> sp.getProperty(), sp.isAscending(), first, count);
>> return paginatedList.iterator();
>> }
>> <...>
>>
>> The issue I am running into at this moment is this: in case of large
>> datasets, the db call from within the iterator gives a timeout, and
>> returns null. In this case I would like to give the user a warning
>> modalwindow and the possibility to change the search options. Of course
>> I am able to catch the timeout, and return an empty set, but I cannot
>> see how to move on from that, as I am already within the DataTable.
>>
>> Anyone has an idea on this?
>>
>> Thanks in advance, regards,
>> Frank Prins
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
>> For additional commands, e-mail: users-help@wicket.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: DB lookup time-out within DataTable Iterator
Posted by Michael O'Cleirigh <mi...@rivulet.ca>.
Hi Frank ,
Are you sure that your dao is setting max results on the underlying
query? The provider.iterator(...) should only be returning the current
page values and typically the page size would be small like 25 to 100.
Also it depends on your backend database since I know that some (older
version of PostgreSQL) have issues with doing a SELECT ... ORDER BY X
LIMIT Y without looking at all of the values in the unlimited select.
If the total number of rows is reasonable (say several 1000) then just
return the full list into Java and then use a Comparator to sort the
loaded data according to the sort property.
In terms of handling this issue in your interface you can just have
another component render based on the value from the provider. If you
can cache the results of the provider like the loadable detachable model
does then there will only be 1 hit on the db and then you can show zero
rows in the table and an error message label.
Regards,
Mike
>
> Breaking my head on the following, hopefully someone might be able to
> give me a hint?
>
> In a page which serves a view on a log database with quite a lot of
> rows, I am using a DataTable to show the rows. The data for the table is
> being served by a SortableDataProvider which looks a bit like this:
>
> <...>
> public SortableSystemlogDataProvider(Map searchParams) {
> // creating new instance, set default sorting, Search parameters,
> counting dataset
> this.dao = new H2InterfaceDaoImpl();
> setSort("sl_id", false);
> this.searchParams = searchParams;
> this.systemlogListSize = dao.countAllSystemlogRows(searchParams);
> }
>
> public Iterator iterator(int first, int count) {
> SortParam sp = getSort();
> if (sp.getProperty() == null) {
> setSort("sl_id", false);
> sp = getSort();
> }
> // return iterator on search list by params
> paginatedList = dao.selectSystemlogs(this.searchParams,
> sp.getProperty(), sp.isAscending(), first, count);
> return paginatedList.iterator();
> }
> <...>
>
> The issue I am running into at this moment is this: in case of large
> datasets, the db call from within the iterator gives a timeout, and
> returns null. In this case I would like to give the user a warning
> modalwindow and the possibility to change the search options. Of course
> I am able to catch the timeout, and return an empty set, but I cannot
> see how to move on from that, as I am already within the DataTable.
>
> Anyone has an idea on this?
>
> Thanks in advance, regards,
> Frank Prins
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org
Re: DB lookup time-out within DataTable Iterator
Posted by Ernesto Reinaldo Barreiro <re...@gmail.com>.
Hi Frank,
Just an idea... If you do the filtering "via AJAX" you could use the
same AJAX request to:
1- display a modal window in case you get the timeout error: just
place the ModalWindow on your page do
modalWindow.addOrReplace(new
MyErrorMessagePanel(modalWindow.getContentId())); and use
modalWindow.show(target) to show it.
2-Place your "filters" on a panel (orWebMarkupConatiner) that can be
update via AJAX, reset the filter bean you are using to populate the
filters and update this panel via AJAX.
Ernesto
On Tue, Sep 7, 2010 at 8:40 AM, Frank Prins <Fr...@copernicus.nl> wrote:
> Hi guys!
>
> Breaking my head on the following, hopefully someone might be able to
> give me a hint?
>
> In a page which serves a view on a log database with quite a lot of
> rows, I am using a DataTable to show the rows. The data for the table is
> being served by a SortableDataProvider which looks a bit like this:
>
> <...>
> public SortableSystemlogDataProvider(Map searchParams) {
> // creating new instance, set default sorting, Search parameters,
> counting dataset
> this.dao = new H2InterfaceDaoImpl();
> setSort("sl_id", false);
> this.searchParams = searchParams;
> this.systemlogListSize = dao.countAllSystemlogRows(searchParams);
> }
>
> public Iterator iterator(int first, int count) {
> SortParam sp = getSort();
> if (sp.getProperty() == null) {
> setSort("sl_id", false);
> sp = getSort();
> }
> // return iterator on search list by params
> paginatedList = dao.selectSystemlogs(this.searchParams,
> sp.getProperty(), sp.isAscending(), first, count);
> return paginatedList.iterator();
> }
> <...>
>
> The issue I am running into at this moment is this: in case of large
> datasets, the db call from within the iterator gives a timeout, and
> returns null. In this case I would like to give the user a warning
> modalwindow and the possibility to change the search options. Of course
> I am able to catch the timeout, and return an empty set, but I cannot
> see how to move on from that, as I am already within the DataTable.
>
> Anyone has an idea on this?
>
> Thanks in advance, regards,
> Frank Prins
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
> For additional commands, e-mail: users-help@wicket.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@wicket.apache.org
For additional commands, e-mail: users-help@wicket.apache.org