slowness

Re: slowness

Postby MarcoBoschi » Mon Sep 16, 2024 3:33 pm

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)
User avatar
MarcoBoschi
 
Posts: 1065
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

Re: slowness

Postby Otto » Mon Sep 16, 2024 6:23 pm

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

Image

Image


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





 
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: slowness

Postby mauri.menabue » Mon Sep 16, 2024 9:55 pm

Hi Marco
Are there many double keys in the index?
bye
User avatar
mauri.menabue
 
Posts: 155
Joined: Thu Apr 17, 2008 2:38 pm

Re: slowness

Postby Otto » Tue Sep 17, 2024 9:44 am

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

Image
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: slowness

Postby MarcoBoschi » Tue Sep 17, 2024 9:49 am

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


fw program written by myself
User avatar
MarcoBoschi
 
Posts: 1065
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

Re: slowness

Postby MarcoBoschi » Tue Sep 17, 2024 10:14 am

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
User avatar
MarcoBoschi
 
Posts: 1065
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

Re: slowness

Postby Otto » Tue Sep 17, 2024 11:12 am

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


Image
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: slowness

Postby MarcoBoschi » Tue Sep 17, 2024 11:41 am

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
User avatar
MarcoBoschi
 
Posts: 1065
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

Re: slowness

Postby Otto » Tue Sep 17, 2024 1:42 pm

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
 
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: slowness

Postby MarcoBoschi » Tue Sep 17, 2024 3:44 pm

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
User avatar
MarcoBoschi
 
Posts: 1065
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy

Re: slowness

Postby Enrico Maria Giordano » Tue Sep 17, 2024 4:01 pm

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


Do you get any error messages?
User avatar
Enrico Maria Giordano
 
Posts: 8710
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: slowness

Postby Otto » Tue Sep 17, 2024 4:21 pm

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
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6312
Joined: Fri Oct 07, 2005 7:07 pm

Re: slowness

Postby Enrico Maria Giordano » Tue Sep 17, 2024 4:36 pm

Otto wrote: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.
User avatar
Enrico Maria Giordano
 
Posts: 8710
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: slowness

Postby TimStone » Tue Sep 17, 2024 5:13 pm

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
Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit
User avatar
TimStone
 
Posts: 2943
Joined: Fri Oct 07, 2005 1:45 pm
Location: Trabuco Canyon, CA USA

Re: slowness

Postby paquitohm » Tue Sep 17, 2024 8:40 pm

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


Perhaps letoDb or letoDbf
paquitohm
 
Posts: 257
Joined: Fri Jan 14, 2022 8:37 am

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: nageswaragunupudi and 53 guests