bb.donnay-software.com

Posted: **Tue Dec 05, 2017 5:10 pm**

Here is a test program I wrote to compare performance of the PGDBE to the ODBCDBE.

Passing a 1 uses the ODBCDBE
Passing a 2 uses the PGDBE

The ODBCDBE out-performs the PGDBE by about 40% on the average.
These tests are based on SQL SELECT statements, not ISAM.

This example retrieves data from SENDMAIL.DBF which has 271,454 records.
The cursor is returned in .64 seconds (ODBCDBE) or 1.03 seconds (PGDBE).
The output to the "output pane" for the same statement using pgAdmin III is about 1.18 seconds.

Code: Select all

#INCLUDE "dcdialog.ch"
#include "pgdbe.ch"
#INCLUDE "odbcdbe.CH"
#INCLUDE "sqlcmd.CH"

// Execute a statement (ODBCDBE)
#xcommand PGSQL <(x)> [VIA <session>] [TO <y>] ;
              => [<y> :=] PG_SqlStmtExec(<(x)>,<session>)

FUNCTION Main( cDbe )

LOCAL cConnect, oSession, i, oBrowse, aStru, GetList[0], GetOptions, ;
      cSQL1, cSQL2, lStatus, nDbe, cAlias, oStmt, nSeconds, nTime

DEFAULT cDbe := '1'

nDbe := Val(cDbe)

IF nDbe == 2

  IF(!DbeLoad("pgdbe"))
    DCMSGBOX "Unable to load the PostgreSQL DatabaseEngine"
    QUIT
  ENDIF

  cConnect := "DBE=pgdbe;SERVER=localhost;DB=medallion;UID=postgres;PWD=" + GetEnv('POSTGRESQL_PASSWORD')

ELSEIF nDbe == 1

  IF(!DbeLoad("odbcdbe"))
    DCMSGBOX "Unable to load the ODBC DatabaseEngine"
    QUIT
  ENDIF

  cConnect := "DBE=odbcdbe;DRIVER={PostgreSQL Unicode};"
  cConnect += "SERVER=localhost;PORT=5432;Database=medallion;UID=postgres;PWD=" + GetEnv('POSTGRESQL_PASSWORD')

ENDIF

SET NULLVALUE OFF
dbSetNullValue(.f.)

oSession := DacSession():New( cConnect )

IF .NOT. oSession:isConnected()
  DCMSGBOX oSession:GetLastMessage(), ;
           "Unable to establish connection to server"
  QUIT
ENDIF

TEXT INTO cSQL1 WRAP
SELECT date, time, subject, mail_to, __record from sendmail
       where error_code >= 0
       order by ? ;
ENDTEXT

cSQL2 := DC_ApplySQLParams( cSQL1, {'[__record]'} )

nSeconds := Seconds()

IF nDbe == 1

  PGSQL (cSQL2) VIA (oSession) TO lStatus
  IF !lStatus
    RETURN .f.
  ENDIF

ELSE

  oStmt := DacSqlStatement():fromChar(cSQL2)
  oStmt:build():query()

ENDIF

nTime := Seconds() - nSeconds

cAlias := Alias()

wtf reccount(), cAlias

@ 0,0 DCBROWSE oBrowse ALIAS cAlias SIZE 120,25 ;
      RESIZE DCGUI_RESIZE_RESIZEONLY

aStru := (cAlias)->(dbStruct())

FOR i := 1 TO Len(aStru)
  DCBROWSECOL DATA FieldWBlock(aStru[i,1],cAlias) ;
    HEADER DC_CapFirst(aStru[i,1]) WIDTH Min(aStru[i,3],10) PARENT oBrowse ;
    SORT SortBlock(aStru[i,1],oSession,@oBrowse,cAlias,nDbe,cSQL1) ;
    PICTURE IIF( aStru[i,1] == '__record','9999999',nil)
NEXT

DCGETOPTIONS RESIZE
DCREAD GUI FIT ;
   TITLE 'PostGreSQL test (' + IIF(nDbe==1,'ODBCDBE','PGDBE') + ;
          ') (' + Alltrim(Str(nTime)) + ')';
   OPTIONS GetOptions

