Mr Rao, slow to create xbrowse(solved)...

[albeiroval]

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: Select all  Expand view

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  
 

[nageswaragunupudi]

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.

[albeiroval]

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 ?

[nageswaragunupudi]

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.

[Rick Lipkin]

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: Select all  Expand view

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]

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: Select all  Expand view

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
 

[albeiroval]

Use FWH 17.04

[albeiroval]

Rick

The table has more than 1000 records

[nageswaragunupudi]

Code: Select all  Expand view

                       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?

[albeiroval]

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: Select all  Expand view

EXTEND CLASS TXBrowse WITH METHOD QueryWithJoin

oBrw:QueryWithJoin( cQuery, TABLA_COMPRASFACTURAS, oQry )
 

[nageswaragunupudi]

It works very well.

Glad

[albeiroval]

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.

[nageswaragunupudi]

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..

[albeiroval]

Rao,

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