Performance Issues

[rdonnay]

I have completed some preliminary research on whether PGDBE can be a solution for my customer and it is not looking good.

The main reason they are looking at PGDBE is because they need improved multi-user performance on a local area network (1200 separate locations).
Currently, they are using DBFCDX, so data corruption also tends to be a problem.
Also, they need to add new columns to data tables when doing updates, and that takes too long due to large databases.

All of their issues could be resolved quickly and reliably if we could migrate them to ADSDBE instead of PGDBE.

Look at these debug screens:
Screen 1 shows the amount of time it takes to open 16 tables using DBFCDX. (.34 seconds)
Screen 2 shows the amount of time it takes to open the same 16 tables using PGDBE. (9.15 seconds)

OpenFile.dbfcdx.JPG (212.7 KiB) Viewed 7725 times

OpenFile.pgdbe.JPG (220.23 KiB) Viewed 7725 times

Now look at these debug screens:
Screen 3 shows the amount of time it takes to do a search through several tables and fill an array of record numbers based simply on dbSeeks() using DBFCDX (50.94 seconds)
Screen 4 shows the amount of time it takes to do a search through several tables and fill an array of record numbers based simply on dbSeeks() using PGDBE (1931.04 seconds)

Search1.dbfcdx.JPG (235.11 KiB) Viewed 7725 times

Search1.pgdbe.JPG (252.2 KiB) Viewed 7725 times

Neither of these searches used indexes that have UDFs.
This is a deal killer for my customer unless I am not understanding something and am doing something wrong.
Any ideas?

[SlavkoDam]

Hi,

You could download and try test version of PowerSql library and do the same research with it. It would be interesting to compare it's performance with your current ones. BTW, did you do your tests several times or only once?

[k-insis]

Indeed this looks worrisome. I knew after testing few years ago that performance of out of the box of pgdbe is not that fast, but this is unexpectedly slow.

This is read-only performce, correct? CPU and/or network is fully saturated during search?

Can you shed a bit more details on size of data /records/GB/ too?

That was done on single machine setup where DBF files and located and postgresql instance run? On same one and only one workstation or on different hosts?
Client and pgsql on same machine is different than dedicated host on network; on network there is overhead of NIC speed + latency, while on local machine antivirus can wreak havoc on performance of DB.

Am not full 'db admin' , but q:
Is yours postgresql server instance configured for large datasets or is just default setup? Memory allocation for resultsets / indexes / data does matter ; when high performance db and lots of users at same there is a lot of sense to put enough RAM into server that entirety of db+indexes+everythng else fits into RAM + handy reserve for users or even made into db cluster. So pgsql server needs tweaking.

Without optimising search for SQL with regular 'SELECT & WHERE' into cursor or temporary table on server - to get results fast, there is no way you will achieve speedup. At least that search that returns record numbers matching should be speed up significantly.

Also on WAN networks the network latency for fat-clients like xbase app becomes issue no matter the server and further slows pefromance.

As in my company among other things we also write and maintain a rather large inf.system (two decades old, still on dbfs and in xbase) for our country first level courts so this is interesting pov for me.

I have completed some preliminary research on whether PGDBE can be a solution for my customer and it is not looking good.

The main reason they are looking at PGDBE is because they need improved multi-user performance on a local area network (1200 separate locations).
Currently, they are using DBFCDX, so data corruption also tends to be a problem.
Also, they need to add new columns to data tables when doing updates, and that takes too long due to large databases.

[rdonnay]

This is read-only performce, correct? CPU and/or network is fully saturated during search?

Yes, read-only. I haven't looked yet at CPU performance, but I suspect it isn't much different because I can move windows around during this process normally.

Can you shed a bit more details on size of data /records/GB/ too?

These times that I am showing are only the seeks, nothing else. Therefore PGDBE should be accessing only index data.

Reading data seems to be not an issue. I have done a lot of testing and performance is very good.
If I use my SqlQuery tool (written in Xbase++) to compare a SELECT .. ORDER BY <expr> to USE .. OrdListAdd(), SQL is much faster than ISAM.
SQL is slower to open a dataset if using SELECT .. ORDER BY <expr> on a large dataset, but much faster when browsing the result.

I have a lot of experience with ADSDBE doing these same kind of tests and ADSDBE is always fast whether using ISAM or SQL.

These tests have been done on a computer with a SSD drive and PostGreSql installed on same computer as the client program (Xbase++).
I get the same result with PostGreSql installed on a Win 10 server on a local area network.

If this were a new project, I wouldn't be so concerned about PGDBE because I would write everything in SQL from scratch, but this is 25 year old Xbase+ code, originally Clipper S87 and converting all that ISAM to SQL will be a big challenge. It now depends on what kind of budget my customer has for this and how much time, because it may be possible to convert to SQL in less than 2 years. If ISAM performance were acceptable, it would be a 2 month project.

After my weekend testing, I thought I should propose a migration to ADS and so I reached out to SAP to find out if they could support ADS for maybe another 10 years. They are saying that end of life for ADS is this year and they will stop generating new licenses for any new customers.

[rdonnay]

I have determined that some ISAM routines just cannot be used due to poor performance (even after updating to PG version 16), but I also determined that SQL SELECT statements perform very well, therefore I am recommending to my customer that some search routines be re-written using a SELECT statement.

For example, I replaced this Xbase++ PGDBE ISAM code (execution time 15 minutes on large tables):

Code: Select all

     DO WHILE upper(IIF(empty(last),substr(first,1,16),substr(last,1,8)+substr(first,1,8))) = mCode .AND. !eof()    // sort16

        IF (!empty(fullcodeL).AND. !upper(names->Last)=fullcodeL) .OR. ;
             (!empty(fullcodeF).AND. !upper(names->First)=fullcodeF)
          skip
          loop
        ENDIF
        SELE nameref
        SeekNameNo := substr(names->name_no,2)   // drop name type & save a copy for
        seek SeekNameNo                          //   civil defendant check
        DO WHILE name_no = SeekNameNo .AND. !eof()      // !eof()

          sele charges                           // loop  to gather all charges
          seek nameref->index_no
          IF FOUND() .AND. aScan(aFoundList,RecNo())==0 // Need drop NameRev Duplicates
            nSrchResult := 1                     // A case was found so....
            aAdd( aFoundList, RecNo() )          //    record it in the search list
          ENDIF
          sele nameref
          skip
          IF len(aFoundList)>250 .AND. !lShowAlias
            EXIT
          ENDIF
        ENDDO

        SELE names

        skip

        IF len(aFoundList)>250 .AND. !lShowAlias
          EXIT
        ENDIF
        SetSearchAlert(@oAlertBox, @aFoundList)
      ENDDO

with this PGDBE SELECT statement (execution time less than 1 second):

Code: Select all

      TEXT INTO cStatement WRAP
        select distinct
          charges.name_no, charges.arrest_dt, nameref.name_no, charges.__record
        from
          charges
        inner join
          nameref on charges.name_no = nameref.name_no
        inner join
          names on Substr(names.name_no,2) = nameref.name_no
          where names.first = '&mcode'
       ENDTEXT

        oPgStatement := DacSqlStatement(pgSession()):FromChar(cStatement)
        oPgStatement:Build():Query(,'CHARGESQRY')

        DO WHILE !CHARGESQRY->(Eof())
          AAdd(aFoundList,CHARGESQRY->__Record)
          CHARGESQRY->(dbSkip())
          IF Len(aFoundList) > 250 .AND. !lShowAlias
            EXIT
          ENDIF
          SetSearchAlert(@oAlertBox, @aFoundList)
        ENDDO
        CHARGESQRY->(dbCloseArea())