// Disconnect from server
oSession:disconnect()

RETURN nil

* -----------

PROCEDURE Appsys ; RETURN

* -----------

STATIC FUNCTION SortBlock( cFieldName, oSession, oBrowse, cAlias, nDbe, cSQL )

RETURN {||Resort(cFieldName,oSession,oBrowse,cAlias,nDbe,cSQL)}

* -----------

STATIC FUNCTION ReSort( cFieldName, oSession, oBrowse, cAlias, nDbe, cSQL )

LOCAL oStmt, lStatus, nSeconds

(cAlias)->(dbCloseArea())

cSQL := Strtran(cSQL,'?',cFieldName)

nSeconds := Seconds()

IF nDbe == 2

  oStmt := DacSqlStatement():fromChar(cSQL)
  oStmt:build():query()

ELSE

  PGSQL (cSQL) VIA (oSession) TO lStatus

ENDIF

wtf Seconds() - nSeconds

oBrowse:refreshAll()

RETURN nil

* ---------

FUNCTION PG_SqlStmtExec(cSql,oSession)

LOCAL oError, lStatus := .f., GetList[0], GetOptions, ;
      bError := ErrorBlock({|oError|break(oError)}), ;
      aStack[0], aStackList[0], i := 1, cSqlError

BEGIN SEQUENCE

  // Execute a statement
  lStatus := SqlStmtExec(cSql,oSession,,,.F.)

RECOVER USING oError

  DO WHILE .t.

    AAdd( aStackList, "Called from " + Trim(ProcName(i)) + ;
       "(" + Alltrim(Str(ProcLine(i))) + ")" )
    i++
    IF Empty(ProcName(i))
      EXIT
    ENDIF

  ENDDO

  cSqlError := OdbcDebug(oSession)[15,2]

  @ 0,0 DCSAY 'Error in SQL statement:' SAYSIZE 0 FONT '10.Lucida Console' ;
        RESIZE DCGUI_RESIZE_REPOSONLY_Y

  @ 1,0 DCMULTILINE cSql SIZE 120,18 FONT '10.Lucida Console' NOHSCROLL ;
        RESIZE DCGUI_RESIZE_RESIZEONLY

  @ 20,0 DCMULTILINE cSqlError SIZE 120,3 FONT '8.Lucida Console' NOHSCROLL ;
        RESIZE DCGUI_RESIZE_REPOSY_RESIZEX

  FOR i := 1 TO Len(aStackList)
    @ 23+i,0 DCSAY aStackList[i] FONT '10.Lucida Console' SAYSIZE 0 ;
      RESIZE DCGUI_RESIZE_REPOSONLY_Y
  NEXT

  DCGETOPTIONS RESIZE

  DCREAD GUI FIT TITLE 'SQL Error' BUTTONS DCGUI_BUTTON_OK ;
     MODAL OPTIONS GetOptions

END SEQUENCE

ErrorBlock(bError)

RETURN lStatus

Posted: **Tue Dec 05, 2017 10:18 pm**

rdonnay wrote: The cursor is returned in .64 seconds (ODBCDBE) or 1.03 seconds (PGDBE).
The output to the "output pane" for the same statement using pgAdmin III is about 1.18 seconds.

i wonder that PgAdmin3 is slower than PgDBE

would be interesting how fast native Solution is ...

... hm ... how to create a "big" Reference DBF so we can testdrive with same data ...

Posted: **Wed Dec 06, 2017 8:09 am**

i wonder that PgAdmin3 is slower than PgDBE

There was probably additional time required to create the browse view.
That was not considered in my tests of ODBCDBE and PGDBE.
I only record the time to create the cursor.
It also takes additional time to create the browse view in Xbase++.

bb.donnay-software.com

ODBC Driver for PostGreSQL

Re: ODBC Driver for PostGreSQL

Re: ODBC Driver for PostGreSQL

Re: ODBC Driver for PostGreSQL