(My environment: Windows 7 x64 and Server 2008, EclipseLink 2.5.2, I've tried the following with JTDS and the MS JDBC driver, and with MS SQL Server Express 2008 and 2012.)
I'm doing a paginated query in a table with anywhere from 5-50 million rows, with about 5 million rows matching the filter criteria. The following is the paginated query I'm using to display this data in the UI 25 rows at a time. The pagination is working properly - the list contains just 25 rows for each page. However, the query takes 24 seconds to return 25 rows, which seems long.
My goal is to log the generated SQL, so I can see exactly how JPA is accomplishing the pagination in SQL Server 2008 vs 2012. But the generated SQL doesn't include anything to do with pagination, which makes me wonder what else I'm not seeing in the generated SQL.
The query:
CriteriaBuilder cb = JPA.em().getCriteriaBuilder();
CriteriaQuery<RGHICarrierPull> cq = cb.createQuery(RGHICarrierPull.class);
Root<RGHICarrierPull> from = cq.from(RGHICarrierPull.class);
CriteriaQuery<RGHICarrierPull> select = cq.select(from);
// Add filter/sort predicates to "predicates"
...
select.where(predicates);
// Apply pagination
records.setFirstResult((page-1)*limit);
records.setMaxResults(limit);
// Get data
List<RGHICarrierPull> lst = records.getResultList();
To log the generated SQL programatically:
// Log the sql for this query
Session session = JPA.em().unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery = ((EJBQueryImpl)records).getDatabaseQuery();
databaseQuery.prepareCall(session, new DatabaseRecord());
System.out.println(databaseQuery.getSQLString());
The logged SQL:
SELECT t1.SHIPTO_ZIP, t1.WHSE, t1.ANYNBR1, t1.ANYTEXT1, t1.CREATE_DATE_TIME, t1.
MOD_DATE_TIME, t1.PULL_TIME, t1.PULL_TIME_AMPM, t1.PULL_TRLR_CODE, t1.USER_ID,
1.SHIP_VIA FROM Ship_Via t0, RGHI_Carrier_Pull t1 WHERE ((t1.WHSE = 'WHSE1') AND
(t0.SHIP_VIA = t1.SHIP_VIA)) ORDER BY t0.SHIP_VIA ASC, t1.SHIPTO_ZIP ASC
Obviously, this is not a paginated query, so if I run this query directly, it runs for over a minute and returns all 5 million rows. I get the same results if I use persistence.xml settings to log all JPA queries, and also if I log the SQL from MS SQL Server.
Is this the actual generated SQL? I see two possibilities:
- This is the full generated SQL, but EclipseLink is doing something else to accomplish the pagination.
- EclipseLink is logging this generated SQL prior to the pagination stuff being added to it
