Displaying Large Volume Of Records With JAX-RS

[Prabhash Mishra]

Hi,
   I developed Restful web service for Big publishing company which takes online book orders for schools,colleges and individuals.In the case of school and colleges the number of items could be in thousands.I the case of thousands of items to display they are asking for response time less than 1 sec.Currently i am using JNDI lookup with Connection pooling to server the @GET request.In current QA database we don't have customer having thousands of orderitems.We do have 50-60 items order.But in this case it is taking time 10 sec. Can anybody tell me what i need to do in jdbc context to display thousands of records since client is not asking for paging.Please give me some idea how to handle large records using jdbc.

Thanks,
Prabhash

[Tim Moores]

No human being can (or will want) to view thousands of records at a time. There may be a redesign required to come to a usable request/response/data size mix. Maybe load and display the first 20 records, and then load the next batch of 20 in the background to enable quick forward/backward movement in the UI.

Asking for a subsecond response time for thousands of items over a network with unknown characteristics (at least that's what I'm assuming you're talking about) is unrealistic. You need to push back on that.

10 seconds to retrieve 60 items is way too long. Put some logging and debug code in place to determine what's taking up all that time.

[Prabhash Mishra]

Hi,
  Thanks for your response.Since Sql query is written by other database guy so there is possibility of not optimized query since query is retrieving data from several tables.Can you confirm using CallableStatement has any advantage over preparedStatement as i am using preparedStatement to execute query.Also JAX-RS is producing XML as output not JSON.Can you confirm that producing XML as output is slower comparing to JSON.

Thanks,    

[Jesper de Jong]

As Tim says, it is rarely useful to return a list with thousands of results, since nobody is going to look at everything in such a long list anyway.

A solution that is often used for this is pagination. Instead of returning a list with 1000 results, return just the first 50, for example. Then allow the user to specify with query parameters on your REST webservice which "page" of results (s)he wants to see. For example, you can do a request where you specify that you want to start at row number 51, and then the service returns results 51-100, etc.

[Tim Moores]

Since Sql query is written by other database guy so there is possibility of not optimized query since query is retrieving data from several tables.

A slow DB call could be the cause of the problem, but then, so could something else. Without measuring where the time is spent, you'll never know.

Can you confirm using CallableStatement has any advantage over preparedStatement as i am using preparedStatement to execute query.

It's highly unlikely that either will make an appreciable difference.

Also JAX-RS is producing XML as output not JSON.Can you confirm that producing XML as output is slower comparing to JSON.

That choice, too, is unlikely to make much difference, IMO. But it's easy to test and time, so you know for sure.