On 30-1-2013 19:41, red_october2009 wrote: > 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.
It is actually none of the above (it is almost a, but not entirely). SUSPEND signals that the current values of the output variables should be returned to the client. In essence any stored procedure that contains SUSPEND is a selectable stored procedure. Each call to SUSPEND produce a new row (with the current values of the output variables). Or as the Interbase 6.0 Language Reference (page 177) says: Suspends execution of a select procedure until the next FETCH is issued and returns values to the calling application. Available in stored procedures only. Syntax SUSPEND; Description The SUSPEND statement: * Suspends execution of a stored procedure until the application issues the next FETCH. * Returns values of output parameters, if any. A procedure should ensure that all output parameters are assigned values before a SUSPEND. SUSPEND should not be used in an executable procedure. Use EXIT instead to indicate to the reader explicitly that the statement terminates the procedure. Mark -- Mark Rotteveel
