Client Server options

[lucasdebeltran]

Hi Carles,

Thanks for reply.

Can you use your ADS in xBrowse as an RDD more? Of course your can do it, but I do not think is the best way. I think the best is to create query, load and manage data in internal tables, and finally to work in some browse, no matter wich of them.

So that´s that does my code but the performance is not as good as TDolphin.

Maybe I miss something?.

Rick, thank you about ADO. I will check it.


Thanks.

[lucasdebeltran]

Reinaldo,

Here is a working sample:

Code: Select all  Expand view

// Harbour Advantage RDD sample

#include "FiveWin.ch"
#include "ads.ch"
#include "xbrowse.ch"

Request ADS,ADSKeyCount,ADSKeyNo,OrdKeyCount,OrdKeyNo, AdsGetRelKeyPos, AdsSetRelKeyPos

//----------------------------------------------------------------------------//

function Main()

     local aArray := {}, cDbf, i

     local nInicio, nFinal

     local lCopy := .F.



     // Driver CDX----------------------------------------------------------------
     REQUEST DBFCDX, DBFFPT


     RddRegister( "ADS", 1 )
     RddSetDefault( "ADS" )

     //setting up server type
     // 1 = Local Server (no client / server)
     // 2 = Remote Server
     // 3 = Local + Remot
     // 4 = Internet Server
     // 5 = Local + Internet
     // 6 = Remote+Internet
     // 7 = Local + Remote + Internet , best choice
     adsSetServerType( 4 )  // ó  adsSetServerType( ADS_REMOTE_SERVER )

     //setting up the file types
     // 1 = DBFNTX
     // 2 = DBFCDX
     // 3 = ADSADI
     adsSetFileType( ADS_CDX )  // o ADS_CDX

     AdsRightsCheck(.F.)









   IF AdsConnect60( "\\189.228.227.218:2000\cursos\adsdemo\cibconwn\stc\internet.add", 4,"demo","demo")


       MsgInfo("Esta ahora conectado al Sevidor AIS de . en Mexico "+CRLF+CRLF+;
        "You are now connected to AIS Server","Bienvenido / Welcome / Versión "+ADSVersion())



   ELSE

      MsgStop("Se ha rechazado la conexión al servidor AIS, por favor intente mas tarde"+CRLF+CRLF+;
         "Conection to AIS Server rejected, please, try again later","AIS Error")
      QUIT

   ENDIF




   USE CATCUE SHARED NEW ALIAS "DATOS"
   DATOS->(AdsCacheRecords(50))
   DATOS->(OrdSetFocus("NUMCUENTA"))
   DATOS->(DBGOTOP())


   sysrefresh()






   nInicio := seconds()

   aArray := FW_DbfToArray()

   nfinal := seconds()
   msgalert(nFinal-nInicio, " DURACION de carga Array ")







   nInicio := seconds()

   COPY TO "RENE.DBF" VIA "DBFCDX"

   nfinal := seconds()
   msgalert(nFinal-nInicio, " TIME CopyTable ")




   XBROWSER aArray

   dbcloseall()
   quit

return nil

//----------------------------------------------------------------------------//

It takes up to 10 seconds to load into the Array or copy file.

It is not very good performance.

Any clue?.

Thanks

[reinaldocrespo]

So, you're bringing a whole .dbf across the wire and you are asking why is it slow? Don't bring a whole table across the wire. That's the easy answer. The question should be, why are you bringing a whole .dbf table across the wire? The speed at which a table comes across a wire, depends on the speed of the wire.

I don't know what problem you are trying to solve. Maybe you need synchronized local copies of a remote table, if so then maybe you should read about ADS replication.

Again, so far everything I see from your code does not show a problem with AIS. It only shows a problem with what you are trying to do with it. I'm sorry I can't help you.

Best regards,


Reinaldo.

[lucasdebeltran]

Reinaldo,

I need to browse the full table. That´s what my software does and my customer except. I can´t just eliminate the browse to solve the problem.

I tested with ADS in two samples posted -last one using COPY TO LOCAL VIA "DBFCDX" and AdsCreateSQL...-. In both cases, the performance is very very bad. Maybe I am losing a third way.

