FiveTech Software tech support forums

www.FiveTechSoft.com
https://fivetechsupport.com/forums/

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

https://fivetechsupport.com/forums/viewtopic.php?f=3&t=34208

Page 1 of 1

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

PostPosted: Thu Jun 15, 2017 4:38 am
by 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  
 

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 12:43 pm
by 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.

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 1:19 pm
by 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 ?

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 1:35 pm
by 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.

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 1:42 pm
by 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

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 2:29 pm
by 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
 

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 2:31 pm
by albeiroval
Use FWH 17.04

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 2:36 pm
by albeiroval
Rick

The table has more than 1000 records

:(

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 2:46 pm
by 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?

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 3:12 pm
by albeiroval
Excellent Mr Rao !!

It works very well. :D

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 )
 

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 3:25 pm
by nageswaragunupudi
It works very well. :D

Glad

Re: Mr Rao, slow to create xbrowse

PostPosted: Thu Jun 15, 2017 3:50 pm
by 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.

Re: Mr Rao, slow to create xbrowse(solved)... totals not update

PostPosted: Thu Jun 15, 2017 4:20 pm
by 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..

Re: Mr Rao, slow to create xbrowse(solved)... totals not update

PostPosted: Thu Jun 15, 2017 6:21 pm
by albeiroval
Rao,

Yes, I already did creating a query for totals and it works.
Thank you very much
All times are UTC
Page 1 of 1
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/