glendean Posted November 25, 2017 Share Posted November 25, 2017 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. Quote Link to comment Share on other sites More sharing options...
Administrators Farshad Mohajeri Posted November 25, 2017 Administrators Share Posted November 25, 2017 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. Quote Link to comment Share on other sites More sharing options...
glendean Posted November 26, 2017 Author Share Posted November 26, 2017 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 Quote Link to comment Share on other sites More sharing options...
glendean Posted December 2, 2017 Author Share Posted December 2, 2017 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; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.