Also, my settings in ADS.INI are (thanks to Francis advice):

[Settings]
COMPRESSION = Always
USE_TCP_IP = 1
PACKET_SIZE = 1024 //;or 1024 O 512


Without them, it takes 2 seconds more to accomplish the task.


But with Daniel´s TDolphin I can browse the full table almost inmediate (yes, the full table over the wire!!!).

So the real point is that there is an important problem with performance with ADS and Harbour. This is the real point.

[carlos vargas]

lucas si usa sql en ads intenta realizar la instruccion pidiendo un cursos estatico el cual te muestra los datos inmediato
ejemplo

Code: Select all  Expand view

select {static} codigo, nombre from clientes
 

cuando no se agrega {static} el cursor enviado por l servidor esta vivo y este toma mas tiempo mostrarse en un browse.

salu2
carlos vargas

[lucasdebeltran]

Carlos,

Many thanks.

With your great tip, I noticed an important increase of speed. Very very good.

Now the performance is much better with xBrowse.

But it also takes up to 30 seconds to load the table into the array. If I load the table in local mode the process takes 0.5 segs.

[lucasdebeltran]

Carlos,

Y en mi ejemplo, si necesito refrescar los datos del DBF temporal creado por ADS con el resultado de la consulta, ¿debo repetir la operación o hay algo parecido a un Refresh() del ADSExecuteSQLDirect( cSql )?.

Muchas gracias.

Code: Select all  Expand view

function Test_SQL()

   LOCAL cSql   := ""
   LOCAL aArray := {}

   LOCAL cAlias := Alias()




   /*al inicio de la instruccion sql se define un area vacia*/
   DBSelectArea(0)


   /*se define instruccion sql en una cadena de texto*/
   cSql := "SELECT {static} * FROM CUSTOMER"
   msgINFO(csql, "cSql")


   /*crea instruccion sql y pasa los datos a tabla temporal*/
   ADSCreateSQLStatement( "REPTMP", 2 )


   IF ADSExecuteSQLDirect( cSql )

      DBSelectArea("REPTMP")

      REPTMP->( DBGoTop() )


      IF LastRec() > 0  // !REPTMP->( Eof() )

         /*lista resultado en tabla*/
         xbrowse()
         //aArray := FW_DbfToArray()
         //XBROWSER aArray

      ELSE

         /*no se encontro datos que cumpla condicion sql*/
         MsgInfo( "No se ha encontrado REGISTROS" )

      ENDIF

      /*cierra tabla de datos temporal*/
      REPTMP->( DBCloseArea() )




   ELSE

      Alert( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) )



   ENDIF



   /*se selecciona area de datos anterior*/
   IF !Empty(cAlias)
      DBSelectArea(cAlias)
   ENDIF

   /*da foco al browse*/


RETURN NIL
 

[Marcelo Via Giglio]

Hola,

en ese caso, no creo que un cursor se pueda actualizar, salvo que lo vuelvas a crear, entonces para tu fin podrías intentar crear una tabla temporal en lugar de un cursos, me imagino que el tiempo de creación debería ser similar al del cursor.

Para actualizarla, no le veo complicado actualizar los registros modificados o los nuevos registros, esto lo puedes hacer bajo demanda, temporizado o utilizando las notificaciones de ADS ( habrá gente que sabrá como implementarlas en xHarbour, yo no lo hice), para estas cosas puedes utilizar MERGE, ahora para las eliminaciones puede que con triggers sea una opción.

Yo creo que este mismo problema tienes con tDolphin, salvo como tu dices las consultas con el son mucho mas rápidas por lo que no habría problema de reconsultar todo para refrescar la información

Son solo ideas, haber si nos comentas que haces

saludos

Marcelo

[reinaldocrespo]

I would think that to browse a remote table over an internet connection you would have to browse an SQL result sets with pagination (regardless of the sql engine). Lucas thinks he is bringing a whole table over the wire with Tdolphin, but I doubt so. That would be slow no matter the sql engine being used. I'm guessing that TDolphin is suppling result pages to xbrowse via the SetDolphin() method in xBrowse. This makes xBrowse smart enough to browse sql result sets from tdolphin.

