Hi,

I have a question regarding the most efficient way of querying a
database with a program I'm writing.The problem boils down to this
(I'll illustrate with a simplified example):

I have a fragment table in the database e.g.

complex | frag_mass
----------------------------
1 | x
1 | y
2 | z
2 | y

(Read as complex 1 has fragments with masses x and y, complex 2 has
fragments with masses z and y)

I generate fragments in my java program (e.g. a, b, c) and I want to
compare the masses of these generated fragments against those in the
database table. My question is what is the most efficient way of doing
this? I have considered the following two approaches:

Approach A
-----------------
As each fragment is generated in the program I could fire off an sql
statement to the database and count the number of matches.
1. Generate fragment a
2. Send the following SQL to the database:
SELECT count(*) FROM fragments WHERE (mass of a) = frag_mass AND
complex = 1
3. Proceed the same with the next generated fragment, b.

Approach B
-----------------
Alternatively I could load the entire fragment table into the program
(hence into memory) using

SELECT * FROM fragments WHERE complex = 1;

This would load the entire fragment table into a Java ResultSet object
and I could then generate the fragments (a, , b, c) and call a search
algorithm.

Any comments on the merits of these approaches or improvements would
be mostly appreciated.

- Salman