Forums Register Login

Problem with large number of results

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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: https://coderanch.com/t/587417/JDBC/databases/large-number-results.
I am not young enough to know everything. - Oscar Wilde This tiny ad thinks it knows more than Oscar:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


Reply locked
This thread has been viewed 773 times.
Similar Threads
problem persisting an object with collections and manytomany relationship
Convert SQL to Criteria please
Do I re-use existing declarative role-based data structures for user authorization?
ManyToMany assoicate delete join table entry
Struts2: My s:select will not select rows which it should do applicable
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 19, 2024 00:01:04.