FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Mr Rao, slow to create xbrowse(solved)...
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Mr Rao, slow to create xbrowse(solved)...
Posted: Thu Jun 15, 2017 04:38 AM
Hi,

How can I improve the speed of creation of an xbrowse when loading data from a table with many records using tdolphin,
Probe using oBrw :: oMysql: lPagination = .T. And does not improve the speed to create xbrowse.

The code is as follows :
Code (fw): Select all Collapse
REDEFINE XBROWSE oBrw OF oDlg ID 100;
    DATASOURCE oQry;
    LINES FOOTERS AUTOSORT 
    
  ADD TO oBrw DATA oQry:fecha                 TITLE "Fecha"                       SIZE 100    
  ADD TO oBrw DATA oQry:fecha_factura     TITLE "Fecha_Factura"   SIZE 100   
  ADD TO oBrw DATA oQry:rif_provedor       TITLE "Rif"                         SIZE 100   
  ADD TO oBrw DATA oQry:nombre_provedor TITLE "Provedor"                SIZE 300   
  ADD TO oBrw DATA oQry:numero_factura  TITLE "Factura"                 SIZE 120 FOOTER "Totales"  
  ADD TO oBrw DATA oQry:tipo                    TITLE "Tipo"                        SIZE 080 LEFT   
  ADD TO oBrw DATA oQry:montobase          TITLE "Base"                        SIZE 120 PICTURE "@E 999,999,999,999.99" TOTAL nTotBas RIGHT  
  ADD TO oBrw DATA oQry:montoexento      TITLE "Exento"                    SIZE 120 PICTURE "@E 999,999,999,999.99" TOTAL nTotExe RIGHT        
  ADD TO oBrw DATA oQry:montoiva            TITLE "Iva"                         SIZE 120 PICTURE "@E 999,999,999,999.99" TOTAL nTotIva RIGHT    
  ADD TO oBrw DATA oQry:montototal         TITLE "Total"                   SIZE 120 PICTURE "@E 999,999,999,999.99" TOTAL nTotTot RIGHT 
  ADD TO oBrw DATA oQry:montoretenido    TITLE "Retencion"           SIZE 120 PICTURE "@E 999,999,999,999.99" TOTAL nTotRet RIGHT    
  ADD TO oBrw DATA oQry:periodo               TITLE "Periodo"                 SIZE 100
Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 12:43 PM

Dolphin should be quite fast with pagination and smaller page size.

In this case, please remove all totals. We should never call oBrw:MakeTotals() for large tables (even for DBF). Let us see if the performance improves after completely removing totals and using Dolphin's pagination with page size of 100 or so.

Once we achieve acceptable performance, then we shall use some other method to display totals.

Regards



G. N. Rao.

Hyderabad, India
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 01:19 PM

Thank you, Mr. Rao,

Remove the maketotals(), now create xbrowse fast.

Add these lines:

oBrw: oMysql: lPagination = .T.
oBrw: oMysql: SetPages (100)

And to show totals ?

Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 01:35 PM
Totals:
1) Do not use Maketotals() but bring back your TOTAL clause.
Please make sure you never call MakeTotals() even during runtime.

2) Find totals separately with a query "SELECT SUM(...) AS .. , SUM( .. ) AS .."
Assign the values to oCol(n):nTotal, oCol(m):nTotal and so on.

We are spending some extra time for this query. But better than Browse calculating the totals.

3) If you use FASTEDIT and do inline edit, the totals are automatically recalculated.

Please also let us know your fwh version. Automatic recalculation of totals is greatly improved in recent versions.

First do upto this.
We discuss about next steps after you do this.
Regards



G. N. Rao.

Hyderabad, India
Rick Lipkin
Posts: 2706
Joined: Fri Oct 07, 2005 01:50 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 01:42 PM
Albeiro

In cases where I know I am doing a full table scan with :MakeTotals ... I only allow totals to be created for less than 1000 records ..

Code (fw): Select all Collapse
If oRsWork:RecordCount() <= 1000
   oBrw:MakeTotals()
Endif


Not a very elegant solution, but in most situations my queries to not return more than 1000 rows and :maketotals works fine....

Rick Lipkin
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 02:29 PM
Mr Rao,

I made the following changes, but only shows 1 record, it should be by the SUM clause of the query, how should I create the query to show all the records?

