Hi,
I was trying to reply to Erland but got a message about exceeding 1K, which is weird, so I put it as a separate reply to myself.
Hi Erland,
I may need to find some time to re-read this long article again. For now my colleague identified one missing index, when we added it, the performance improved significantly. The longest run of this complex procedure is ~4 min. although we haven't hit a huge number of rows yet.
However, here is what I noticed - it seems to me that the first run of the day may be the slowest regardless on the number of rows to process (say, today it's 4 min. for only 50 rows). The rest of the day it usually takes under a minute for a bigger number of rows. I'm wondering if there is a way to speed up this initial run. Although 4 min. for the very first run is still OK and much better than several hours we used to get for lots of rows.
I also made some extra changes and going to show them to my colleague (they are minor and I don't expect them to make any impact, but I did split that original query into 2 using the temp table - I don't believe it'll make matters worse - but not sure if it's necessary too based on our current performance).
Thanks.