zeos connections on servermodule

[glendean]

I am wanting lightweight access to postgresql without the bde layer (I am not using data controls).  I looked at mORMmot but found it too complexing and restrictive so instead created a thread safe connection class with an Array of zeos IZConnections that I plan to place on servermodule.  The clientclass which sits on mainmodule passes the sql request and a pointer to its array (dataset) to a threadsafe class of IZConnections on servermodule.  Thanks to the performance of zeos, under test in a delphi app, it completed 12,000 sql requests a second to 500 threads via 20 connections to postgresql (24 sql request per second per thread) in an oracle vm.  

 

My thoughts are that 10 or so IZConnections placed on servermodule should easily serve 500+ unigui sessions. Thus saving valuable resources (no Data.DB). I name form tconrols with a similar name to database fields so can update forms with one function call.... DBSetFormControls(self,  SQL) etc. so don't need no dammed bde

 

So my question is this. Is placing a class of zeos IZConnections on servermodule a bad idea for any reason? 

 

Note.... Speed of unigui through the www (via vpn prozy) using zeos IZConnections without bde is simply outstanding.  Saving my bickies so I can rid myself of the unigui trial banner soon.

[Farshad Mohajeri]

Hi,

 

If you can manage all these in a thread-safe manner then your approach should work. You need to ensure that no connection is used concurrently by more than one session.  Actually it is called connection pooling. You may also want to try using a DB library which internally implements a pool system.

[glendean]

Hi,

 

If you can manage all these in a thread-safe manner then your approach should work. You need to ensure that no connection is used concurrently by more than one session.  Actually it is called connection pooling. You may also want to try using a DB library which internally implements a pool system.

 

Thanks Rafashad for the reassurance and headsup, 

 

Cheers, Glen

[glendean]

Thanks Rafashad for the reassurance and headsup, 

 

Cheers, Glen

 

If anyone is planning to roll their own connection pooler on servermodule the following code using the zeos library completes over 12,000 sql requests a second from postgresql. In my case reducing the unigui app exe size, without bde, by a third. Data is temporarily stored in a array of array of variant (aka TUniDBData) which can be accessed from a tquery like replacement residing on forms or the mainmodule. Performance across the web is excellent as long as you instigate a request for data from clientevents. Please advise if I have goofed anywhere.

 

Function TUniDBConnection.ExecuteQuery (Const Sql: String; var fData: TUniDBData; var ErrorMessage: String; Const NoResult: Boolean): Boolean;

Var

  ConnectionID,Colind,rowind: Integer;

begin

    Result := False;

    if Length(fConnections) = 0 then

    begin

        ErrorMessage := 'System is not connected to a database. Operation aborted.';

        Exit;

    end;

    ConnectionID := -1;

 

    // Stress tested with 400 threads executing over 6 miilion sql request in total through 20 connections.

    // Throughput with 20 connections was 15,000 sql requests per second.

    // Typical average responce in real world should be 1-25 ms with 10 connections depending on load (10 conn = ~13,000 requests second)

 

    Try

        EnterCriticalSection(fGate);

        while ConnectionID = -1 do

        begin

            ConnectionID := SelectFreeConnection;

            If ConnectionID = -1 then

            begin

              resetevent(fWaitSignal);

              WaitForSingleObject(fWaitSignal, 10);

            end;

        end;

        InterlockedIncrement(fQueueBusy[ConnectionID]); //atomic operation.

    Finally

        LeaveCriticalSection(fGate);

    End;

 

    Try

        Try

            if NoResult = True then

                Result := fStatements[ConnectionID].Execute(SQL)

            Else

            begin

                fResultSets[ConnectionID] := fStatements[ConnectionID].ExecuteQuery(SQL);

                if Assigned(fResultSets[ConnectionID]) then

                begin

                    Result := True;

                    SetLength(fdata,fResultSets[ConnectionID].GetMetadata.GetColumnCount, 2);

                    for ColInd := 1 to fResultSets[ConnectionID].GetMetadata.GetColumnCount do

                    begin

                        fdata[Colind-1, 0] := fResultSets[ConnectionID].GetMetadata.GetColumnLabel(Colind);

                        fdata[Colind-1, 1] := fResultSets[ConnectionID].GetMetadata.GetColumnType(Colind);

                    end;

                    rowind :=1;

                    while fResultSets[ConnectionID].Next do

                    begin

                        inc(rowind);

                        SetLength(fdata,Length(fData), rowind + 1);

                        for ColInd := 1 to fResultSets[ConnectionID].GetMetadata.GetColumnCount  do

                        begin

                            case fResultSets[ConnectionID].GetMetadata.GetColumnType(ColInd) of

                                stBoolean: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetBoolean(ColInd);

                                stByte: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetByte(ColInd);

                                stShort: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetShort(ColInd);

                                stInteger: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetInt(ColInd);

                                stLong: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetLong(ColInd);

                                stFloat: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetFloat(ColInd);

                                stDouble: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetDouble(ColInd);

                                stBigDecimal: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetBigDecimal(ColInd);

                                stBytes, stBinaryStream: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetBlob(ColInd).GetString;

                                stDate: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetDate(ColInd);

                                stTime: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetTime(ColInd);

                                stTimeStamp: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetTimeStamp(ColInd);

                                stAsciiStream, stUnicodeStream: fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetBlob(ColInd).GetString;

                            else

                                fdata[ColInd-1,RowInd] := fResultSets[ConnectionID].GetString(ColInd);

                            end;

                        end;

                    end;

                end;

            end;

            Except on E: EzSQLException do

            begin

                SetLength(fdata,0, 0);

                ErrorMessage := 'Sql Error ' + Inttostr(E.ErrorCode) + ' : ' + E.Message;

                Result := False;

            end;

        End;

    Finally

        Interlockeddecrement(fQueueBusy[ConnectionID]); // atomic operation.

        SetEvent(fWaitSignal);                         // atomic operation.

    End;

End;

 

Function TUniDBConnection.SelectFreeConnection: Integer;

var

  i: integer;

begin

  Result := -1;

  for I := 0 to Length(fConnections) - 1 do

  begin

      if fQueueBusy = 0 Then

      begin

            Result := i;

            Break;

      end;

  End;

end;