Re: Large query results threating in SessionBean

[Juan Pablo Lorandi]

Title: Message

Make your query paginable. For instance in mySQL or HypersonicSQL you could use LIMIT to err... limit the results of your query:

 

SELECT * FROM OILWELLS WHERE (somecondition)

 

would become

 

SELECT LIMIT n m * FROM OILWELLS WHERE (somecondition)

 

where "n" is the first row to retrieve and "m" the last.

 

Let's assume that you decided to show PageSize records per page, and you want to retrieve a desired Page. The statement:

 

SELECT LIMIT ((Page-1)*PageSize) ((Page)*PageSize) * FROM OILWELLS WHERE (somecondition)

 

should retrieve the results. eg: PageSize = 30, Page = 3

 

SELECT LIMIT 60 90 * FROM OILWELLS WHERE (somecondition)

 

The formula assumes that the Page number is 1-based (there is no page zero).

 

AFAIK neither Oracle nor SQL Server support LIMIT, but they support TOP; then the statement would be:

 

SELECT TOP PageSize NOT IN (SELECT TOP PageSize*(Page-1)) .....

 

or something similar. Recently I've had to do something similar with DB2, I used the built in OLAP functions to do so:

 

SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY NAME) AS RES_NUM, ID, NAME, altName, birthDate, deathDate, contactDetails, picture, publish, links FROM PERSON) AS PERSON WHERE RES_NUM > N AND RES_NUM <= M

 

at the end of the statement you can change N and M accordingly.

 

This is mostly a DBA problem. Since the query's you're trying to execute are probably very computationally expensive, you should get in touch with an experienced DBA, with skills in the specific of the RDBMS you've chosen, so you get qualified help.

 

HTH,

 

 

 

Juan Pablo Lorandi

Chief Software Architect

Code Foundry Ltd.

[EMAIL PROTECTED]

Barberstown, Straffan, Co. Kildare, Ireland.
Tel: +353-1-6012050  Fax: +353-1-6012051
Mobile: +353-86-2157900
www.codefoundry.com

 

Disclaimer:

 

Opinions expressed are entirely personal and bear no relevance to opinions held by my employer.

Code Foundry Ltd.'s opinion is that I should get back to work.

-----Original Message-----
From: A mailing list for Enterprise JavaBeans development [mailto:[EMAIL PROTECTED]] On Behalf Of Kirill Mikhailov
Sent: Friday, June 28, 2002 7:26 AM
To: [EMAIL PROTECTED]
Subject: Large query results threating in SessionBean

Hi,

My application works with oil&gas wells production data.
I have a SessionBean with couple of methods which queries very large RDBMS tables (up to 6-7 million records)
and get large results in custom serializable object (20 columns * 12 monthes * 10 year * thousand wells * tens layer * prod. methods... records * number of app. users). The result should be shown to the client as JTable and then saved to file in specified format (csv, excel, etc) on demand.

I. It is too expensive to hold the all contents of result in memory of EJB container before return it to the client, so I see only three ways to solve the problem:

1. StateFull SessionBean methods return partial result, for example for every N records, next call to method return next portion of data, but

I don't know how to avoid passivation of the statefull bean. When it will be passivated, I should close the JDBC connection, so ResultSet object will broke

and throw an exception.

2. Using JMS Queue
Session bean method execute query, getting data from ResultSet and periodically sending result object to queue.
Clients are listen for this queue and getting the result. It's seems complicated isn't it? How to restrict the clients receivers to get exactly what they requested?

I am afraid that all of the clients which listen to this queue, no matter what they requested in SessionBean will get large objects and overload the LAN.

Only today, I read about message selector when creating Message or QueueReceiver. Does it means when I sending the Message from the SessionBean, it sends only the message header (messageSelector) to all QueueReceivers and then send message body if exists the QueueReceiver for this messageSelector?

3. Using RowSet at the client
I am assuming RowSet class is just extended ResultSet class with Serialization. I guess it is holding connection to RDBMS or SessionBean

while its open, but I don't like this way just because I don't wonna make my client thick! That SessionBean doing calculations on every record, which I prefer to do on the server.

II. It is too expensive to hold all result in memory on the client and I'm going to do the TableModel class holding result data pageable to disk. So

MyTableModel.add() method will write data to file and getValueAt(x, y) will get data from buffer. May be you know another way?

Any ideas appreciated! :)

Thanx,
Kirill Mikhailov