What advantages does one big database query have over many small ones

Question

I inherited the app and what it does is get data from 4 views with an (xml file in it) in chunks of 1000 records then writes them down in an xml file all this split up by a type parameter that has 9 different possibilities. That means in a worst case there will be 36 connections to the database for each 1000 of that type/view combination.

The real data will exist of 90.000 lines and in this case 900 - 936 times fetching up to 1000 lines from database.

Now I am wondering what advantages it would give to read all data into the app and make the app work with this to write the 900+ files.

1000 lines is about 800MB, 90.000 lines is approx 81GB of data being transferred.

The code would have to be rewritten if we read it all at once and although it would make more sense this is a one time job. After the 90.000 lines, we will never use this code again. Is it worth it to spend 2, 3 hours to rewrite code that works to reduce the amount of connections this way?

Answer 1

If it's a one-time thing then why spend any effort at all optimizing it? Answer: no.

Let me add, though, in answer to your general question of what advantage does a big query have over lots of small ones: probably none. If you run a huge query you are leaving a lot of magic up to the middleware, it may or may not work well.

While having 36 simultaneous connections isn't optimal either, its probably better than running a query that could return 80 gigabytes of data. The ideal solution (if you had to use this code more than once) would be to rewrite it to get data in chunks but not leave lots of connections open simultaneously.