title: Fastest way of checking if one (or more) entries with given id is present in several tables
body: <p>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).</p>
  
  <p>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.</p>
  
  <p>In order to check that i was doing:</p>
  
  <pre><code>Boolean table1Ok = false;
  Boolean table2Ok = false;
  Boolean table3Ok = false;
  Boolean table4Ok = false;
  ...
  
  for (Foo instance : (Collection&lt;Foo&gt;) 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&gt;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
  }
  </code></pre>
  
  <p>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). </p>
  
  <p>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):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Now my question is: <strong>how could i improve speed when checking that for each Foo Object there's (at least) one entry in each of the seven tables?</strong></p>
  
  <p>If possible, i'd rather use Criteria than Hibernate HQL.</p>
  
  <p>Thank you very much,</p>
  
  <p>Guillem.</p>