Database access taking a very long time...

This may not be a Vaadin problem but I’m hoping someone can help me figure out what is going on…

I’m using EclipseLink to access a mysql database. One of my tables has several hundred thousand rows. If I run mysql on the command line and run the following:


select * from metric where path like '%abcde%';

The output is about 9000 rows and takes about 1/2 of a second to complete.

When I make the same request from within my Vaadin 7 program, I get the following behavior:

  1. The first time I make the request, it runs for over a minute - the spinning wheel in the top right corner turns yellow, then red, and after a minute I get a ‘Communication Problem’ message. When I click ‘Continue’ my program actually exits and goes back to the login screen

  2. When I log in a second time and make the identical request, it runs for about 20 seconds and then completes correctly. The correct set of 9000 records is returned to my table.

Obviously some caching is going on here - but I can’t figure out why it takes so long to collect the data. Here is the code I’m using to request the data:


     List<Metric> lm = null;

...
...
     lm = getMetric(filter_string);

     public List<Metric> getMetric(String fnameFilter){
		List<Metric> l_FilteredList = null;
		
		em = getEntityManager();
		
		Query q = em.createQuery("select m from Metric as m where m.filename like :name");
		q.setParameter("name", fnameFilter);

		l_FilteredList = q.getResultList();

		return(l_FilteredList);
	}

I put some print statements in the getMetric() routine and the time is being spent in the q.getResultList() call.

What can I do to speed this up?

Thanks,

nbc

I have some additional information - Still having the problem tho… I believe the problem is caused by the fact that my database contains 4 tables that are inter-locked with foreign keys. The structure looks something like this - several data fields left out for simplicity…


Table Asset
    int id
    String name

Table Asset_Member
    int id
    int asset_id   ---> Asset
    String member_name;

Table metric
    int id
    int asset_member_id   ---> Asset_Member
    String path

There is a 4th table but these three describe the problem I think. The issue is that there are about 10000 Asset objects, and more than 500000 metrics. So when I try to load a set of metrics, even though it may only be 20k-30k, they also load the various Assets and Asset Members - and it seems to take forever. I need these relationships - and I have it set up at the moment to do cascade inserts and cascade deletes - but the performance penalty seems excessive. Do I have to manage the relationships myself in order to be able to load data quickly? I’m unfortunately not a database expert, so there may be something obvious I should be doing to make this work better.

I’ve tried using JPAContainers and I’ve tried BeanItemContainers - neither seems to be very fast… Any suggestions would be appreciated…

thanks,

nbc

Allow me to re-phrase the question - I’m now having a more Vaadin-specific issue related to my database access.

In the example above, I was trying to load a set of metrics (up to 500k records) and then display one column of that data in a table (the path). What I’ve done now is write a query that simply selects the path value from the table instead of the entire record - this eliminates all the effort of reading the foreign keys and building the related records.

So I now have a List coming out of my query - the code for the query looks like:


    em = getEntityManager();
    Query q = em.createQuery("select m.path from Metric where m.path like :name");
    q.setParameter("name", filterString);
    List<String> ls = q.getResultList();
    em.close();

This returns a list of strings very quickly - almost instantly, in fact. Now I want to display that in a table. And that is where things slow down. I tried creating a BeanItemContainer, but it doesn’t want to accept a list of strings as inputs. I created a Path_Bean, but it takes a long time to extract 100k strings from my list, create beans and add them to the beanitemcontainer (more than a minute for 120k records).

So my Vaadin question becomes:

How do I take that list of strings and throw it into a table most efficiently? I want to be able to apply additional filters on the container once it is displayed…

Thanks,

nbc

If you have 10000 or 100000 rows, I would strongly recommend using some lazy container that loads only the needed data from the DB (with your custom query and additional limits) and - if possible - does also any additional filtering in the DB.

If you do want to use an in-memory container and make such huge updates to it, make sure the container is not attached to a Table when making the changes. That can make a big difference as the container does not need to send notifications to the Table for every change but only one at the end when it is attached as the data source of the table.

Also, if keeping the data in memory, I would recommend using a customized subclass of AbstractInMemoryContainer instead of Bean(Item)Container or IndexedContainer. This way you can tune it to work the way you want and trim the memory and CPU overhead by avoiding any extra copies of data, access via reflection APIs etc. It should not be too hard to implement - especially if the data shown is read-only.

Thanks Henri - I’m now trying to figure out how the LazyQueryContainer works - that may do the trick…

nbc