Re: How to design tables that support 50-100K rows?
Probably could:)
James Carman wrote:
In Hibernate, couldn't you just use get(Class entityClass,
Serializable id) rather than doing the whole uniqueResult() stuff?
On Thu, Apr 17, 2008 at 9:09 AM, Nino Saturnino Martinez Vazquez Wael
[EMAIL PROTECTED] wrote:
Using jpa you can do it this way:
private BaseEntity findById(String table, Long id) {
Query query = entityManager.createQuery(select p from + table
+ p where p.id= + id);
return (BaseEntity) query.getSingleResult();
}
private List findBaseEntity(int first, int count, String property,
boolean ascending, String table) {
Query query = entityManager.createQuery(select p from + table
+ p order by p.:property :sort);
query.setParameter(property, property);
if (ascending) {
query.setParameter(sort, asc);
} else {
query.setParameter(sort, desc);
}
query.setFirstResult(first);
query.setMaxResults(count);
return (ListBaseEntity) query.getResultList();
}
and this in hibernate:
public Promo findPromo(Long id) {
Criteria criteria =
getHibernateSession().createCriteria(Promo.class);
criteria.add(Restrictions.eq(id, id));
return (Promo) criteria.uniqueResult();
}
public ListPromo findPromo(int first, int count, String property,
boolean ascending, Company company) {
Criteria criteria = getHibernateSession().createCriteria(Promo.class)
.add(Restrictions.eq(company, company));
criteria.setMaxResults(count);
criteria.setFirstResult(first);
if (ascending) {
criteria.addOrder(Order.asc(property));
} else {
criteria.addOrder(Order.desc(property));
}
return (ListPromo) criteria.list();
}
public int promoCount(Company company) {
Criteria criteria = getHibernateSession().createCriteria(Promo.class)
.add(Restrictions.eq(company, company));
criteria.setProjection(Projections.rowCount());
Object obj = criteria.uniqueResult();
return (Integer) obj;
}
gumnaam23 wrote:
I have done this, and it works quite nicely.
Be warned though, the biggest bottle neck in this, is the loading of the
data from database.
So for best performance, you should read only one page's worth data at a
time from your DB
(in your case 500).
If you load the whole data, and use some thing like a list iterator, you
will not get any performance
benefit.
Unfortunately ANSI SQL has no syntax for pagination of results. So you
have
2 choices.
1) Keep an open DB cursor , and retrieve as much data as you want per page
rendering. But this
ties your cursor to the user session. Not a very good idea.
2) Use the vendor specific way to get paginated results. Almost all major
vendors support pagination
of query results in their own idiosyncratic way.
If you go with approach 2, you can even use the
AjaxFallbackDefaultDataTable, which will do all the
pagination etc for you, and with AJAX to boost.
You only need to provide a SortableDataProvider, which implements the
size()
and iterate(int lower, int upper) calls. Your size() call should fetch a
total count of items. i.e. select count(*) and the iterate(int lower, int
upper) should retrieve the query results
between lower and upper limit.
And with sortable data provider, you even get sorting for free. (I mean
the
UI, you do have to write
the back end implementation your self).
For my case, I use iBatis for DB access, so it is really easy to write DB
Vendor specific queries and
yet retain the pagination abstraction. If I need to work with another DB ,
I
can easily swap the
pagination query part.
Hope this has helped.
PhilipJohnson wrote:
* Finally, when I am displaying Page 1 containing 500 rows (using
something like PageableListView), I'd like to only populate my model
with rows 1-500 from
my backing store. Let's say the user then clicks on Page 3: at that
point, I'd like
to go retrieve rows 1001-1500 from my backing store and populate my
model for rows
1001-1500. How would one go about doing this? Would it be possible to
kind of sketch out the
approach for me?
--
-Wicket for love
Nino Martinez Wael
Java Specialist @ Jayway DK
http://www.jayway.dk
+45 2936 7684
-
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]
--
-Wicket for love
Nino Martinez Wael
Java Specialist @ Jayway DK
http://www.jayway.dk
+45 2936 7684
Re: How to design tables that support 50-100K rows?
I have done this, and it works quite nicely. Be warned though, the biggest bottle neck in this, is the loading of the data from database. So for best performance, you should read only one page's worth data at a time from your DB (in your case 500). If you load the whole data, and use some thing like a list iterator, you will not get any performance benefit. Unfortunately ANSI SQL has no syntax for pagination of results. So you have 2 choices. 1) Keep an open DB cursor , and retrieve as much data as you want per page rendering. But this ties your cursor to the user session. Not a very good idea. 2) Use the vendor specific way to get paginated results. Almost all major vendors support pagination of query results in their own idiosyncratic way. If you go with approach 2, you can even use the AjaxFallbackDefaultDataTable, which will do all the pagination etc for you, and with AJAX to boost. You only need to provide a SortableDataProvider, which implements the size() and iterate(int lower, int upper) calls. Your size() call should fetch a total count of items. i.e. select count(*) and the iterate(int lower, int upper) should retrieve the query results between lower and upper limit. And with sortable data provider, you even get sorting for free. (I mean the UI, you do have to write the back end implementation your self). For my case, I use iBatis for DB access, so it is really easy to write DB Vendor specific queries and yet retain the pagination abstraction. If I need to work with another DB , I can easily swap the pagination query part. Hope this has helped. PhilipJohnson wrote: * Finally, when I am displaying Page 1 containing 500 rows (using something like PageableListView), I'd like to only populate my model with rows 1-500 from my backing store. Let's say the user then clicks on Page 3: at that point, I'd like to go retrieve rows 1001-1500 from my backing store and populate my model for rows 1001-1500. How would one go about doing this? Would it be possible to kind of sketch out the approach for me? -- View this message in context: http://www.nabble.com/How-to-design-tables-that-support-50-100K-rows--tp16737855p16743554.html Sent from the Wicket - User mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: How to design tables that support 50-100K rows?
Using jpa you can do it this way:
private BaseEntity findById(String table, Long id) {
Query query = entityManager.createQuery(select p from + table
+ p where p.id= + id);
return (BaseEntity) query.getSingleResult();
}
private List findBaseEntity(int first, int count, String property,
boolean ascending, String table) {
Query query = entityManager.createQuery(select p from + table
+ p order by p.:property :sort);
query.setParameter(property, property);
if (ascending) {
query.setParameter(sort, asc);
} else {
query.setParameter(sort, desc);
}
query.setFirstResult(first);
query.setMaxResults(count);
return (ListBaseEntity) query.getResultList();
}
and this in hibernate:
public Promo findPromo(Long id) {
Criteria criteria =
getHibernateSession().createCriteria(Promo.class);
criteria.add(Restrictions.eq(id, id));
return (Promo) criteria.uniqueResult();
}
public ListPromo findPromo(int first, int count, String property,
boolean ascending, Company company) {
Criteria criteria =
getHibernateSession().createCriteria(Promo.class)
.add(Restrictions.eq(company, company));
criteria.setMaxResults(count);
criteria.setFirstResult(first);
if (ascending) {
criteria.addOrder(Order.asc(property));
} else {
criteria.addOrder(Order.desc(property));
}
return (ListPromo) criteria.list();
}
public int promoCount(Company company) {
Criteria criteria =
getHibernateSession().createCriteria(Promo.class)
.add(Restrictions.eq(company, company));
criteria.setProjection(Projections.rowCount());
Object obj = criteria.uniqueResult();
return (Integer) obj;
}
gumnaam23 wrote:
I have done this, and it works quite nicely.
Be warned though, the biggest bottle neck in this, is the loading of the
data from database.
So for best performance, you should read only one page's worth data at a
time from your DB
(in your case 500).
If you load the whole data, and use some thing like a list iterator, you
will not get any performance
benefit.
Unfortunately ANSI SQL has no syntax for pagination of results. So you have
2 choices.
1) Keep an open DB cursor , and retrieve as much data as you want per page
rendering. But this
ties your cursor to the user session. Not a very good idea.
2) Use the vendor specific way to get paginated results. Almost all major
vendors support pagination
of query results in their own idiosyncratic way.
If you go with approach 2, you can even use the
AjaxFallbackDefaultDataTable, which will do all the
pagination etc for you, and with AJAX to boost.
You only need to provide a SortableDataProvider, which implements the size()
and iterate(int lower, int upper) calls.
Your size() call should fetch a total count of items. i.e. select count(*)
and the iterate(int lower, int upper) should retrieve the query results
between lower and upper limit.
And with sortable data provider, you even get sorting for free. (I mean the
UI, you do have to write
the back end implementation your self).
For my case, I use iBatis for DB access, so it is really easy to write DB
Vendor specific queries and
yet retain the pagination abstraction. If I need to work with another DB , I
can easily swap the
pagination query part.
Hope this has helped.
PhilipJohnson wrote:
* Finally, when I am displaying Page 1 containing 500 rows (using
something like
PageableListView), I'd like to only populate my model with rows 1-500 from
my backing
store. Let's say the user then clicks on Page 3: at that point, I'd like
to go retrieve
rows 1001-1500 from my backing store and populate my model for rows
1001-1500. How would
one go about doing this? Would it be possible to kind of sketch out the
approach for me?
--
-Wicket for love
Nino Martinez Wael
Java Specialist @ Jayway DK
http://www.jayway.dk
+45 2936 7684
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: How to design tables that support 50-100K rows?
In Hibernate, couldn't you just use get(Class entityClass,
Serializable id) rather than doing the whole uniqueResult() stuff?
On Thu, Apr 17, 2008 at 9:09 AM, Nino Saturnino Martinez Vazquez Wael
[EMAIL PROTECTED] wrote:
Using jpa you can do it this way:
private BaseEntity findById(String table, Long id) {
Query query = entityManager.createQuery(select p from + table
+ p where p.id= + id);
return (BaseEntity) query.getSingleResult();
}
private List findBaseEntity(int first, int count, String property,
boolean ascending, String table) {
Query query = entityManager.createQuery(select p from + table
+ p order by p.:property :sort);
query.setParameter(property, property);
if (ascending) {
query.setParameter(sort, asc);
} else {
query.setParameter(sort, desc);
}
query.setFirstResult(first);
query.setMaxResults(count);
return (ListBaseEntity) query.getResultList();
}
and this in hibernate:
public Promo findPromo(Long id) {
Criteria criteria =
getHibernateSession().createCriteria(Promo.class);
criteria.add(Restrictions.eq(id, id));
return (Promo) criteria.uniqueResult();
}
public ListPromo findPromo(int first, int count, String property,
boolean ascending, Company company) {
Criteria criteria = getHibernateSession().createCriteria(Promo.class)
.add(Restrictions.eq(company, company));
criteria.setMaxResults(count);
criteria.setFirstResult(first);
if (ascending) {
criteria.addOrder(Order.asc(property));
} else {
criteria.addOrder(Order.desc(property));
}
return (ListPromo) criteria.list();
}
public int promoCount(Company company) {
Criteria criteria = getHibernateSession().createCriteria(Promo.class)
.add(Restrictions.eq(company, company));
criteria.setProjection(Projections.rowCount());
Object obj = criteria.uniqueResult();
return (Integer) obj;
}
gumnaam23 wrote:
I have done this, and it works quite nicely.
Be warned though, the biggest bottle neck in this, is the loading of the
data from database.
So for best performance, you should read only one page's worth data at a
time from your DB
(in your case 500).
If you load the whole data, and use some thing like a list iterator, you
will not get any performance
benefit.
Unfortunately ANSI SQL has no syntax for pagination of results. So you
have
2 choices.
1) Keep an open DB cursor , and retrieve as much data as you want per page
rendering. But this
ties your cursor to the user session. Not a very good idea.
2) Use the vendor specific way to get paginated results. Almost all major
vendors support pagination
of query results in their own idiosyncratic way.
If you go with approach 2, you can even use the
AjaxFallbackDefaultDataTable, which will do all the
pagination etc for you, and with AJAX to boost.
You only need to provide a SortableDataProvider, which implements the
size()
and iterate(int lower, int upper) calls. Your size() call should fetch a
total count of items. i.e. select count(*) and the iterate(int lower, int
upper) should retrieve the query results
between lower and upper limit.
And with sortable data provider, you even get sorting for free. (I mean
the
UI, you do have to write
the back end implementation your self).
For my case, I use iBatis for DB access, so it is really easy to write DB
Vendor specific queries and
yet retain the pagination abstraction. If I need to work with another DB ,
I
can easily swap the
pagination query part.
Hope this has helped.
PhilipJohnson wrote:
* Finally, when I am displaying Page 1 containing 500 rows (using
something like PageableListView), I'd like to only populate my model
with rows 1-500 from
my backing store. Let's say the user then clicks on Page 3: at that
point, I'd like
to go retrieve rows 1001-1500 from my backing store and populate my
model for rows
1001-1500. How would one go about doing this? Would it be possible to
kind of sketch out the
approach for me?
--
-Wicket for love
Nino Martinez Wael
Java Specialist @ Jayway DK
http://www.jayway.dk
+45 2936 7684
-
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]
Re: How to design tables that support 50-100K rows?
Thanks for all the quick help! I am communicating with a back-end RESTful web service that I've designed myself, so paginating the data from the repository is actually no problem. But it's cool to see the sample DB code, and I'm sure others will find it useful when they search the mailing list. (I now realize my third bullet point was slightly misleading!) Thank you, Igor, for the pointers to relevant classes: dataview, idataprovider, and pagingnavigator. I will start investigating them. If anyone happens to have a link to sample code illustrating how these three classes work together, and could easily point me to it, I would be very appreciative! Cheers, Philip -- View this message in context: http://www.nabble.com/How-to-design-tables-that-support-50-100K-rows--tp16737855p16746811.html Sent from the Wicket - User mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: How to design tables that support 50-100K rows?
PhilipJohnson wrote: If anyone happens to have a link to sample code illustrating how these three classes work together, and could easily point me to it, I would be very appreciative! I'm going to answer my own question to save current and future readers the trouble: http://wicketstuff.org/wicket13/repeater/ Contains nice sample code. Cheers, Philip -- View this message in context: http://www.nabble.com/How-to-design-tables-that-support-50-100K-rows--tp16737855p16754896.html Sent from the Wicket - User mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
How to design tables that support 50-100K rows?
Greetings, wicket experts, I am a newly infatuated wicket developer, and would like to solicit your advice on the best way to design a table that can support up to 100,000 rows. Here are my initial thoughts: * Clearly, I don't want to display all of them at once. I am assuming that I will want to use a class like PageableListView so that I can show only 500 or so at a time. Is that reasonable, or is there a better approach? * Clearly, I don't want to have to keep all of the rows in memory. I am assuming I need to use a LoadableDetachable model as a result. Is that correct? * Finally, when I am displaying Page 1 containing 500 rows (using something like PageableListView), I'd like to only populate my model with rows 1-500 from my backing store. Let's say the user then clicks on Page 3: at that point, I'd like to go retrieve rows 1001-1500 from my backing store and populate my model for rows 1001-1500. How would one go about doing this? Would it be possible to kind of sketch out the approach for me? If I am totally going down the wrong path, please let me know about that. Thanks for your help! Cheers, Philip Johnson - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: How to design tables that support 50-100K rows?
see dataview, idataprovider, and pagingnavigator -igor On Wed, Apr 16, 2008 at 8:00 PM, Philip Johnson [EMAIL PROTECTED] wrote: Greetings, wicket experts, I am a newly infatuated wicket developer, and would like to solicit your advice on the best way to design a table that can support up to 100,000 rows. Here are my initial thoughts: * Clearly, I don't want to display all of them at once. I am assuming that I will want to use a class like PageableListView so that I can show only 500 or so at a time. Is that reasonable, or is there a better approach? * Clearly, I don't want to have to keep all of the rows in memory. I am assuming I need to use a LoadableDetachable model as a result. Is that correct? * Finally, when I am displaying Page 1 containing 500 rows (using something like PageableListView), I'd like to only populate my model with rows 1-500 from my backing store. Let's say the user then clicks on Page 3: at that point, I'd like to go retrieve rows 1001-1500 from my backing store and populate my model for rows 1001-1500. How would one go about doing this? Would it be possible to kind of sketch out the approach for me? If I am totally going down the wrong path, please let me know about that. Thanks for your help! Cheers, Philip Johnson - 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]
