Fastest way of checking if one (or more) entries with given id is present in several tables

In my application DB, for a given Foo Object with a String id, there are seven tables, each one contains information about Foo Object, and each of the 7 tables has a field with the String id of the Foo Object (as well as many more fields with the stored data).

In a quality check method i need to know which id/s (Foo Object instances) do not have at least one row (one entry) in at least one of the seven tables.

In order to check that i was doing:

Boolean table1Ok = false;
Boolean table2Ok = false;
Boolean table3Ok = false;
Boolean table4Ok = false;
...

for (Foo instance : (Collection<Foo>) instancesToCheck)
{
   // for table 1
   try{
  Criteria crit = session.createCriteria(mappedObject1);
  ProjectionList projList = Projections.projectionList();
  projList.add(Projections.count("id"));
  crit.setProjection(projList);
  crit.add(Restrictions.eq("id", givenId));
  Long count = crit.uniqueResult();
  if (count>0){
    table1Ok = true;
  }
    }
catch ( HibernateException he){
    return new Long(0);
    }

   // table 2 similar code to table1
   // table 3 similar code to table1
   // table 4 similar code to table1
   // table 5 similar code to table1
   // table 6 similar code to table1
   // table 7 similar code to table1
}

The problem is that the approach above is really slow, for my application each table row count takes a bit more than 1 second, so around 7-9 seconds to check one Foo Object (all 7 tables).

I suspected/hoped it might be due to the overhead of creating Criteria for each table, and checked if using the following SQL sentence i could get any faster (but i couldn't):

select count(table1.id) as table1, 
       count(table2.id) as table2, 
       count(table3.id) as table3,
       count(table4.id) as table4, 
       count(table5.id) as table5, 
       count(table6.id) as table6,
       count(table7.id) as table7 
from table1, table2, table3, table4, table5, table6, table7 
where table1.id="GIVEN_ID" and 
      table1.id=table2.id  and 
   开发者_高级运维   table1.id=table3.id  and 
      table1.id=table4.id  and
      table1.id=table5.id  and
      table1.id=table6.id  and
      table1.id=table7.id;

Now my question is: how could i improve speed when checking that for each Foo Object there's (at least) one entry in each of the seven tables?

If possible, i'd rather use Criteria than Hibernate HQL.

Thank you very much,

Guillem.

If you have mapped each of these associations as OneToOne JPA associations between entities, you could use the following query (in HQL or criteria):

select t1.id, t2.id, ... from Foo f
left join f.table1 t1
left join f.table2 t2
...
where f.id = :theId

Then test if the returned array contains the ID or null for each table.

If these associations are not mapped as JPA associations, you could use a native SQL query doing the same thing as the HQL query above:

select t1.id, t2.id, ... from Foo f
left join table1 t1 on f.id = t1.id
left join table2 t2 on f.id = t2.id
...
where f.id = :theId