RAO advice on using Mysql

[Maurizio]

RAO,
I wanted to ask you for advice on how to use tables with millions of data without experiencing a loss of performance

Grazie Maurizio

[nageswaragunupudi]

https://forums.fivetechsupport.com/view ... et&start=0

https://forums.fivetechsupport.com/view ... c4#p228914

We will prepare a sample soon for your testing. Please wait.

Anyway, the usual practice is not to open the full table but to open only the required part of a table using where clause.
Still FWH RecSet class provides a fast way to open very large tables.

[Maurizio]

Thank Rao

I'm trying oCn:RecSet() and it's very, very fast, I noticed that certain methods that work with oCn:Rowset() with :RecSet() don't work.

Is it possible to have a list of :RecSet() methods?

Thank you
Maurizio

[nageswaragunupudi]

I'm trying oCn:RecSet() and it's very, very fast, I noticed that certain methods that work with oCn:Rowset() with :RecSet() don't work.

What size of tables you tested?

[Maurizio]

Hello
I tested on a table of 700,000 records , 350000Kb
Regards Maurizio

[nageswaragunupudi]

We now have a table 'custbig' on our FWH Cloud server with
4.1 million records.
Let us see the speed. This speed is by accessing the data from a Server in NewYork over Internet.
Speeds on local area network will be even far better.

Code: Select all | Expand

#include "fivewin.ch"

function Main()

   local oCn, oRs, nSum, nSecs

   SET DATE ITALIAN
   SET CENTURY ON

   FWNumFormat( "A", .t. )

   FW_SetUnicode( .t. )

   CursorWait()
   oCn      := maria_Connect( "208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", .t. )
   oCn:lShowErrors := .t.

   nSecs    := SECONDS()
   MsgRun( "Reading `custbig`", "Please wait", { || oRs   := oCn:RecSet( "custbig", -100 ), ;
         nSum := oCn:QueryResult( "SELECT SUM(SALARY) FROM `custbig`" ) } )
   nSecs    := SECONDS() - nSecs

   XBROWSER oRs TITLE " CUSTBIG (" + cValToChar( nSecs ) + ") seconds" ;
      FASTEDIT SHOW RECID SETUP ( ;
      oBrw:id:cEditPicture := "99,999,999", ;
      oBrw:nFreeze := 1, ;
      oBrw:Salary:nFooterType := AGGR_SUM, ;
      oBrw:Salary:nTotal := nSum, ;
      oBrw:aSumCols := { oBrw:oCol( "SALARY" ) } )

   oCn:Close()

return nil

Also see how instantaneous is the retotaling of 4 million records when one row is modified.

[nageswaragunupudi]

METHODS:

RecCount(), LastRec(), KeyCount(),RecNo(), KeyNo()
GoTop(),GoBottom(),GoTo(nRec),KeyGoTo(nKey),Skip(n)
FCount(),FieldPos(c),FieldName(),FieldLen(),FieldDec(),FieldType()
FieldGet(),FieldPut()
SetOrder(cSort,,lDescend)
Save()
Close() or End()
---------------
NOT SUPPORTED:
Seek(), Filter(),Delete(),Append()

The RecSet class is created in such rare cases where it is essential to read and display a big table in full.
This is not possible with other libraries like ADO,etc.

But we recommend avoid using TRecSet to the extent possible and to use RowSet reading required parts of the table using WHERE clause. This is the standard practice.

[Maurizio]

Thanks RAO for the clear specification, and how to use RecSet.
Now we also know what methods are available.

Regards Maurizio
www.nipeservice.com

[Maurizio]

Hello RAO

Since it is not possible to use filters with RecSet I am trying to close and reopen RecSet in xBrowse.
I normally use

Code: Select all | Expand

WITH OBJECT oBrw:oRs
         :Close()
         :Source  := cNewSelectWithWhere 
         :ActiveConnection := oServer
         :Open()
END
oBrw:Refresh()
   

But this doesn't work .
Is there a solution or is it better to close xBrowse and reopen it?
Regards Maurizio

[nageswaragunupudi]

:ActiveConnections is only for ADO recordsets.
In fwmaria lib, it is oRs:oCn.

To change the sql, please make a new RecSet object, set it to oBrw:oDbf and then close the old RecSet object

You may try this sample and use similar logic

Code: Select all | Expand

#include "fivewin.ch"

function Main()

   local oCn, oRs
   local cSql  := "SELECT * FROM custbig WHERE STATE="

   SET DATE ITALIAN
   SET CENTURY ON

   FWNumFormat( "A", .t. )

   FW_SetUnicode( .t. )

   CursorWait()
   oCn      := maria_Connect( "208.91.198.197:3306,fwhdemo,gnraofwh,Bharat@1950", .t. )
   oCn:lShowErrors := .t.

   oRs   := RecSetNew( oCn, cSql + "'NY'" )

   XBROWSER oRs TITLE "CUSTBIG" FASTEDIT SHOW RECID SETUP ( ;
      oBrw:bRClicked := { |r,c,f,o| ChangeRecSet( o, cSql + "'WA'" ) } )

   oCn:Close()

return nil

function RecSetNew( oCn, cSql )

   local oRs

   MsgRun( "Reading `custbig`", "Please wait", ;
         { || oRs   := oCn:RecSet( cSql, -100 ) } )

return oRs

function ChangeRecSet( oBrw, cSql )

   local oRs := oBrw:oDbf

   oBrw:oDbf := RecSetNew( oRs:oCn, cSql )
   oRs:Close()
   oBrw:GoTop()
   oBrw:Refresh()

return nil

In this sample, right click on the browse will change the recset.

[Maurizio]

Thanks Rao ,
just what I was looking for .
What does -100 mean in oCn:RecSet( cSql, -100 )
I knowo Cn:RecSet( cSql, 100 ) but not with a negative sign

Regards Maurizio

[nageswaragunupudi]

oCn:RowSet( cSql, 100 ) means read first 100 records only
Sameway oCn:RecSet( cSql, 100 ) also means read first 100 records only.

But
oCn:RecSet( cSql, -100 ) means read entire table with page size 100.

RecSet does not read entire table atonce. That will take a very long wait time and also consume huge memory of the PC and sometimes may crash also.
RecSet reads the table in Pages. Displaying the page required page.

In the beginning it reads the first 100 records only and is ready for use. At the same time it also gets the total row count.
If user uses GoBottom() it reads the last 100 records.
If the user wants oRs:GoTo( 123040 ), then it reads 100 records around the row number 123040.

As a result the class always provides the record the programmer wants without unnecessarily occupying the total memory

[Maurizio]

Thanks Rao
excellent clarification

Maurizio