Resolving multiple values returned from a query?

Question

While this seems like a really simple problem, the only solution I've come up with is below, any suggestions for something less ugly with lower time complexity?

My application is in Java and is retrieving with a MS-sql DB using skife.jdbi.

Say I have a Tablewith columns A, B, and C where A and B form a primary key. And I want to retrieve C given a particularly A and B. This is easily accomplished. But what if I my throughput requirement is very high, so I want to make these Select statements in batches. I end up with something like the below:

Given a Set of Objects with values A and B, I iterate the List compiling all the values of A and B. I then run a query like SELECT A, B, C FROM tbl WHERE A IN :listOfAs AND B IN :listOfBs. I then iterate the results of the query, matching a result to the original object by comparing the A and B value. Which all sounds reasonable, but the code ends up looking like the below, which seems ugly and suboptimal?

class MyObject {

  String A;
  String B;
  String C;
  Object otherData;


  @Override
  public boolean equals(Object other) {
    if (this == other) {
      return true;
    } else if (!(other instanceof MyObject)) {
      return false;
    } else {
      return A.equals(other.A) && B.equals(other.B);
    }
  }

  @Override
  public int hashCode() {
    return 31 * A.hashCode() + B.hashCode();
  }
}

// populates the of objects with their proper C value
void retrieveC(Set<MyObject> input) {
  Set<String> aValues = new HashSet<>();
  Set<String> bValues = new HashSet<>();

  for (MyObject object : input) {
    aValues.add(object.A);
    bValues.add(object.B);
  }

  // the dao executes the query mentioned above, and returns a Set of  
  // MyObject instances with members A, B, and C populated from the results.
  // Any results that do not contain a value for A, B, and C (which
  // shouldn't exit) are filtered out by the dao.
  Set<MyObject> results = dao.selectC(aValues, bValues);

  // ewww... O(n^2)
  for (MyObject object : input) {
    boolean resultFound = false;

    for (MyObject result : results) {
      if (object.equals(result)) {
        object.C = result.C;
        resultFound = true;
        break;
      }
    }

    if (!resultFound) {
      // handle this case
    }
  }
}

score 0 · Answer 1 · answered Dec 15 '15 at 22:01

0

I take it that ideal would be

SELECT A, B, C FROM tbl WHERE A+' '+B IN :listOfAandBs

That is not listOfAs x listOfBs (quadratic complexity) but listofAs . listOfBs (linear, in-product)

answered Dec 15 '15 at 22:01

Joop Eggen

107,315
7
83
138

score 0 · Answer 2 · answered Dec 16 '15 at 16:15

Instead of the DAO pattern you could turn to the fluent queries of JDBI.

This way you could switch batching to streaming. In the example given at the JDBI page you would exchange the StringBuilder() with something that allows you to stream your results to your receiver one by one as the database returns them.

If this is possible depends of course on your development environment.

Resolving multiple values returned from a query?

2 Answers2