slowness

[MarcoBoschi]

Mark it's correct
I have to search phone calls in every table
I have to search phone calls in every table
The search in the table opened from other programs is slow while the search in close table are very very fast

If I have to search in calls and out calls from may 2024 to september 2024 I have to open
i_202405.dbf fast
i_202406.dbf fast
i_202407.dbf fast
i_202408.dbf fast
i_202409.dbf slow (opened by programs that received data from pbx)

[Otto]

Dear Marco,

The fastest I can achieve with Harbour over the network is 3.36 seconds and 588 matches.

There are 240,734 records in the database. I simply inserted your dbf a few times.

I believe the DBF file you sent me does not support CDX. I recreated the file and imported yours. Now it works.

I think your mistake is that although you open the index file, you don't select a TAG and you also don't consider the index in the do-while loop.

With my low-level function (PHP4DBF), I only need 0.65 seconds.

Best regards,
Otto

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX
REQUEST DBFFPT
 static nStartTime, nDuration
   FUNCTION CONTA_TELEFONATE()
   LOCAL cAmIni     := SUBSTR( DTOS( DATE() )  , 1 , 6 )
   LOCAL cAmFin     := SUBSTR( DTOS( DATE() )  , 1 , 6 )
   LOCAL aLista     := {}
   LOCAL cChiamante := SPACE( 30 )
   LOCAL cChiamato  := SPACE( 30 )
   LOCAL cDbf
   LOCAL oExcel , oAs
    local nMatches := 0
 /*
  local aFields := { { "EVENT", "C", 20, 0 },;
                   { "TIME", "N", 14, 0 },;
                   { "DATE", "C", 20, 0 },;
                   { "REF", "C", 50, 0 },;
                   { "DIR", "C", 5, 0 },;
                   { "SRC_IF", "C", 20, 0 },;
                   { "DST_IF", "C", 20, 0 },;
                   { "SRC_CGPN", "C", 20, 0 },;
                   { "SRC_CDPN", "C", 20, 0 },;
                   { "SRC_NAME", "C", 30, 0 },;
                   { "DST_CGPN", "C", 20, 0 },;
                   { "DST_CDPN", "C", 20, 0 },;
                   { "SRC_REG_NA", "C", 20, 0 },;
                   { "BCAPS", "C", 20, 0 },;
                   { "CAUSE", "C", 20, 0 },;
                   { "XCODER", "C", 20, 0 },;
                   { "RCODER", "C", 20, 0 },;
                   { "XSTATS", "C", 20, 0 },;
                   { "RSTATS", "C", 20, 0 },;
                   { "ALERT_TIME", "N", 14, 0 },;
                   { "CONNECT_TI", "N", 14, 0 },;
                   { "DISC_TIME", "N", 14, 0 },;
                   { "SRV_ID", "C", 20, 0 },;
                   { "SECONDI", "N", 10, 0 },;
                   { "DURATA", "C", 30, 0 },;
                   { "TCOSTOUNI", "N", 10, 0 },;
                   { "TCOSTOSEC", "N", 10, 0 },;
                   { "TSEC", "N", 10, 0 },;
                   { "DO", "C", 20, 0 },;
                   { "TARIFFA", "C", 3, 0 },;
                   { "COSTO", "N", 14, 5 },;
                   { "COSTO2", "N", 14, 5 },;
                   { "UTEMODI", "C", 3, 0 },;
                   { "DATMODI", "C", 14, 0 } }

DbCreate( "myfile.dbf", aFields, "DBFCDX" )
 
 */
 
 cChiamante := "351573******" //ALLTRIM( cChiamante )
 cChiamato  := ALLTRIM( cChiamato )
 
 
 nInizio := SECONDS()
 
 // use ( "s:\datamarco\myfile.dbf" ) new
 // index on field->src_cgpn   tag myidx  
 // USE
 ? "weiter"
 
 nStartTime := SECONDS()
 use ( "s:\datamarco\myfile.dbf" ) new alias I_OTTO   // Öffne die DBF-Datei
 // set index to "s:\datamarco\myfile.CDX"   // Lade die bestehende Indexdatei
 set order to tag myidx   // Setze den Index-Tag
 
 
 dbSeek( cChiamante )
 DO WHILE FIELD->src_cgpn = cChiamante .and. .not. EOF()
   
   
   
   IF !EMPTY( cChiamante )
      nMatches ++
      IF AT( cChiamante  , FIELD->src_cgpn ) > 0
         IF field->dir = "out" .AND. field->secondi > 0 .AND. field->event = "B:Rel"
            AADD( aLista  , { "IN" , field->src_cgpn ,  SPACE(20) , field->do , field->secondi, field->dst_cdpn  } )
         ENDIF
      ENDIF
   ENDIF
   
   IF !EMPTY( cChiamato )
      IF AT( cChiamato  , FIELD->src_cdpn ) > 0
         IF field->dir = "out" .AND. field->secondi > 0 .AND. field->event = "B:Rel"
            AADD( aLista  , { "OUT" , SPACE( 20 ) , field->src_cdpn ,  field->do , field->secondi , field->src_cgpn } )
         ENDIF
      ENDIF
   ENDIF
   
   SKIP
   
ENDDO
USE




