How to implement long running queries on IdhttpServer?

Question

What is good way to implement long running queries on IdHttpServer. I have written simple logic to do so, please advise suggest better way to achive the same as I'm struggling with its performance. I am using D2010 and Indy 10.5.8 to achieve the goal, also suggest if we retrive values frequently from session will that be a resource intensive ?

procedure TForm1.ServerCommandGet(AContext: TIdContext;
  ARequestInfo: TIdHTTPRequestInfo; AResponseInfo: TIdHTTPResponseInfo);
var
   SessionObj : TSessionData;
begin
 if ARequestInfo.Document = 'EXECUTEQUERY' then
 begin
   if not Assigned(ARequestInfo.Session.Content) then
   begin
      SessionObj := TSessionData.Create;
      ARequestInfo.Session.Content.AddObject('U_SESSION', SessionObj);
      SessionObj.RunLongQuery;
   end;
 end;

 if ARequestInfo.Document = 'GETDATA' then
 begin
   SessionObj := TSessionData(ARequestInfo.Session.Content.Objects[ ARequestInfo.Session.Content.IndexOf('U_SESSION')]);
   if SessionObj.GetQueryStat = Done  then
    begin
       AResponseInfo.ContentStream.CopyFrom(SessionObj.GetMemStream, SessionObj.GetMemStream.Size);
       SessionObj.GetMemStream.Clear;
       AResponseInfo.ResponseNo := 200;
    end else if SessionObj.GetQueryStat = Error then
        AResponseInfo.ResponseNo := 500
    else AResponseInfo.ResponseNo := 102;
 end;

end;

procedure TForm1.ServerSessionEnd(Sender: TIdHTTPSession);
begin
  TSessionData(Sender.Content.Objects[ Sender.Content.IndexOf('U_SESSION')]).Free;
end;

{ TProcessQuery }

constructor TProcessQuery.Create;
begin
  myConn := TMyConnection.Create(nil);
  myConn.LoginPrompt := False;
  myConn.UserName := 'UserName';
  myConn.Password := 'Password';
  myConn.Server := 'Host';
  myConn.Database := 'DBName';
  myConn.Connected := True;
  myQuery := TMyQuery.Create(nil);
  myQuery.Unidirectional := True;
  myQuery.Options.CreateConnection := False;
  myQuery.Connection := myConn;
  Fstat := None;
  Fstream := TMemoryStream.Create;
end;

destructor TProcessQuery.Destroy;
begin
  if Assigned(myConn) then begin
    myConn.Close;
    myConn.Disconnect;
    FreeAndNil(myConn);
  end;
end;

procedure TProcessQuery.ExecuteQuery;
begin
  Status := Started;
  myQuery.SQL.Text := '<Some Query>';
  myQuery.Open;
  try
    try
    while not myQuery.Eof do
    begin
         Status := Inprogress;
         //Add to FStream which would be returned to user.
    end;
    except
        on Exception do
        Status := Error;
    end;
  finally
    myQuery.Close;
  end;
end;

{ TSessionData }

constructor TSessionData.Create;
begin
  FProcessQuery :=  TProcessQuery.Create;
end;

function TSessionData.GetMemStream: TMemoryStream;
begin
  result := FProcessQuery.Fstream;
end;

function TSessionData.GetQueryStat: TStatus;
begin
  result := FProcessQuery.Status;
end;

procedure TSessionData.RunLongQuery;
begin
  FProcessQuery.ExecuteQuery
end;

Answer 1

You are running the actual query in the context of ServerCommandGet(), so the client will not receive a reply until the query has finished. For what you are attempting, you need to move the query to its own thread and let ServerCommandGet() exit so the client gets a reply and can move on, thus freeing it to send subsequent GETDATA requests. In ServerSessionEnd(), you will have to terminate the query thread if it is still running, and free the TSessionData object.

There are some other problems with your code as well.

ServerCommandGet() is checking for not Assigned(ARequestInfo.Session.Content) and then calling ARequestInfo.Session.Content.AddObject() when ARequestInfo.Session.Contentis nil. I don't see any code that is creating the ARequestInfo.Session.Content object.

If the client issues multiple EXECUTEQUERY requests, you are storing them all in AResponseInfo.Session.Content using the same name, 'U_SESSION'. GETDATA will return the results of only the first query it finds, and ServerSessionEnd() only frees the first query it finds. So either give each query a unique name and send that back to the client so it can include it in GETDATA and make ServerSessionEnd() loop through the entire Sender.Content, or else do not allow multiple queries in the same HTTP session. If the client issues a new EXECUTEQUERY while a previous query is still active, kill the previous query before starting the new one.

When the client issues GETDATA, the code needs to take into account that the requested query may not exist, such as if it previously expired and was freed by ServerSessionEnd(). Also, if a query does exist and has finished, you are calling AResponseInfo.ContentStream.CopyFrom() but AResponseInfo.ContentStream is nil when ServerCommandGet() is called. You are responsible for providing your own ContentStream object. So either take ownership of TSessionData's memory stream and assign it as the AResponseInfo.ContentStream object, or else create a new TMemoryStream to copy into and then assign that as the AResponseInfo.ContentStream object. Either way, TIdHTTPServer will free the AResponseInfo.ContentStream after sending it to the client.