There seems to be some sort of overhead involved with the initial running of a stored process in FS. I created a small stored process, and when I run it at the top level, it takes about 5 seconds. Next level - about 35 seconds. Next level - 220 seconds.
If I rerun the process immediately at the selected level, it goes back to the original 5 seconds or so.
This seems to be highly dependent on the level in the hierarchy, and the amount of data at that level. I've got some projects where the initial run of a stored process may take 3 hours or so, but an immediate rerun will take about 10 seconds.
I've tried to capture exactly where it is building in this overhead, but it all seems to be before anything in the stored process is actually run.
Does anyone know what is being invoked to cause this overhead, or how it can be overcome?
Do you see the same behavior for the sample stored processes which come with Forecast Studio? I guess your best bet is to contact Technical Support and work with them on tracking down the root cause of the effect you are experiencing.
Yep, I get the same thing with SAS provided stored processes.
What I'm wondering is whether for any stored process, it brings all the relevant data from that hierarchical level into temp tables in case it is needed for processing in the stored process. If that is the case, then I could understand things as we have a known issue with I/O for our FS data - it is being examined.