Filter IN optimization

[GoogleCodeExporter]

Hi, 

I want to know if there is an efficient way to do joins between queries. I use 
to work with a database that has records segmented by function. This means that 
hibernate objects are created for each function around the database. 

They are joined by a common class that is called lead. This way I can separate 
functionality and plug-in and remove this functionality with easy.

I have:

Surveys
Statistics
Searchs 
etc

They all have Surveys.lead, Statistics.lead and so on.

This means that the object lead has normally no links to the other objects. And 
I have to do a search for lead to be able to find information about. Normally I 
do this by code but know I have a different problem.

I want to do people searching by criterias on survey and after find information 
on lead tables. The only way I found is by doing a search before and the 
looking at the Lead table. This way:

Search answerSearch = new Search(SurveyAnswer.class);

answerSearch.addFilterAnd(
    Filter.equal("idLot", "132")

);
answerSearch.addField("lead");
List<Object> list = surveyAnswerDAO.search(answerSearch);

//log.debug("Count is :" + String.valueOf(list.size()));

Search leadSearch = new Search(Lead.class);
leadSearch.addFilterAnd(
    Filter.ilike("leadBasicDetails.fullName", "%gonzalo%"), 
    Filter.in("", list)
);

int count = leadDAO.count(leadSearch);
log.debug("Count is :" + String.valueOf(count));
return count;

The problem is that it takes a lot of time because it has to take all 
information from answerSearch (Survey module) and then inject into the query of 
the in. That's not optimum at all.

It would be great to have a way to do simple joins without the need of doing 
the query by hand. I cannot modify all the code now to do this jobs but I 
provide a little (Not well tested) improvement of IN filter.

Perform subqueries inside the IN whenever is possible. 

 This happens when result for one query has only one column and is of the same type of the IN attribute.

 So let's optimize it!

 I implemented a solution in which IN filter can handle a Search Object as parameter. Then the search query is included in the main one if data types permits it.

I changed the code in hibernate only. So it must be checked carefully and then 
ported to the other. I will review it in more detail and fix things because as 
I said before not much testing is done yet.

But results are great for now:

It only process about 3000 (query1) x3000 (query2) objects to obtain final 
result.

Old way: Result 18 records. It took 6186 milliseconds. That means 343 millis 
per record
New way: Result 18 records. It took 67 milliseconds. That means 3 millis per 
record
This provides a enhacement ratio of: 0.010830908503071451

This means that it only takes 1% of the time than it took before.

New way is as follows:

Search answerSearch = new Search(SurveyAnswer.class);

answerSearch.addFilterAnd(
    Filter.equal("idLot", "132")

);
answerSearch.addField("lead");

Search leadSearch = new Search(Lead.class);
leadSearch.addFilterAnd(
    Filter.ilike("leadBasicDetails.fullName", "%gonzalo%"),
    Filter.in("", answerSearch)
);

int count = leadDAO.count(leadSearch);
log.debug("Count is :" + String.valueOf(count));
return count;

The difference is here:
 Filter.in("", answerSearch) <- It takes the whole search instead of result.

Original issue reported on code.google.com by gaguilar...@gmail.com on 5 Sep 2010 at 4:48

[GoogleCodeExporter]

It's not a defect it's an improvement

Original comment by gaguilar...@gmail.com on 5 Sep 2010 at 4:49

[GoogleCodeExporter]

This is current patch. 

I will improve it as I said if it's a correct way of perform.
Also I'm thinking about doing something similar with the Filter EQUALS but 
doing joins between queries when they are simple.

Original comment by gaguilar...@gmail.com on 5 Sep 2010 at 4:59

Attachments:

• hibernate-generic-dao-20100905.patch

[GoogleCodeExporter]

Here is another way to accomplish this search without having to change the code:

Search leadSearch = new Search(SurveyAnswer.class);
leadSearch.addField("lead");
leadSearch.addEquals("idLot", "132");
leadSearch.addILike("lead.leadBasicDetails.fullName", "%gonzalo%");

int count = surveyAnswerDAO.count(leadSearch);
log.debug("Count is :" + String.valueOf(count));
return count;

Original comment by dwolvert on 1 Oct 2010 at 3:04

• Changed state: WontFix