Question about performance RowSet

User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Question about performance RowSet

Post by Eroni »

Hello all.

Code: Select all | Expand

oCn:Rowset( "select field1, field2 to my table where field3 = 'x' " ) // [b]1.87 seconds[/b]

oCn:QueryResult( "select field1, field2 to my table where field3 = 'x' " ) // [b]0.21 seconds[/b]
 
that's right?

This difference in accumulated time when I need to obtain data from several tables for editing in multiple browsers becomes very large.
For example, when i have a button to "insert' a new record, is there other way editing data without RowSet object?

Thanks in advance.
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
carlos vargas
Posts: 1721
Joined: Tue Oct 11, 2005 5:01 pm
Location: Nicaragua

Re: Question about performance RowSet

Post by carlos vargas »

podria ser que:

Code: Select all | Expand

oCn:QueryResult( "select field1, field2 to my table where field3 = 'x' " ) // 0.21 seconds
retorna un arreglo simple de datos
y que

Code: Select all | Expand

oCn:Rowset( "select field1, field2 to my table where field3 = 'x' " ) // 1.87 seconds
acá creo que rao hace uso de una estructura de datos propia para poder hacer los filtrados y ordenamiento de forma local (cliente), para no hacer lecturas al servidor constantemente, aunado a que es un objeto bastante complejo, data/var y métodos, que es en lo cual donde realmente toma su tiempo, no tanto en la recuperación del cursor de datos que para ambos métodos es igual.

en lo personal, lo miro normal.

por eso cuando la información que se pretende recuperar es poca, y una sola fila de datos es preferible usar QueryResult, no tiene sentido usar RowSet, dado que no se hará búsqueda, ordenaciones, etc.

otro tip importantes es que, en un recordset, cuando usamos la asignación ( oQry:FIELD1 := 10 ) y lectura de un campo del query ( ?oQry:FIELD1 ), (tanto tmysq y tdolphin, en fwh no lo tengo claro ya que no se proporcionan los fuentes), se hace uso del método error para poder asignar y recuperar la información y eso agrega tiempo adicional, acá Rao podría aclarar.
Salu2
Carlos Vargas
Desde Managua, Nicaragua (CA)
User avatar
carlos vargas
Posts: 1721
Joined: Tue Oct 11, 2005 5:01 pm
Location: Nicaragua

Re: Question about performance RowSet

Post by carlos vargas »

Otro tip adicional es que cuando hacemos una consulta al servidor, la primera vez tomo su tiempo, dado que esa info no existe en la cache del servidor, si en poco tiempo se hace una consulta similar, el tiempo de respuesta se reduce dado que mucha de la info ya está en cache, esto ya es parte de la optimización propia del servidor mysql/mariadb.
Salu2
Carlos Vargas
Desde Managua, Nicaragua (CA)
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

carlos vargas wrote:podria ser que:

Code: Select all | Expand

oCn:QueryResult( "select field1, field2 to my table where field3 = 'x' " ) // 0.21 seconds
retorna un arreglo simple de datos
y que

Code: Select all | Expand

oCn:Rowset( "select field1, field2 to my table where field3 = 'x' " ) // 1.87 seconds
acá creo que rao hace uso de una estructura de datos propia para poder hacer los filtrados y ordenamiento de forma local (cliente), para no hacer lecturas al servidor constantemente, aunado a que es un objeto bastante complejo, data/var y métodos, que es en lo cual donde realmente toma su tiempo, no tanto en la recuperación del cursor de datos que para ambos métodos es igual.

en lo personal, lo miro normal.

por eso cuando la información que se pretende recuperar es poca, y una sola fila de datos es preferible usar QueryResult, no tiene sentido usar RowSet, dado que no se hará búsqueda, ordenaciones, etc.

otro tip importantes es que, en un recordset, cuando usamos la asignación ( oQry:FIELD1 := 10 ) y lectura de un campo del query ( ?oQry:FIELD1 ), (tanto tmysq y tdolphin, en fwh no lo tengo claro ya que no se proporcionan los fuentes), se hace uso del método error para poder asignar y recuperar la información y eso agrega tiempo adicional, acá Rao podría aclarar.
Thank you Carlos.
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
nageswaragunupudi
Posts: 10691
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Question about performance RowSet

Post by nageswaragunupudi »

