Did you hear someone downloading a big file over web services?
But socket can not by-pass the firewalls
You will need to add exception to some other port, or write your socket on port 80, I would not do that. But this line of yours hints there is some scope of tightly coupled code with database.
, and we would lost much the benefits introduced by the web service.
This will be interesting based on what optimizations you prefer and need.
and use JDBC result set (in the web service method) to re-position and get the needed data.
You are getting all the records 1000 records x 10 times OVER NETWORK (JDBC uses sockets to get data from oracle).
re-positioning JDBC cursor? are you querying to get all the million records every time into ResultSet.
Another point you are forming a bulky HTTP request to send all of it to client.
So I visualize your code does.
while(100 times ){[Big oracle query 1000 records] -> [Big result set 1000 records] } -> [Big HTTP request object 1000 records that is sent to client from app server]
Remove that while loop. [BIG oracle query] -> [BIG result set] -> [BIG HTTP request object that is sent to client from app server] Now it looks like old times DB query and display on web page. But see there are no
bottle necks.
If you want to remove those
BIG from the above line resort to pagination.
Use
Oracle pagination queries if you are new to pagination. I could not find the original oracle's article regarding pagination but the resultant query looks something like below, I added the oracle HINTS which is missing in the below links.
Two links that i quickly found are here.
http://www.dba-oracle.com/t_sql_pagination.htm and
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
As Ivan said above especially in his second point some kind of pre-fetching, page scrolling optimization is something I would look into.
If you want to give client an impression that he is looking into a single page I would look for something like the google images search. I do not have experience in this, I can only suggest serarch on google for Continuous Scrolling/scroll pagination etc.
@Ivan, @Jim: That was my humble opinion, I did not want to disregard you opinion, I just wanted to suggest it might be better to deal with simplicity.