1

I have two tables with different structure. I should compare ID from 1st table with ID of the intermediate table and then comapre TXT field of the intermediate table with TXT field of the 2nd table.

If there are missing records, or different values for TXT field in 1st and 2nd table I should add flag to the record in the 2nd table and recreate this record with additions from the 1st table.

I have two ways to do it. Using Java with JDBC or using SQL (or PL/SQL). But what way is correct for performance and future support?

closed as too broad by gnat, Kilian Foth, JeffO, Tulains Córdova, user40980 Feb 10 '15 at 9:47

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

5

Unless you have something which the application must supply all the time, I think it would be best to let the database related stuff be handled by the database itself.

This will allow you to have the DB to do all the heavy lifting, while allowing the application to remain as lightweight as possible so that it can handle whatever it is that the user needs doing.

EDIT: As per your comment.

From my experience, leaving all the calculations on the application, creates more problems then it solves. Unless the calculations you are doing belong exclusively to how will the data interact with the user, meaning that the application should handle all operations which deal with how will the data be presented to the user.

Doing any operations which strictly involves data, such as, get all the users with a given field, or as in your case, synchronization should be left at a DB level. This will allow you to:

  • Leave most of the heavy lifting at a DB level. DB's are usually good at what they do, so if you let them do their stuff, your application will be more lightweight (and most likely more responsive). This should in turn make the users happy because the application is not clunky.

  • Save resources: When you pull data from your DB, you are opening a connection and keep it open until you are done transferring data. Connections are expensive resources which should be used only when necessary. Pulling all the information in the DB so that you can operate on it, and then send it back means that the connection will be hogged for quite some time. If you have many applications using the DB, this could mean that the application will most likely not scale very well.

  • The above can lead to bandwidth issues if you have your application and DB running on different machines. This can make maintaining the application more expensive.

  • Having a stored procedure doing logic will provide you with better flexibility to apply changes. If you have the logic at a DB level, you can just make the change in one place to get the changes you need without having to issue a new application. This would then mean that you can be certain that all of your users are running the same logic, as opposed to having users running version n of the application, while others running version n + 1.

  • Yes, I think so. But I've heard that database should be container for data and all calculations should be done with a programming language. – Dracontis Feb 9 '15 at 8:08
  • @Dracontis: I have tried to provide more information in my answer. Please see if this helps shed light on the issue. – npinti Feb 9 '15 at 8:34
  • 1
    PL/SQL is a programming language – mcottle Feb 9 '15 at 8:50
  • @mcottle - yes, but it is often worth bearing in mind that it is a vendor-specific language that will tie you to one particular software stack, and one whose licensing terms can be pretty onerous at times, too. Keeping all your business logic in the same language and only using standard SQL for data access can improve your ability to make major changes to your environment later. – Jules Feb 9 '15 at 15:19
  • @Jules: I think that one needs to find a balance in between. DB Vendor changes do not happen often (at least in my experience), and although having logic in the application can improve application's ability to move, having code at a DB level usually boosts performance. – npinti Feb 9 '15 at 15:26
1

what way is correct ...

Being cynical just for a moment, none of them.

Try as we might; we never get it completely right. But we can try.

... for performance ...

Database.

For raw data processing power, leverage your DBMS. You don't say which one you're using but all the Big Players have some sort of procedural language (PL/SQL, T-SQL, etc.) that you can write code in, using the full power of the database and without those nasty network overheads.

... and future support?

And there's the problem.

You could write something that runs inside the database and is phenomenally fast, but if no-one else in the company knows that language, then you have a problem. It might be better to sacrifice that raw power for a program written in something more "mainstream"; except in a very few cases, maintainability should be your #1 priority, even at the expense of straight-line speed.

  • I think SQL is probably the most widely known programming language around today. Everyone who knows a language tends to know SQL too. – gbjbaanb Feb 9 '15 at 13:25
  • Strictly speaking, Structured Query Language is not a Programming language; it's a Query language. However, that hasn't stopped Our Friends in Redmond and Redwood City from "extending" them into full-blown programming tools. – Phill W. Feb 9 '15 at 13:33
  • Sure, its not a general-purpose language like C, but surely SQL is still programming, at least in popular usage (and IMHO) – gbjbaanb Feb 9 '15 at 13:38

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