TOP clause applied in @NamedQuery

Question

I have named query @NamedQuery(name="CustomerMarket.findByMarketId", query="SELECT DISTINCT c.marketid FROM CustomerMarket c WHERE c.marketid LIKE :mask") in my class CustomerMarket definition.

I want to take first 1_000 of 350_000 results so I applied setMaxResult:

Query market = session.getNamedQuery("CustomerMarket.findByMarketId");
market.setString("mask", getMask() + "%");
market.setMaxResults(1000);
List<Object> result = market.list()

After performing this query I checked server.log and found this

select distinct customerma0_.marketid as col_0_0_ 
from   CUSTOMER_MARKET_S customerma0_ 
where  customerma0_.marketid like ?

I have got correct data, first 1_000 records, but I want to know if there is TOP restriction applied and where.

How is this TOP clause handled?

1. TOP is applied into callable statement which is used by Hibernate and this is not displayed in log
2. TOP is applied after receiving result from DB and filters data on Java side
3. Or anything else

I have set up persistance.xml and log4j.properties for displaying almost everything, but I think that there is no problem, since I have got SQL script.

EDIT: I did some measurement of the performance in both cases (with and without setMaxResults) and got following times:

long time = System.currentTimeMillis();
market.list();
LOGGER.info("time: " + (System.currentTimeMillis() - time) + "ms");

In the first case without TOP I had average time consumption 1400ms per select. In the second case with TOP implemented I had average time consumption around 150ms.

It was also said to me, that this TOP implementation may be dependent on used Hibernate dialect (we are using Sybase). And the select which appears in log is exactly the same which is send to DB. But in this case is it normal that converting 350k results from resultset (or how is result stored in Hibernate session) into structure List without any metadata takes such long time? I can imagine that internally it is converted something like that:

**SQL generating process**
**SQL setting parameters**
ResultSet rs = executeQuery();
List<Object[]> result = new ArrayList<Object[]>(rs.getFetchSize());
int rowSize = rs.getMetaData().getColumnCount()
while (rs.next) {
  Object[] row = new Object[rowSize];
  for (int i = 0; i < rowSize; i++) {
    row[i] = rs.getObject(i);
  }
}

This does not seems to be so complicated, so why is there this time difference. Probably I am naive because Hibernate is complex framework with lot of features. :-D

Answer 1

It seems that my problem is in DB I use for my app. On the link www.petefreitag.com/item/59.cfm there is described how can user limit its number of results. As you can see, the only one Sybase has this kind of select divided into two separate actions (SET + SELECT). So the whole problem is that first action is not logged and second one is.