Code (fw): Select all Collapse
TEXT INTO cQuery
    SELECT  fecha, 
                    periodo, 
                    rif_provedor, 
                    nombre_provedor, 
                    tipo,
                    fecha_factura, 
                    numero_factura, 
                    control_factura, 
                    montoretenido, 
                    montoexento, 
                montoiva, 
                    montobase, 
                    montototal, 
                    alicuota, 
                    my_recno, 
                    CASE tipo
                            WHEN tipo = 1 THEN "Registro"
                            WHEN Tipo = 2 THEN "Ajuste" 
                            ELSE " "
                        END AS nametipo,
                        SUM(montobase)          AS sumabase, 
                        SUM(montototal)         AS sumatotal,
                        SUM(montoiva)           AS sumaiva, 
                        SUM(montoretenido)  AS sumaretenido,
                        SUM(montoexento)        AS sumaexento
        FROM %1 
    ORDER BY 
  ENDTEXT 
    
  cQuery := StrFormat( cQuery, TABLA_COMPRASFACTURAS )
  
  oQry = oSrv:CreateQuery( cQuery + "fecha" )
    
  DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12 BOLD

  DEFINE DIALOG oDlg TITLE "FACTURAS DE COMPRA" RESOURCE "XBROWSE" ICON aBitmap[ BMP32_ICON ] FONT oFont 
          
  REDEFINE XBROWSE oBrw OF oDlg ID 100;
    DATASOURCE oQry;
    LINES FOOTERS AUTOSORT 
    
  ADD TO oBrw DATA oQry:fecha                       TITLE "Fecha"                       SIZE 100    
  ADD TO oBrw DATA oQry:fecha_factura       TITLE "Fecha_Factura"   SIZE 100   
  ADD TO oBrw DATA oQry:rif_provedor        TITLE "Rif"                         SIZE 100   
  ADD TO oBrw DATA oQry:nombre_provedor TITLE "Provedor"                SIZE 300   
  ADD TO oBrw DATA oQry:numero_factura  TITLE "Factura"                 SIZE 120 FOOTER "Totales"  
  ADD TO oBrw DATA oQry:nametipo                TITLE "Tipo"                        SIZE 080 LEFT   
  ADD TO oBrw DATA oQry:montobase           TITLE "Base"                        SIZE 120 PICTURE "@E 999,999,999,999.99" RIGHT  
  ADD TO oBrw DATA oQry:montoexento       TITLE "Exento"                    SIZE 120 PICTURE "@E 999,999,999,999.99" RIGHT        
  ADD TO oBrw DATA oQry:montoiva                TITLE "Iva"                         SIZE 120 PICTURE "@E 999,999,999,999.99" RIGHT    
  ADD TO oBrw DATA oQry:montototal          TITLE "Total"                   SIZE 120 PICTURE "@E 999,999,999,999.99" RIGHT 
  ADD TO oBrw DATA oQry:montoretenido   TITLE "Retencion"           SIZE 120 PICTURE "@E 999,999,999,999.99" RIGHT    
  ADD TO oBrw DATA oQry:periodo             TITLE "Periodo"                 SIZE 100   
        
  WITH OBJECT oBrw
    :MyConfig()
    :oMysql:SetPages( 100 )
    :QueryWithJoin( cQuery, TABLA_COMPRASFACTURAS, oQry )
    
    :aCols[1]:cSortOrder = "fecha"    
    :aCols[2]:cSortOrder = "fecha_factura"    
    :aCols[3]:cSortOrder = "rif_provedor"
    :aCols[4]:cSortOrder = "nombre_provedor"
    :aCols[5]:cSortOrder = "numero_factura"
    
    :oCol("Base"):nTotal            = oQry:sumabase
    :oCol("Exento"):nTotal      = oQry:sumaexento
    :oCol("Iva"):nTotal             = oQry:sumaiva
    :oCol("Total"):nTotal       = oQry:sumatotal
    :oCol("Retencion"):nTotal = oQry:sumaretenido
    
  END
Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 02:31 PM

Use FWH 17.04

Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 02:36 PM

Rick

The table has more than 1000 records

:(

Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 02:46 PM
Code (fw): Select all Collapse
                        SUM(montobase)          AS sumabase, 
                        SUM(montototal)         AS sumatotal,
                        SUM(montoiva)           AS sumaiva, 
                        SUM(montoretenido)  AS sumaretenido,
                        SUM(montoexento)        AS sumaexento

This should be a different query. Not a part of you main table query

Are you using modified xbrowse?
Regards



G. N. Rao.

Hyderabad, India
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 03:12 PM
Excellent Mr Rao !!

It works very well. :-)


Are you using modified xbrowse?


I have not modified xbrowse.

I just made a new method to create filters with dolphin using this code :
Code (fw): Select all Collapse
EXTEND CLASS TXBrowse WITH METHOD QueryWithJoin

oBrw:QueryWithJoin( cQuery, TABLA_COMPRASFACTURAS, oQry )
Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 03:25 PM
It works very well. :-)

Glad
Regards



G. N. Rao.

Hyderabad, India
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse
Posted: Thu Jun 15, 2017 03:50 PM

3) If you use FASTEDIT and do inline edit, the totals are automatically recalculated.


I do not use cell editing to add or modify records,
I use INSERT and UPDATE, oQry: Requery () to update the xbrowse data,
The problem now the totals do not update them.
Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Mr Rao, slow to create xbrowse(solved)... totals not update
Posted: Thu Jun 15, 2017 04:20 PM

Yes, with dolphin and tmysql, even we modify one field we need to reload the entire data again.
That requires a browse refresh too. Anyway you are spending time for reloading the data. You spend a little more time requery totals query also and refresh the totals.

With fwmysql only the modified row is refreshed. You may try \fwh\samples\maria01.prg and check the edit behavior both inline and with dialog..

Regards



G. N. Rao.

Hyderabad, India
albeiroval
Posts: 400
Joined: Tue Oct 16, 2007 05:51 PM
Re: Mr Rao, slow to create xbrowse(solved)... totals not update
Posted: Thu Jun 15, 2017 06:21 PM

Rao,

Yes, I already did creating a query for totals and it works.
Thank you very much

Saludos,
Regards,

Albeiro Valencia
www.avcsistemas.com

Continue the discussion

Static archive · New replies & topics via GitHub Discussions