Map vs quering DB?

[nirajj]

Hi all,

The program that I am working on requires that each employee's value be checked against a table.

The 2 different approaches that I have in mind are...
1) Pull up values for all the employees ahead of time and store in a HashMap. (about 12000 employees)
2) Query the DB each employee at a time.

I am using the 1st method. Populate the HashMap and lookup value for each employee in the HashMap.

But I was wondering which method would give me better performance in terms of memory and execution? Any idea?

Any other ways to code that in?

All inputs most welcome... Thanks!

 

[Diancecht]

If you're plannig to check all values, I think the most efficient way is a stored procedure.



Cheers,
Dian

 

[nirajj]

Diancecht,

Unfortunately the type of work I am doing, I am not allowed to add any objects in the DB. That violates the contract.

So the only solution I have is through the java.

 

[Canelas]

I think i'd rather do a large query than 12000 small ones.
You you narrow it down to a query, you'll be doing only one connection to the DB. Meanwhile, if you do 12000 ones, it'll be kind of like "spamming" the DB.

At least that's what i think.

 

[Diancecht]

That strongly depends on the JDBC driver and the way you manage the created connections.

If you're plannig to check all values in a row, then I'd consider taking all data but if you're doing one at a time ...

Cheers,
Dian

 

[nirajj]

I tried both the ways and I was able to cut 20 secs off the total processing by pulling up all the data beforehand than single query for each employee.
Thanks for all the inputs!