-1

This is more of a design/architecture question though can easily find its way in code as well. When improving the performance of a Java application deployed on app server talking to database to retrieve records, what is the best methodology?

Improve performance on the database layer by tuning SQL, ORM, any other form of tuning OR at Java code chosing best collections based on data, tuning code etc.

For one real-life scenario I had a requirement to parse some data from a BLOB looking for some attributes.

One option was to get all of data from dB and iterate through list looking for specific attributes thereby filtering the records;

Other option is to limit the data from DB by using complex queries/Stored procs and building REG Ex using Oracle's functions on BLOB.

Would like to know some practical pointers from real-world problems. We are talking about Millions of rows here for data I am referring to. I did resort to filtering using DB layer but still have not tested well with very large datasets.

Edit:

The question is Simple: This app/module has a front-end and a back-end which talks to Oracle. Need to retrieve and filter data from a BLOB containing Java serialized object. What is more efficient?

Parsing BLOB with the RAW function and building Regular Expressions (which can fail due to changing data in BLOB) OR Get Data via JDBC (API builds on top of some persistence layer using hibernate) and then filter it in Java layer.

This is a real life problem with a working app. I even discussed with a DBA and was suggested to filter it in Java layer.

Reference Questions:

How can I avoid "raw variable length too long" errors in SQL Developer?

How do I get textual contents from BLOB in Oracle SQL

Community
  • 1
  • 1
Rockoder
  • 746
  • 2
  • 11
  • 22
  • I posted a questions since I ran into this actual scenario. I am not sure how else I could have made the question Legit to get meaningful responses. I have seen far too broader questions than this. – Rockoder Dec 21 '15 at 20:24
  • For the people who downvoted. I was thinking of a question today and found a similar question very similar to what I had in mindhttp://stackoverflow.com/questions/17343157/static-method-behavior-in-multi-threaded-environment-in-java No one down voted the question and in fact there are about 566 replies to this question. – Rockoder Dec 22 '15 at 06:23
  • The best way to utilize the database is to move out the attributes that are searched into columns. Actually, you should get rid of the blob and move as much as possible to columns and then use JPA. Then add indexes on the fields in the database, and use QueryDSL to write nice queries in java. – KarlP Jun 18 '16 at 18:33

1 Answers1

1

Just today I was trying to find speedups in an app I had written. Just like you, I had prior guesses about what the sources of slowness could be.

In particular, I had assumed the app was I/O bound, so little could be done. I was wrong.

I used the method I always use, and found that only about 40% of time was in I/O. The remaining 60% could be improved, and was, resulting in about a factor of 2 speedup, after which it actually was I/O bound.

What's the take away? There's no harm in having prior guesses of what needs to be fixed. There is only harm in believing them.

Community
  • 1
  • 1
Mike Dunlavey
  • 40,059
  • 14
  • 91
  • 135