wood burning stoves 2.0*
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 Java 8 in Action this week in the Java 8 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: 18155
    
    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
 
Similar Threads
ManyToMany assoicate delete join table entry
Do I re-use existing declarative role-based data structures for user authorization?
Problem with large number of results
problem persisting an object with collections and manytomany relationship
Convert SQL to Criteria please