• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

best way to iterate a result set..

 
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
I have a result set containing empId,MailId,and departmentNo. Total number of records in the resultset might be around 20,000. I need to send an email to all users depending upon their department. So if employee - A & employee - B are in same dept then they 'll receive the same mail. what will be the best approach to solve this ?
Approach 1 :
1. Keep all mailId's, departmentNo in a array list or any collection.
2. For each department
2.1 get all the mailid's that belongs to this department
2.2 send mail
3. Remove the processed mailId's from the collection so that, the number of iterations ( or records ) processed by next department gets reduced.
Approach2 :
1. Update the SQL query to get the results ordered by departmentno.
2. Iterate the result set and create a Arraylist having all mailid's
3. while iterating, if department number changes, then save the array list created in step2, to hashtable with departmentno as key.
4. After iterating, the last department will not be in the hashtable so add it.
5. Send mail to employees.
 
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Srinivasa,
I would go for Approach 2. I'm not really sure about performance, but at least it's more organized. Having to iterate over the departments list more than once in Approach 1 sounds messy.

Best regards,
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Would there be any value in moving the mail step (5) up into (3)? Instead of building a hashmap and saving it until you're done reading the resultset, mail the prior department as soon as you hit the new department ... and again at end. You might even be able to run the mailing on its own thread.

That would get the first mailing out sooner and avoid storing all the mail ids in the hashmap even for a short while. Don't know if it would get the LAST mailing out any sooner, tho.
[ January 25, 2006: Message edited by: Stan James ]
 
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd check your mail server to see if it can interface to something like an LDAP server to get lists of users whose departments are treated as roles in the JAAS sense. Map LDAP to your table and you just send as many pieces of email as you have different departments.
 
reply
    Bookmark Topic Watch Topic
  • New Topic