Improving DataTable Performance

I was wondering if there is a way to improve the performance of dataTables.

Here's my scenario:

My application has a lot of data displaying data from really huge tables from the database (I'm talking about tables with millions of rows) and they also have a lot of relationships with other tables, so I need to be very careful when loading data from them, otherwise case my application will be rather slow.

My first step was to implement the lazyDataModel, so the dataTable can only load the data that is displayed on the current page, of course it was not an option to not do this, the dataTable won't even load if I try to fetch all the data, but even with lazy loading, it took a few seconds to load.

To cut load times a little more, I had to bring in only the columns used in the dataTable, so instead of writing a query like this:

SELECT t FROM Table t

      

        
        
        
      

    

I am writing this:

SELECT t.column1, t.column2, t.column3, t.column4  FROM Table t
//The four columns are only ones that are being displayed on the dataTable. 

      

        
        
        
      

    

The performance has been significantly increased due to this, now the data is loaded quickly enough, it takes only a few milliseconds to load.

Here is my lazyDataModel:

public class MyLazyDataModel<T> extends LazyDataModel<T> implements Serializable {

  @Override
  public List<T> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, String> filters) {
       DataTable dt = (DataTable) UIViewRoot.getCurrentComponent(FacesContext.getCurrentInstance());
       String whereClause = getfilters(filters);

       //Load the data
       List data = loadData("SELECT "+columns(dt)+" FROM "+ entity()+ " t WHERE "+ whereClause, first, pageSize);

       //Set the row count
       setRowCount(loadData("SELECT COUNT(t) FROM "+ entity()+ " t WHERE "+ whereClause));

       return data;
  }

  ...                
}

      

        
        
        
      

    

Everything seems to be working fine, but there is another problem:

Although the data takes a few milliseconds to load, I still need to do another query to set the row count and it still takes a few seconds to get it, Ive tried not to set it, but the dataTable was not showing any data.

I measured the time it took to set the number of lines and the result was 5438ms, I don't think it's worth taking that long to set this value. Does anyone have any suggestions on how to improve this?

I am using Primefaces 3.4.1, JSF and JPA2.