Help! Our dynamic sitemap generation speed is tanking server performance and causing indexing headaches.

hey everyone, hope you're having a less stressful day than my server!

we've got a growing SaaS app, and with more users and content, our sitemap is getting huge. it used to be fine, just chilling in the background, but lately, generating the sitemap feels like watching paint dry... or maybe watching a snail try to win a marathon.

the sitemap generation process is painfully slow, often taking 5-10 minutes for around 100,000 URLs. it's like our server decides to take a coffee break, a nap, and then a long walk every time it tries to build the thing. during this time, server CPU and memory usage spike dramatically, sometimes even causing temporary timeouts for other services. not exactly ideal for user experience, you know?

• we're worried this slow sitemap generation speed is impacting our crawl budget and how quickly new pages get indexed. googlebot probably thinks we're just playing games at this point.
• i suspect database query inefficiencies or perhaps how we're writing the XML file are the culprits, but i'm not sure where to start digging deeper. it's a bit of a black box right now.

i've tried a few things, but nothing's really been the magic bullet:

• implemented some basic caching for static data, but it hasn't made a significant dent.
• did some light database query optimization, but clearly not enough to make a real difference.

so, what are the go-to strategies and tools for debugging and optimizing sitemap generation speed for large, dynamic websites? are there any common pitfalls or best practices I might be missing that could save my server from an early retirement? any advice on profiling these kinds of operations would be super helpful.

thanks in advance for any insights!