which is fastest win32 or SQL query?

Question

Hi,

I'm writing a client-server win32 application in Delphi 7 and in a section i need to bring aprox. 100k (less or more) rows with data from an Oracle database. Everything ok so far, but one of the fields must be calculated (a simple division with a large number).

My question is, how is less resources consuming and optimum, to make the division in the SQL query(maybe a store procedure), or to calculate the value in code for this field(on the server side)? I don't want to use a TDataset with calculated fields.

Oracle system(v 9.2) is also used by others applications, is not dedicated only to this application.

Thanks in advance.

Best regards,

Answer 1

The SQL Engines are designed for this type of task, so the answer is make the operation in the oracle system.

Always do the SQL data manipulation tasks in the database server, which is designed for that.

Answer 2

how is less resources consuming and optimum, to make the division in the SQL query(maybe a store procedure), or to calculate the value in code for this field(on the server side)?

Should make no difference. The bulkd of the time is going to be spend on calculating and transmitting 100.000 rows.

Answer 3

With such a scalar operation, the performance difference will be trivial. Do whichever is semantically more reasonable, or more convenient.

Answer 4

If the performance is the same, I would do it in the place where it would be most maintenance / configuration friendly. If the database requires a dedicated maintenance window (like Saturday at midnight) for metadata changes, I would choose a client- or middle-tier solution over a stored procedure.