5

I am dealing with a pretty extensive data set. After a few joins, the foundation table has something like 40 columns. The next 40 columns will all be calculated. I am trying to keep my Java clean of the business logic because it is fairly verbose, at times difficult to follow, and not necessarily belonging to a single model. In short, I think that because of the sheer magnitude of business logic, Java may be difficult for future users to follow.

On the flip side, I have tried doing the business logic in SQL. However, suppose I have columns A, B, C, D, where A and B are from the foundation table.

C = A + B
D = C + B

Now because D is dependent on C, this requires either a nested select or a with clause, and as a result also becomes very messy very quickly.

Recently my coworker introduced me to cursors, not something I have much experience with, but they seem ideal for this use case. I do a select...join... to retrieve a fairly large foundation view, then I use the cursor for that view to loop through each row and calculate all the additional columns. In this way, I can do D = C + B much more easily. There will still be a fair amount of noise here, however it feels as if my business logic will be centralized here. At the end of all calculations, I will simply insert the values as a row into some table, then my Java can query that table.

Some preliminary reading tells me that cursors are not adviseable as they introduce additional overhead. However, if all I am doing is purely arithmetic operations to populate a number of columns, is the user of a cursor valid? I have written some of the code and it is easy to follow and centralized.

I get that I can do the same in Java, but I would rather have the fully calculated table available in my DB, and it feels like the roundtrip through Java is unnecessary because the cleanliness of the code there will not be much better.

EDIT: I'd like to include also that although the column span is fairly large, the expected amount of rows is actually not that great, it is somewhere between 1-2,000. To that effect, an INSERT in a loop seems like it wouldn't be too bad.

  • I can't imagine this looking cleaner in the db and definitely won't if you use a cursor. – JeffO May 3 '16 at 0:40
  • 1
    There are still valid use cases for cursors in SQL programming. The reason you try to avoid cursors is because you want to be sure that iterating over the entire record set is truly necessary. Any efforts you make to separate SQL code from your java code will make your code more maintainable. – Thomas Carlisle May 3 '16 at 0:46
  • @JeffO it is cleaner in the sense that I am using a single LOOP and you can clearly see line items such as D := C + B. This is way better than nested SELECT FROM SELECT (readability) statements and a little less verbose than using WITH as @kevin suggested, though it seems that WITH,,, syntax is recommended – mike May 3 '16 at 13:17
10

I would use a series of with clauses:

WITH some_stuff AS (
  SELECT ...
  FROM ...
  INNER JOIN ... ON 
  ...
),
some_stuff_with_c AS (
  SELECT *,
    a + b AS c
  FROM some_stuff
),
some_stuff_with_d AS (
  SELECT *,
    b + c AS d
) ...

and wrap it all up in a view. I think this is pretty easy to understand, It's just a list of column definitions. Using SELECT * eliminates the repetition. If you have unneeded intermediate values, you can drop them in the final SELECT. This seems far simpler than using a cursor.

  • I am not quite familiar with the inner workings of with but it seems like every time I rack up an additional calculation, I am doing a SELECT *; are there optimizations happening with this syntax? I'm still very used to Java and it seems that with the explicit cursors only one iteration through the data set occurs, whereas with with it seems like there is one for every additional column calculated – mike May 3 '16 at 13:12
  • 1
    Don't think of SQL statements as "doing" something, but rather describing what data you want. It is the query engines job to give you the data you ask for in whichever way it thinks is fastest. – Caleth May 3 '16 at 13:18
  • Having written out some boilerplate using both cursors and the suggested syntax above, I can say that the WITH is not much messier than the cursors, and if it gives me additional performance optimizations I'll take that. – mike May 3 '16 at 14:43
  • @mike: The query engine collapses all this just as you would expect. For Transact-SQL, the query plan has a single "Calculate Scalars" block that takes almost no time. – kevin cline May 3 '16 at 17:34
1

Cursors can be the preferred solution, but more often than not, they're [ab]used for logic that would be better implemented using joins, case statements, whiles etc.

You state this logic:

C = A + B
D = C + B

But is D really dependent on C? Could you not write all the derived columns as a series of long hand statements e.g.

C = A + B
D = A + B + B

If you can, it is questionable as to whether a cursor is really the solution here.

  • While I agree that I can write it as above, I want to emphasize the sheer column count. If we continue this on for E, F, G in a similar manner, you will end up with: E = D + C = A + B + B + A + B, F = E + D = A + B + B + A + B + A + B + B You get the idea. In this case, yes, it is simply addition, but my arithmetic also includes some more complicated logic around NVL, ABS, a few cases, so this can quickly can repetitive and messy – mike May 3 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.