xBrowse does not have a special method to do the same with an ADS sql result set. It might be a good idea for the fw community to add a method to xBrowse to enable browsing an ADS SQL result set, **but** before SetAds() method can be added to xbrowse, we would need to have a tAdsQuery() class to take care of methods, Gotop(), GoBottom(), Bof(), Eof(), LastRec(), RecNo(), Goto(), FieldPos(), FieldName(), FieldLen(), FieldDec, PrevPage() and NexPage() with pagination from an ADS sql query.

If we can recruit someone (Mr. Rao?) to work with the xbrowse part, then I would help with the TAdsQuery class. Again, adding SetAds() method to the xbrowse class would only be useful when xbrowsing tables over an internet connection from a fw/harb app.



Reinaldo.

[lucasdebeltran]

Hi friends,

First of all, I want to thank you all your help and valuable tips. We have narrowed the problem and we are very close to solve it. Remember, all except browsing features work fine with AIS over internet, with very few changes in code. This is very important and very reliable solution, with much more advantages than using Terminal Server.

Also, I want to thank Antonio for mantaining this strong and very helpfull community.

This is the sample with Carlos tip. The performance is much better than before:

Code: Select all  Expand view

// Harbour Advantage RDD sample

#include "FiveWin.ch"
#include "ads.ch"
#include "xbrowse.ch"

Request ADS,ADSKeyCount,ADSKeyNo,OrdKeyCount,OrdKeyNo, AdsGetRelKeyPos, AdsSetRelKeyPos

//----------------------------------------------------------------------------//

function Main()

     local aArray := {}, cDbf, i

     local nInicio, nFinal

     local lCopy := .F.



     // Driver CDX----------------------------------------------------------------
     REQUEST DBFCDX, DBFFPT


     RddRegister( "ADS", 1 )
     RddSetDefault( "ADS" )

     //setting up server type
     // 1 = Local Server (no client / server)
     // 2 = Remote Server
     // 3 = Local + Remot
     // 4 = Internet Server
     // 5 = Local + Internet
     // 6 = Remote+Internet
     // 7 = Local + Remote + Internet , best choice
     adsSetServerType( 4 )  // ó  adsSetServerType( ADS_REMOTE_SERVER )

     //setting up the file types
     // 1 = DBFNTX
     // 2 = DBFCDX
     // 3 = ADSADI
     adsSetFileType( ADS_CDX )  // o ADS_CDX

     AdsRightsCheck(.F.)









  IF AdsConnect60( "\\c i b er-tec.d2g.com:2000\cursos\adsdemo\cibconwn\stc\internet.add", 4,"demo","demo")

       MsgInfo("Esta ahora conectado al Sevidor AIS de . en Mexico "+CRLF+CRLF+;
        "You are now connected to AIS Server","Bienvenido / Welcome / Versión "+ADSVersion())



   ELSE

      MsgStop("Se ha rechazado la conexión al servidor AIS, por favor intente mas tarde"+CRLF+CRLF+;
         "Conection to AIS Server rejected, please, try again later","AIS Error")
      QUIT

   ENDIF






   USE CATCUE SHARED NEW ALIAS "DATOS"
   DATOS->(AdsCacheRecords(50))
   DATOS->(OrdSetFocus("NUMCUENTA"))
   DATOS->(DBGOTOP())


   sysrefresh()


   Test_SQL()
   DBCLOSEALL()

   QUIT




return nil

//----------------------------------------------------------------------------//



//-----------------------------------------------------------------------------



