I am working on a website that when put into production is likely to have thousands of new pages added each day and each page will need to be added to the sitemap. Now I am not asking how to add to the sitemap as the code for this is already well established, what I am asking is if it is better (or if there is a difference at all) to generate the sitemap file on a scheduled run (say every 2 hours regenrate the file) or simply generate it on the fly each time it is requested.
The way I see it if it is generated on the fly it has the advantage of being the most recent sitemap with all new content even if the content was only added a minute previously, but on the flip side it has the potential to take a while to generate (the sitemap generator would have to run queries on a dozen different databases).
Conversely if I used a scheduled run there would be the advantage that the sitemap would load just as fast as any other file that same size (no generation time needed), but the disadvantage is that it would potentially be up to 3 hours late which could equate to a few hundred pages not being in it.
Now from my point of view it would seem like it mainly depends on the system resources used to generate the sitemap file and how long each generation would take. Is there any hard limits on how long Google will wait trying to download a sitemap file before the download times out, and is there any generally accepted guidance on how frequently the sitemap should be updated.
I know the SO sitemap is a very large one with a massive number of records, this is very well documented in Meta, but I can't see anywhere that indicates if this is generated in real time or as a batch job.