RowSet
RowSet takes a bit more time, because during creation it collects a lot other relevant information which makes usage faster.
First time reading is a bit slower when compared to other libraries.
Once opened, updates, inserts, deletes, sorts, filters, etc. are all extremely faster than the other libs.
If we look back in the forums, we posted speed comparison with other libraries a few years back.

When a RowSet is opened for ReadOnly, again the initial reading is faster.

Code: Select all | Expand

oRs := oCn:RowSet( cSql, [aParams], [lReadOnly] )
Execute

Code: Select all | Expand

aData := oCn:Execute( cSql, [aParams] )
We get the data in a multi-dimensional array.
In case we need the structure, call this immediately

Code: Select all | Expand

aStruct := oCn:Execute()
QueryResult
This is an extension to Execute.
if we use an sql like "select age from customer where id = 100"
this function returns 58, instead of {{58}}

As you indicated above, if we want to edit just one record in a table, we can

Code: Select all | Expand

aRow := oCn:QueryResult( "select * from states where code='WA'" )
//We get a single dim array { "WA", "Washington" }
// Edit the array and then
oCn:Upsert( "states", nil, { aRow } )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
Posts: 10691
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Question about performance RowSet

Post by nageswaragunupudi »

when i have a button to "insert' a new record, is there other way editing data without RowSet object?
Please try this and let us know.

Code: Select all | Expand

   oData := TArrayData():New( oCn, "select * from <tablename> limit 1" )
   oData:Record(,.t.):Edit()
   oData:SaveData()
I am thinking of adding method to get a single record from a table ( existing or new ) and then edit and save the data.
May be something like this:

Code: Select all | Expand

oRecord := oCn:Record( cSql, [lBlank] ) // returns FW_Record / TDataRow object
oRecord:Edit()
Regards

G. N. Rao.
Hyderabad, India
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

Mr Rao, thank you for tyour attention and informattions.
nageswaragunupudi wrote:RowSet
RowSet takes a bit more time, because during creation it collects a lot other relevant information which makes usage faster.
First time reading is a bit slower when compared to other libraries.
Once opened, updates, inserts, deletes, sorts, filters, etc. are all extremely faster than the other libs.
If we look back in the forums, we posted speed comparison with other libraries a few years back.

When a RowSet is opened for ReadOnly, again the initial reading is faster.

Code: Select all | Expand

oRs := oCn:RowSet( cSql, [aParams], [lReadOnly] )
Execute

Code: Select all | Expand

aData := oCn:Execute( cSql, [aParams] )
We get the data in a multi-dimensional array.
In case we need the structure, call this immediately

Code: Select all | Expand

aStruct := oCn:Execute()
QueryResult
This is an extension to Execute.
if we use an sql like "select age from customer where id = 100"
this function returns 58, instead of {{58}}
That is ok, works fine.
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

nageswaragunupudi wrote: As you indicated above, if we want to edit just one record in a table, we can

Code: Select all | Expand

aRow := oCn:QueryResult( "select * from states where code='WA'" )
//We get a single dim array { "WA", "Washington" }
// Edit the array and then
oCn:Upsert( "states", nil, { aRow } )
Mr Rao, this works when there exists any record from select.
When the select there no result, insert does not work.

I am try this:

Code: Select all | Expand

aRow := oCn:QueryResult( "select field1,field2 from states where code='WA'" )
if aRow == NIL // not found, then inserts
    aRow := { "aa","bb" } 
    oCn:Upsert( "states", nil, { aRow } )
endif
But insert not work.

I have many processes for generating payroll for workers (40 thousand workers) in which the above situation occurs.
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

nageswaragunupudi wrote:
when i have a button to "insert' a new record, is there other way editing data without RowSet object?
Please try this and let us know.

Code: Select all | Expand

   oData := TArrayData():New( oCn, "select * from <tablename> limit 1" )
   oData:Record(,.t.):Edit()
   oData:SaveData()
This code generated a runtime error:

Code: Select all | Expand

Código           : 1
Descrição do Erro:
Erro BASE/1123  Erro nos parƒmetros: AADD
Código: 1
Detalhamento:
------------
Arg. 1      Tipo: U Valor: 
Arg. 2      Tipo: N Valor: 1

