I have a CPU intensive stored procedure. It only runs occasionally at user request, and when it runs, my server is busy running the stored proc, and other users using the same DB notice a severe delay or they even get a "not responding" message on my Delphi front end.
I have some loops going on in the stored proc where I supposed I could put some "SUSPEND" statements but I just want to be sure about how SUSPEND works. There are two possible scenarios and I need to know which is true: a) SUSPEND is like a Application.ProcessMessages in Windows API/Delphi. Which means, I can throw in as many as I like in my stored proc and at any place. It will allow a chance for communication between calling client and server. (a.1: and will also not overload the server so much, allowing other clients to continue, perhaps with some delay, but not as bad as without the SUSPEND statements) b) SUSPEND is like EXIT, in that process flow "jumps out" of the stored proc at the point where it appears, and any lines following the SUSPEND do not get executed Which statement is true, and is sub-statement a.1 true? Thanks in advance for any help you can provide.
