Win a copy of Functional Reactive Programming this week in the Other Languages forum!
    Bookmark Topic Watch Topic
  • New Topic

Problem with large number of results

 
Abdullah Khalaf
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Report post to moderator
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.
 
    Bookmark Topic Watch Topic
  • New Topic