Chamada de Pilha
================
Chamado de AADD(0)
Chamado de (b)TARRAYDATA:LOADMYSQL(618)
Chamado de AEVAL(0)
Chamado de TARRAYDATA:LOADMYSQL(618)
Chamado de TARRAYDATA:FROMQUERY(456)
Chamado de TARRAYDATA:FROMMYSQL(366)
Chamado de TARRAYDATA:NEW(255)
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

nageswaragunupudi wrote:
I am thinking of adding method to get a single record from a table ( existing or new ) and then edit and save the data.
May be something like this:

Code: Select all | Expand

oRecord := oCn:Record( cSql, [lBlank] ) // returns FW_Record / TDataRow object
oRecord:Edit()
This would be very good, I believe, for many programmers.

Thany you Mr Rao.
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
nageswaragunupudi
Posts: 10691
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Question about performance RowSet

Post by nageswaragunupudi »

I am try this:
Code:
aRow := oCn:QueryResult( "select field1,field2 from states where code='WA'" )
if aRow == NIL // not found, then inserts
aRow := { "aa","bb" }
oCn:Upsert( "states", nil, { aRow } )
endif


But insert not work.
Table `states` has 3 fields: id, code, name where id is autoincrement field

This should work:

Code: Select all | Expand

oCn:Upsert( "states", nil, { { 0, "KK", "KState" } } )
For Upsert() to work, we need to include primary key also.

But you can use normal Insert like this:

Code: Select all | Expand

oCn:Insert( "states", "code,name", { { "KK", "KKKK" } } )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
Posts: 10691
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Question about performance RowSet

Post by nageswaragunupudi »

Please try this for adding new record.
This is working for me and should work for you too.
First run this program as it is without changes before you adopt to your tables.

Code: Select all | Expand

function MariaNewRecord()

   local oCn := maria_Connect( "209.250.245.152,fwh,fwhuser,FiveTech@2022" )
   local oData

   oData := TArrayData():New( oCn, "states", "FALSE" )
   oData:Edit()
   oData:SaveBatch()

   xbrowser ocn:states

return nil
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

Hello Mr. Rao.
nageswaragunupudi wrote:I am try this:

Code: Select all | Expand

aRow := oCn:QueryResult( "select field1,field2 from states where code='WA'" )
if aRow == NIL // not found, then inserts
    aRow := { "aa","bb" }
    oCn:Upsert( "states", nil, { aRow } )
endif
 
But insert not work.
Table `states` has 3 fields: id, code, name where id is autoincrement field

This should work:

Code: Select all | Expand

oCn:Upsert( "states", nil, { { 0, "KK", "KState" } } )
For Upsert() to work, we need to include primary key also.
My table have 20 fields, but in this part of process I need edit or insert just a few.
nageswaragunupudi wrote:But you can use normal Insert like this:

Code: Select all | Expand

oCn:Insert( "states", "code,name", { { "KK", "KKKK" } } )
Works, faster, I can use it. thank you!
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
Eroni
Posts: 100
Joined: Fri Jul 21, 2006 7:15 pm
Location: Criciuma/SC Brazil
Contact:

Re: Question about performance RowSet

Post by Eroni »

nageswaragunupudi wrote:Please try this for adding new record.
This is working for me and should work for you too.
First run this program as it is without changes before you adopt to your tables.

Code: Select all | Expand

function MariaNewRecord()

   local oCn := maria_Connect( "209.250.245.152,fwh,fwhuser,FiveTech@2022" )
   local oData

   oData := TArrayData():New( oCn, "states", "FALSE" )
   oData:Edit()
   oData:SaveBatch()

   xbrowser ocn:states

return nil
 
Run time error:

Code: Select all | Expand

Código           : 1
Descrição do Erro:
Erro BASE/1081  Erro nos parƒmetros: +
Código: 1
Detalhamento:
------------
Arg. 1      Tipo: C Valor:  where 
Arg. 2      Tipo: O Valor: TARRAYDATA

Chamada de Pilha
================
Chamado de TARRAYDATA:LOADMYSQL(607)
Chamado de TARRAYDATA:FROMMYSQL(391)
Chamado de TARRAYDATA:NEW(255)
Chamado de MARIANEWRECORD(190)
FWH 1709 BCC72 MySql MariaDB
Visual Studio 2019 / Xamarin / C#
User avatar
nageswaragunupudi
Posts: 10691
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Question about performance RowSet

Post by nageswaragunupudi »

Run time error:
Which version of FWH did you test with please?
Regards

G. N. Rao.
Hyderabad, India
Post Reply