1

I have two tables stored on the DB server. I need to join these two tables and perform further calculations. I am sure that the calculations part should be done on backend server. But I am wondering if I should perform the join in the DB server and then bring the new table to the backend or if I should bring the two tables to backend and there perform operations replicating join (using ADO.NET) before continuing with the further calculations.

NOTE:

  1. Tables have few millions records.
  2. It will be great if you provide some references to support your answer.
2

JOINing is an important, standard, operation in SQL. It is possible, but not that standard in app languages. Do it in SQL.

Ditto for other operations, such as aggregation (eg SUM with GROUP BY).

Ditto for sorting (ORDER BY) and limiting (LIMIT).

Also, consider the amount of data that might need to be transferred. In most example like the above, less data need be shoveled to the client. This is another argument for doing the work in SQL.

Another argument... SQL can handle a 1-row table or a billion-row table. It does not care. It will probably use totally different code for each (RAM-based code versus caching versus disk-based). Do you really want to compete with that level of sophistication?

SQL loves doing "set-based" operations; client code loves tedious 'for'-loops. Why write a for-loop when a single SQL statement will do the 'for' and the stuff inside it?

Use SQL for what it good at.

In rare cases, some tasks are better done in client code. Your Question comes nowhere near such.

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.