Optimizing Sql Transactions (large single transaction vs many small ones)

Question

I'm working on a webserver. I can have an endpoint that compiles data in multiple transactions, or all in a single transaction. Which would be faster?/ Better?

Answer 1

The answer: It depends on the amount of data you would expect your database to return.

A: A lot of data being returned (Thousands, millions): Suppose you are doing the next Facebook. If you are about to fetch a really enormous amount of data (2 millions of email addresses) it would probably be better to use some kind of "pagination" and fire a query every few seconds or minutes. You wouldn't want a query which waits for 10 minutes in order to get your results and keep the entire server busy.

B: Small or moderate amount of data being returned Or, if you are about to fetch some moderate amount of data (300 cities, 523 employees and 43 phones) then you wouldn't want wasting transaction times by executing a separate SQL query for cities, employees and phones and try to use as few separate queries, as possible. This means probably using a lot of JOINs.