//-----------------------------------------------------------------------------
function Test_SQL()

   LOCAL cSql   := ""
   LOCAL aArray := {}

   LOCAL cAlias := Alias()

   local nInicio, nFinal





   /*al inicio de la instruccion sql se define un area vacia*/
   DBSelectArea(0)


   /*se define instruccion sql en una cadena de texto*/
   //cSql := "SELECT * FROM BIBLIO ORDER BY TITULO"
   cSql := "SELECT {static} *  FROM CATCUE ORDER BY DESCRIPCIO"

   msgINFO(csql, "cSql")




   /*crea instruccion sql y pasa los datos a tabla temporal*/
   ADSCreateSQLStatement( "REPTMP", 2 )


   IF ADSExecuteSQLDirect( cSql )

      DBSelectArea("REPTMP")


      IF LastRec() > 0  // !REPTMP->( Eof() )

         /*lista resultado en tabla*/
         
         dbgotop()
         xbrowse()


      ELSE

         /*no se encontro datos que cumpla condicion sql*/
         MsgInfo( "No se ha encontrado REGISTROS" )

      ENDIF

      /*cierra tabla de datos temporal*/
      REPTMP->( DBCloseArea() )




   ELSE

      Alert( "AdsExecuteSQLDirect() failed with error "+ Str( ADSGetLastError() ) )



   ENDIF



   /*se selecciona area de datos anterior*/
   IF !Empty(cAlias)
      DBSelectArea(cAlias)
   ENDIF

   /*da foco al browse*/


RETURN NIL
 

Hope Mr. Nages and Daniel could help us with xBrowse. In this case, we will build a very easy way to enable cliente/server to our software with very very little changes.


Thanks again for this very instructive debate.

[reinaldocrespo]

Lucas;

It is not going to change speed of the SQL cursor xbrowsing, but I think you can save these lines as they are not doing anything. You don't need to open the table when using SQL.

Code: Select all  Expand view

//the lines below are not doing anything.

USE CATCUE SHARED NEW ALIAS "DATOS"
   DATOS->(AdsCacheRecords(50))
   DATOS->(OrdSetFocus("NUMCUENTA"))
   DATOS->(DBGOTOP())


   sysrefresh()
 

Reinaldo.

[lucasdebeltran]

Thank you.

I have them as I did other tests, such as append blank, locate, etc which are working very well.

Do you think any way to update/refresh the cursor?. It is not a handicap, as I can always repeat the query.

Thanks again.

[reinaldocrespo]

A static cursor a new table based on the old table (or tables). It is not refreshed when the base table changes. What you want is a Dynamic cursor (remove the {static} clause). A Dynamic cursor is actually faster than a static cursor. The problem is that xBrowse requests the full result set every time you move. If xbrowse was fixed to be used with ADS, then a dynamic (live) cursor would be much faster to xbrowse.

Here is the difference between a live (also called dynamic) cursor vs a static cursor from the help file:

A live cursor is constructed by essentially putting a filter on the base table so that only the requested rows are visible. Conversely, a static cursor is actually a new table dynamically built with the requested rows of the original table or tables. Because the live cursor does not move any records around, it is faster to retrieve as a rowset. However, a static cursor is not completely populated when it is created.

A form of "caching" is used to add a certain number of rows at a time to the static cursor, so that control is returned to client in a reasonable time. The static cursor is populated as the rowset is traversed. This results in a more immediate response to the query, and processing is not wasted if only the first portion of the rowset is traversed. Note that the following operations will cause the static cursor rowset to be fully populated: Last (or Go Bottom), RecCount, Create Index.

For either live or static cursors, a cursor handle is utilized in a manner analogous to a table handle. This cursor handle can be used to navigate the resulting cursor in a fully scrollable manner. For all intents and purposes, the cursor can be treated like a table. Static cursors cannot be modified, but modifications on live cursors are reflected in the source table.

A live cursor can be much faster than a static cursor. However, this performance difference has a trade-off against features that can only be used on a static cursor. A live cursor is used if the SELECT statement does not contain any of the following:

· • DISTINCT in the SELECT clause

· • Joins (inner, outer, self, or UNION)

· • Any aggregate function

· • GROUP BY or HAVING clauses

· • Subqueries

· • Certain scalar functions (see Scalar Functions in a WHERE Clause)

· • If a memo field is used in a WHERE clause (e.g., WHERE memo is null)

