which is fastest win32 or SQL query?

Question

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.

Litta

What are you going to do with that data? Do you send them back to the DB or once on the client they are just manipulated there? As answered, mot of the time will be used to transfer that data. The real savings is if you may avoid to transfer that many row. – Anaemia

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.

Brookite

Delphi is also designed for that sort of scalar operation, so it can happen just as well there. It's not clear from the question whether the division makes any sense in a context other than the application, so I wouldn't frown on either approach. I might if I knew more. – Petronel

IMHO operations that not reduce the number of tuples returned are slower in DB than in Delphi. – Itacolumite

Answer 2

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

Petronel

Answer 3

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.

Wounded

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.

Zel

Very good point. While I agree with others that it would probably be more efficient to do it on the server, it may be much more practical to do it where you can quickly get your hands on it and make changes. In some environments, desktop changes are easy, and server changes (outside the normal release cycle) require a VP signature! – Mitre

Flip side: re-releasing the application to 3000 users if the calculation changes might be more hassle than getting the database change approved. – Barbarbarbara