wood burning stoves*
The moose likes Other JSE/JEE APIs and the fly likes Problem with large number of results Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Java » Other JSE/JEE APIs
Reply locked New topic
Author

Problem with large number of results

Abdullah Khalaf
Greenhorn

Joined: Dec 02, 2009
Posts: 12
Dears,
Hope this finds you well.
I posted this also in JDBC forum,
I have an issue and I need an architectural solution or opinion for this.
I have these three tables
1- Users table 2- Groups table 3- UserGroups table

The issue is that I have service allows users(these users are in Users table) to subscribe in specific groups (these groups are in Groups table), I managed this in UserGroups table.
The UserGroups contains two columns, group id (which is unique in Groups table) and user id (which is unique in Users table) -- one to many relationship --
The user table may contain over 1 000 000 of users.
The group id is number (1,2,....,8), so I use this SQL statement

I'm using this SQL statement to get the whole stuff as one record.
I'm using JDBC (prepared statement and result set), setting setFetchSize for both with specific parameter,
The return type is like this "Map<UserTO,List<GroupsTO>>", UserTO contains the details of the user, and GroupsTO contains a list of subscribe group.
The problem is when I execute this task, it takes very very long time and then a timeout exception thrown !
Any better solution? or any suggestion? from both sides the database and java code !
May environment is : Database: 9.5, application server: IBM - WebSphere Application Server 7, the task is hosted by EJB component as scheduler deployed in the application server.

Your quick help is highly appreciated.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Abdullah Khalaf wrote:I posted this also in JDBC forum.


Yes, that was a good choice. I see there is discussion there already. For those interested, here's a link to that other post: http://www.coderanch.com/t/587417/JDBC/databases/large-number-results.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem with large number of results