Question about performance RowSet

[Eroni]

Hello all.

Code: Select all  Expand view

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

oCn:QueryResult( "select field1, field2 to my table where field3 = 'x' " ) // 0.21 seconds
 

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.

[carlos vargas]

podria ser que:

Code: Select all  Expand view

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 view

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.

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

[Eroni]

podria ser que:

Code: Select all  Expand view

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 view

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.

[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 view

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

Execute

Code: Select all  Expand view

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 view

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 view

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 } )

[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 view

  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 view

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

[Eroni]

Mr Rao, thank you for tyour attention and informattions.

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 view

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

Execute

Code: Select all  Expand view

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 view

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.

[Eroni]

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

Code: Select all  Expand view

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 view

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.

[Eroni]

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 view

  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 view

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)

[Eroni]

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 view

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.

[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 view

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 view

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

[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 view

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
 

[Eroni]

Hello Mr. Rao.

I am try this:

Code: Select all  Expand view

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 view

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.

But you can use normal Insert like this:

Code: Select all  Expand view

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

Works, faster, I can use it. thank you!

[Eroni]

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 view

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 view

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)

[nageswaragunupudi]

Run time error:

Which version of FWH did you test with please?