· • LIKE operator is used in a WHERE clause (e.g.,. WHERE lastname LIKE 'Smith%)

· • Expressions or scalar functions in the select list (e.g., select UCASE(lastname) … )

· • TOP in the SELECT clause

· • WHERE clause contains a large expression that is not supported by the expression engine

It is possible to force Advantage to produce a static cursor on a SELECT statement that would normally result in a live cursor. To do this, use the {static} escape sequence after the SELECT keyword. For example:



SELECT {static} * FROM emp WHERE hire_date < '1990-01-14'



Without the escape sequence, Advantage would create a live cursor for that query. With {static} specified, though, Advantage will create a static cursor. From a performance standpoint, it is probably better in most circumstances to allow Advantage to create live cursors when possible.

If, though, the WHERE clause is very restrictive (thus producing a small rowset) and the base table is very large, it may be faster to force a static cursor. This is because the live cursor would be implemented with a filter on the server. If the client application were to traverse the rowset multiple times, the server would have to filter the rowset each time. If the client forced the cursor to be static, however, the server may be able to quickly seek directly to the data for the rowset with minimal cost. And once the cursor is created, the server would not be required to do any filtering when the client traversed the rowset.

See Also

Cursor Types

Reinaldo.

[reinaldocrespo]

Here is a good explanation of the difference between a live cursor and a static cursor from the help file:

Cursors are produced by executing SQL SELECT statements. Technically, a cursor is considered to be the mechanism by which the results of a query are manipulated and traversed. More generally, however, a cursor is often equated with the rowset itself. Cursors produced by Advantage are either live or static.

An Advantage live cursor represents a dynamic, server-side, scrollable cursor. With live (dynamic) cursors, updates to the base table are reflected in the cursor. For example, consider "select * from employee where lastname = ‘Smith’". If a record is appended to the employee table and the new record’s lastname field is set to ‘Smith’, that new record will be inserted into the cursor. Likewise, an update will remove a record from the cursor if the lastname field is changed.

Advantage static cursors cannot be updated, nor are updates to the base tables of a static cursor reflected in the cursor itself. An example of a query that results in a static cursor is "select * from orders, customers where order.custid = customers.custid." Once the rowset produced by that query is fully populated, updates to the orders and customers tables will have no effect on the cursor.

It is important to note that there are two specific cases where table updates made by one user are not reflected in another user's dynamic cursor. Advantage Local Server does not automatically detect changes in tables that are made by other clients. This means that updates made by applications using Advantage Local Server are propagated only to cursors owned by that same application. Similarly, another situation where updates are not reflected in live cursors is with Advantage Database Server when it uses the compatibility locking mode in order to share DBF tables with third-party applications. If a third-party application updates a table, the Advantage Database Server does not automatically detect that update and, therefore, it will not update cursors based on that table.

Reinaldo.

[carlos vargas]

el culpable de la lentitud en los browse es la funcion ordkeycount, la cual para obtener su valor de retorno tiene que recorrer todo el cursor.
y para remate el bloque de codigo bkeyCount en xbrowse que es el responsable de obtener el valor de cuantos registros tiene el browse, se ejecuta cada tanto.
haciendo la presentacion de un browse extremadamente lenta.

es por ello que en los grid de delphi, de forma estandar el scrollbar vertical no es funcional completamente, solo funciona el movimiento hacia el inicio y hacia el final.
(TOP y BOTTOM) y el thumbnail esta siempre al centro, no funciona desplazar el thumbnal hacia arriba o abajo. (SKIP 1 o SKIP -1) esto para no estar calculando la posicion relativa del cursor y posicionar el thumbnail en la posicion correcta, ya que para hacer esto necesitaria estar costantemente calculando cuantos registros hay (llamando a adskeycount por supuesto) y cualcular la pos relativa, esto es por lo cual los grid en delphi dan la apariencia de ser rapidos con ads, la verdad es que para ambientes cliente servidor la funcion adskeycount se debe evitar como a la peste bubonica.

lo que habria que hacer es modificar los browse de fwh para que tengan el comportamiento similar al de delphi.

esto lo he deducido viendo arc, y un poco con el poco ingles que entiendo leyendo el help de ads.
asi que no es una ley que aparecio en la biblia. , puedo estar muy equivocado.

salu2
carlos vargas