nDuration := (SECONDS() - nStartTime)
xbrowse(aLista, "nDuration:  "+str(nDuration) + "nMatches:  " + str(nMatches))



RETURN NIL


INIT PROCEDURE PrgInit
   
   SET CENTURY ON
   SET EPOCH TO YEAR(DATE())-98
   
   SET DELETED ON
   SET EXCLUSIVE OFF
   
   REQUEST HB_Lang_DE
   
   HB_LangSelect("DE")
   
   SET DATE TO GERMAN
   
   rddsetdefault( "DBFCDX" )
   
   EXTERN DESCEND
   
RETURN





 

[mauri.menabue]

Hi Marco
Are there many double keys in the index?
bye

[Otto]

Marco,

I created a DBF file information screen, and it seems your original DBF has exactly the same structure.
Who created the I_otto.dbf file—was it you with your software, or the phone system?

Best regards,
Otto

[MarcoBoschi]

Who created the I_otto.dbf file—was it you with your software, or the phone system?

fw program written by myself

[MarcoBoschi]

Otto,

Let's consider Emag previous example in this post

Code: Select all  Expand view

#include "Dbinfo.ch"


REQUEST DBFCDX
REQUEST DBFFPT


FUNCTION MAIN()

    LOCAL nSec

    RDDSETDEFAULT( "DBFCDX" )

    SET DBFLOCKSCHEME TO DB_DBFLOCK_CL53EXT

    USE \\yourserver\yourfolder\yourdbf SHARED

    SET INDEX TO \\yourserver\yourfolder\yourcdx

    nSec = SECONDS()

    WHILE !EOF()
        SKIP
    ENDDO

    ? SECONDS() - nSec

    INKEY( 0 )

    RETURN NIL
 

Try it three times

first time run this program with DBF and CDX files opened only by this program
second time run this program with only DBF opened by another pc in the same lan
third time run this program with dbf and cdx opened by another pc in the same lan


Let's not think about anything other than this clarifying example

when DBF is used in Share Mode only Once, it will use SMB "Exlusive Locking" Mode
as soon when DBF was opened second Time it will switch into "Opportunistic Locking"

Jimmy probably came close to the problem but in my opinion the problem is not dbf file but cdx file

[Otto]

Marco, I don't notice this problem.
I open the file from 2 different PCs and leave it open, as you can see from the screenshot.
The speed doesn't slow down.
On the other side, I have Windows Server 2022, and on the PCs, Windows 10 and 11.



Best regards,
Otto

[MarcoBoschi]

Otto,
please do the last test, compile Enrico's example program and then let's leave it alone, I don't know what to say anymore.
I don't change my opinion until I will see with my eyes that Emag example wiil be fast in every condition
King regards
Marco

[Otto]

Marco, this sample for me only works if I do not use index.
speed is within 5 sec.
Best regards,
Otto

Code: Select all  Expand view

#include "fivewin.ch"
#include "Dbinfo.ch"


REQUEST DBFCDX
REQUEST DBFFPT


FUNCTION MAIN()

    LOCAL nSec

    RDDSETDEFAULT( "DBFCDX" )

    // SET DBFLOCKSCHEME TO DB_DBFLOCK_CL53EXT

    USE ( "s:\datamarco\myfile.dbf" ) SHARED

   // SET INDEX TO "s:\datamarco\myfile.CDX"  
    // set order to tag myidx
    nSec = SECONDS()

    WHILE !EOF()
   
          SKIP
    ENDDO

    ? SECONDS() - nSec
? "Ende"
    INKEY( 0 )

    RETURN NIL
 

[MarcoBoschi]

I've made some test also used as a server an old fashioned xp notebook. The speed is ...spectatural

from windows windows 10 to windows 10
0.03 secs with closed table
25.36 secs with opened table from other users

from windows 10 to windows xp

0.0 secs with closed table
4.2 secs with opened table from other users

What a shame, I think something could be done

[Enrico Maria Giordano]

Marco, this sample for me only works if I do not use index.

Do you get any error messages?

[Otto]

Enrico, since there is no display, you can’t see anything. It might work, but I just stopped it after a certain amount of time because it cannot be a solution.

It won’t work with mixed Windows versions. I gave up on that a long time ago. We first disabled SMB-2 back then, but it’s too complex.

That’s why I switched to Remote Desktop. You don’t have any of these problems here.

What you might consider is a microservice. We use it to write into the DBF files since we still need to stay compatible with the desktop version and its index files.
We read from the DBF files via the web using low-level access.


Best regards,
Otto

[Enrico Maria Giordano]

Enrico, since there is no display, you can’t see anything. It might work, but I just stopped it after a certain amount of time because it cannot be a solution.

Yes, that's exactly what you had to see: the slowness of the SKIP loop.

[TimStone]

When using Advantage Database Server ( server mode ), I can open 6 files and CDX's on multiple computers at the same time ( same files ), and it remains very fast. It was an excellent solution. My clients found the same results ... the speed was very fast at all workstations.

Then the company who bought out the original company killed the product and it is no longer available.

Unfortunately, we have no alternatives available. It is very sad.

Tim

[paquitohm]

Unfortunately, we have no alternatives available. It is very sad.
Tim

Perhaps letoDb or letoDbf