Page 1 of 1
RAO advice on using Mysql
Posted: Tue Jan 30, 2024 2:32 pm
by 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
Re: RAO advice on using Mysql
Posted: Tue Jan 30, 2024 3:51 pm
by 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.
Re: RAO advice on using Mysql
Posted: Thu Feb 01, 2024 11:37 am
by 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
Re: RAO advice on using Mysql
Posted: Thu Feb 01, 2024 6:17 pm
by 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?
Re: RAO advice on using Mysql
Posted: Fri Feb 02, 2024 7:50 am
by Maurizio
Hello
I tested on a table of 700,000 records , 350000Kb
Regards Maurizio
Re: RAO advice on using Mysql
Posted: Fri Feb 02, 2024 12:48 pm
by 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.
Re: RAO advice on using Mysql
Posted: Fri Feb 02, 2024 12:55 pm
by 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.
Re: RAO advice on using Mysql
Posted: Fri Feb 02, 2024 2:24 pm
by 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
Re: RAO advice on using Mysql
Posted: Tue Feb 06, 2024 5:34 pm
by 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
Re: RAO advice on using Mysql
Posted: Wed Feb 07, 2024 4:43 am
by 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.
Re: RAO advice on using Mysql
Posted: Wed Feb 07, 2024 8:03 am
by 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
Re: RAO advice on using Mysql
Posted: Wed Feb 07, 2024 10:06 am
by 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
Re: RAO advice on using Mysql
Posted: Wed Feb 07, 2024 1:58 pm
by Maurizio
Thanks Rao
excellent clarification
Maurizio