ADS -> Optimizacion de consultas SQL

[Marcelo Via Giglio]

Holas,

bueno segun se, si haces una consulta sobre una tabla sin realizar un join, es decir sin que intervenga otra tabla, lo que ADS nos devuelve es un cursor dinamico y por eso que ARC te da las cabeceras en mayusculas, pero en tu aplicacion xHarbour tu optienes ese cursor dinamico en la tabla temporal, aqui lo interesante, podriamos probar de realizar modificaciones sobre ese cursor (resultado) y deberiamos ver que las modifiacciones seran reflejadas en la tabla original, no pasara esto cuando la consulta se haga sobre mas de una tabla donde el resultado sera un cursor estatico.

Con esto da que pensar que si ADS con una consulta estatica tan solo aplica un filtro sobre la tabla objeto ( Como lo dice fernando )

Creo que deberia ayudarnos algun guru de ADS

saludos

Marcelo

[Carles]

Marcelo, Fernando,

Gracias por vuestras opiniones. Intentare realizar ejemplos de testeo si puedo este fin de semana, aplicando filtros AOF, consultas dinamicas y estaticas, a ver q conclusion puedo sacar. Cuando termine, intentare publicarlo aqu.

Gracias.

[Marcelo Via Giglio]

Carles,

es como te digo una consulta sobre una sola tabla te genera un cursor dinamico cuya modificacion la hace ne la tabla original, eso significa que el resultado es la tabla original con un filtro aplicado sobre ella, como lo dijo Fernando.

Haber pero esperemos que pasa el fin de semana con tus pruebas

saludos

Marcelo

[Carles]

Hola Marcelo,



Si, si..., esta claro pero entonces por lo que estoy experimentando, una consulta sobre una sola tabla es muuuucho mas lento, que aplicar un DBSetFilter(). Si yo creo una consulta sobre una tabla de 1 millon de registros, con esta select "Select {static} first, last, state, age form test where age > 90 and state = 'DE' ", usando la funcion ADSExecuteSqlDirect(), el sistema demora aprox. 4.5 seg. En cambio si uso DBSetFilter( bCode, cCode ), el sistema va como una bala 0.02 seg., y con la diferencia que puedes actualizar la tabla.

Uhmmm, tiene que haber algo mas que diferencie la aplicacion del filtro (segun el help un live cursor) y un static cursor. La pregunta seria: Que se puede hacer o que ventajas tiene un filtro frente a un static cursor.

En fin, vamos a ver...

Buen fin de semana.

[fgondi]

Hola,

Es normal que ADSExecuteSqlDirect() tarde mas que DbSetFilter para la sql que indicas (Select {static} first, last, state, age form test where age > 90 and state = 'DE'). Pero ojo depende del número de registros que cumplan la condición.

Si son muchos los que cumplen, es mejor usar DbSetFilter, ya que ADSExecuteSqlDirect() tiene que crear un DBF temporal con todos los registros, osea, uno a uno ir añadiendo registros en una DBF. Sin embargo con DbSetFilter lo único que haces es aplicar un filtro sobre una tabla ya abierta.

Sin son pocos los que cumplen, es mejor usar ADSExecuteSqlDirect(), ya que no va a tardar en añadirlos a la DBF temporal que crea y sin embargo DbSetFilter te puede dar problemas si vas a usar un browse, porque problamente para saltar de un registro a otro tarde, al tener que validar si los registros intermedios cumplen la condición indicada.

[Carles]

Hola,

Finalmente pude realizar mis testeos y aquí os mando un resumen. Si alguien cree q estoy errado o quiere ampliar en algo, le agradeceria q lo posteara.

Un cursor es el controlador de una tabla que se ha producido como consecuencia de realizar una consulta via SQL. Estos cursores los podemos diferenciar como dinamicos o estaticos. Dichos cursores los podemos gestionar como los controladores de una tabla y su funcionalidad es la misma.

El hecho de usar la funcion AdsExecuteSqlDirect(), hace que ADS trabaje de 2 posibles maneras.

a.- Cursor dinamico. Si puede aplicar un filtro a la tabla, crea una vista con los campos implicados y devuelve el cursor. Es posible la modificacion del registro.

b.- Cursor estatico. Se crea una tabla con los campos implicados y rellena los registros con los que cumplen la sql. No es posible su modificacion.

Es posible en algun caso forzar un cursor estatico poniendo la clausula {estatic}. Mas adelante describo un caso en que es aconsejable su uso

Que diferencias, sin embargo, existen entre un tipo de cursor y otro., El cursor estatico es mas lento de crearse porque se crea la tabla y se rellena con los registros resultantes pero en la consulta podemos usar (sacado del help de ads):

• 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

Es logico pensar que si no usamos una de estas clausulas, crear un filtro en la tabla via DbSetFilter(), es lo mejor, pero tenemos de analizar los caso en que aplicar un sistema u otro en funcion del tamaño de la tabla, el numero de aciertos conseguidos y el tipo de consulta:

Los test realizados son a partir de la tabla Test.dbf con 1 millon de registros. En el test, se intenta:

1.- Aplicar un DbSetFilter() y AdsExecuteSqlDirect() (en algun caso forzar la consulta statica).
2.- Consulta sobre un campo normal y con indice
3.- En el caso en que hay 3 registros de resultado se han insertado previamente en el registro 1, 500000 y 1000000.

Los resultados los tengo en una hoja excel y los he intentado plasmar en un fichero txt. Espero que se entienda.

Code: Select all  Expand view

****
Test
****

Id Reg
   aprox Index  Funcion                Consulta
-- ----- -----  ---------------------  -----------------------------------------------------------------------------------
1   2200        AdsExecuteSQLDirect()  Select first, last,state, age from test where age > 90
2               AdsExecuteSQLDirect()  Select {static} first, last,state, age from test where age > 90
3               DbSetFilter()          age > 90

4  80000  first AdsExecuteSQLDirect()  Select first,last,state, age form test where first >= 'A' and first <= 'B'
5               AdsExecuteSQLDirect()  Select {static} first,last,state, age form test where first >= 'A' and first <= 'B'
6               DbSetFilter()          first >= 'A' .and. First <= 'B'

7               AdsExecuteSQLDirect()  Select first,last,state, age form test where last >= 'A' and last <= 'B'
8               AdsExecuteSQLDirect()  Select {static} first,last,state, age form test where last >= 'A' and last <= 'B'
9               DbSetFilter()          last >= 'A' .and. last <= 'B'

10     3  first AdsExecuteSQLDirect()  select first, last, state, age from test where first = 'Dummy'
11              AdsExecuteSQLDirect()  select {static} first, last, state, age from test where first = 'Dummy'
12              DbSetFilter()          first = 'Dummy'

13              AdsExecuteSQLDirect()  select first, last, state, age from test where last = 'Dummy'
14              AdsExecuteSQLDirect()  select {static} first, last, state, age from test where last = 'Dummy'
15              DbSetFilter()          last = 'Dummy'

16  8648  first AdsExecuteSQLDirect()  Select * from test where first like 'Fra%'
17              AdsExecuteSQLDirect()  Select {static} * from test where first like 'Fra%'
18              DbSetFilter()          ***

19              AdsExecuteSQLDirect()  Select * from test where last like 'Fra%'
20              AdsExecuteSQLDirect()  Select {static} * from test where last like 'Fra%'
21              DbSetFilter()          ***

22     3  first AdsExecuteSQLDirect()  Select * from test where first like 'Fra%'
23              AdsExecuteSQLDirect()  Select * from test where last like 'Fra%'
24              DbSetFilter()          ***

**********
Resultados
**********

Id  Tiempo  Listar
    Funcion 100 reg. Browse()  Update Trafico
--  ------- -------- --------  ------ -------
1    38,72    0,03   Muy Lento   Si      Si
2     8,34    0,03    Rapido
3     0,02    0,01    Rapido     Si

4    26,99    0,03   Muy Lento   Si      Si
5        3    0,03    Rapido
6     0,02    0,02    Rapido     Si

7     9,31    0,03   Muy Lento   Si      Si
8     3,88    0,03    Rapido
9        0    0,02    Rapido     Si

10    0,03    0,09    Lento      Si      Si
11    0,01    0       Rapido
12    0,01    1,59    Lento      Si

13    1,73    0,09    Lento      Si      Si
14   12,88    0       Rapido
15    0,03    8,63    Lento      Si

16    0,05    0,03    Rapido
17    2,91    0,03    Rapido
18

19   16,33    0,03    Rapido
20   17,92    0,03    Rapido
21

22    0,03    0,02    Rapido
23   16,34    0,02    Rapido
24


Es curioso al final, como mi principal obsesion, que era la de ir ‘rapido’ como Arc32 se ha cumplido.

Code: Select all  Expand view

1.- En el caso de la busqueda last = ‘Dummy’ (3 registros):

   Arc32             9.2
   DbSetFilter()          8.63

2.- En el caso de la busqueda first = ‘Dummy’ (3 registros con indice)

   Arc32            0.33   
   AdsExecuteSqlDirect()      0.32

3.-    En el caso first = ‘Fra%’   (indice)   

   Arc32            0.04
   AdsExecuteSqlDirect()      0.05

4.-    En el caso last = ‘Fra%’      

   Arc32            17.78
   AdsExecuteSqlDirect()      16.33




Todo depende de q funcion usar y para que (si queremos porcesar en tablas internas, en browse,…). Gracias Fernando y Marcelo por vuestra ayuda. Si alguien quiere el programa de test para ampliar sus testeos, que me lo pida que se lo mandare.

Nota: No queria dormir a nadie, pero es lo q hay...

[MarioG]

Retomo este hilo debido a que estoy porbando trabajar con SQL en ADS.
Utilizao la versión 6.2, de ADS.
Tengo lo siguiente

Code: Select all  Expand view

      cCount   := 'Select Count(*) FROM '+::aDbFile[_LOCALIDAD]+" where CProv='"+cCProv+"'", ;
      cConsulta:= 'Select * FROM '       +::aDbFile[_LOCALIDAD]+" where CProv='"+cCProv+"'", ;

...

   if ADSExecuteSQLDirect( cCount )
      MsgMeter( {|oMeter,oTxt| oMeter:nTotal:= SQLarea->Expr   , ;
                               ADSExecuteSQLDirect( cConsulta ), ;
                               ::aLocalidades:= ::CargaArr( oMeter, oTxt ) }, "", "Cargando Tabla..." )


ADS me responde con con un msg de error de que primero debo cerrar la primer consulta. Como lo hago?

Tambien de este ejemplo observo que en el sample se usa AdsDisconnect(AdsGetConnectionHandle()) donde, AdsGetConnectionHandle() parece no estar en la version 6.2 puede ser?
Es suficiente con AdsDisconnect() ?

muchas gracias

[fgondi]

Hola Mario,

1. Antes ejecutar ADSExecuteSQLDirect tienes que ejecutar ADSCreateSQLStatement para crear el alias donde guardará el resultado de la consulta.

2. Antes dejecutar ADSCreateSQLStatement si no pasas el alias de la aplicación a 0 ( DbSelectar(0) ) reutiliza el alias seleccionado en lugar de crear uno nuevo.

[MarioG]

Fernando; disculpá la ignorancia. Pero soy principiante tanto en esto como en comandos SQL.
Mi codigo es el siguiente

Code: Select all  Expand view

   CLOSE ALL   // Es una aplicación ya implementada por lo quer cierro todo

   AdsConnect( ::oMG:cDirDbf, 2 )
   DbSelectAr( 0 )                               // Lo incluyo aquí

   if !ADSCreateSQLStatement( "SQLarea",2 )  // 2 == ADS_CDX
      MsgAlert( "Fallo en la conexión SQL" )
      return( lOk)
   endif

   if ADSExecuteSQLDirect( cCount )
      MsgMeter( {|oMeter,oTxt| oMeter:nTotal:= SQLarea->Expr   , ;
                               wqout({ ADSExecuteSQLDirect( cConsulta ) }), ;
                               ::aLocalidades:= ::CargaArr( oMeter, oTxt ) }, "", "Cargando Tabla..." )
   else
      AdsDisconnect()
      SQLarea -> ( DbCloseArea() )
      return( lOk )
   end


No entiendo donde incluir DBSelectAr( 0 ); probe incluirlo previo a ADSCreateSQLStatement() y el mensaje sigue siendo el mismo: "The cursor from the previous execute must be closed..."

La otra pregunta es AdsDisconnect(), declarado así, es suficente?
Y por último, donde puedo leer ayuda sobre estas, y otras, funciones.

muchas gracias por tu tiempo

[Carles]

Mario,

Aisla tu codigo con este pequeño ejemplo, y te deberia funcionar. En este ejemplo abro una tabla, muestro el browse, preparo una consulta a otra tabla, muestro resultado, y finalmente retorno al primer browse.

Code: Select all  Expand view

#include "ads.ch"
#include "fivewin.ch"    

*--------------
FUNCTION main()
*--------------
    LOCAL cServer  := "\\BSD001\Test"
    LOCAL consulta := "Select * from tsqlcdx where age > 90"
    LOCAL cTxt     := ''

    RddRegister("ADS",1)
    RddSetDefault("ADS")
    AdsSetServerType ( 2 )
    SET FILETYPE TO CDX

    IF AdsConnect( cServer , 2 ) == .F.
       MsgAlert( 'Error en conexion' )
       RETU .F.
    ENDIF

    USE \\BSD001\Test\customer NEW SHARED

    cAlias := Alias()

    (cAlias)->( Browse() )

    nArea := Select()

    DbSelectAr( 0 )

    IF ! ADSCreateSQLStatement("SQLarea",2)  // 2 == ADS_CDX
       MsgAlert( "ADSCreateSQLStatement( 'SQLArea', 2 )" )
       AdsDisconnect(AdsGetConnectionHandle())
       RETU .F.
    ENDIF

    IF ADSExecuteSQLDirect( consulta )

       FOR nI := 1 TO 20

           cTxt += SQLArea->first + ' ' + SQLArea->last + ' ' + SQLArea->street + ' ' + str( SQLArea->age ) + CRLF

                   SQLArea->( DbSkip() )

       NEXT

       MsgInfo( cTxt )

    ENDIF


    SQLArea -> ( DBCLOSEAREA() )

    (cAlias)->( Browse() )
    (cAlias)->( DbCloseArea() )

    AdsDisconnect(AdsGetConnectionHandle())

RETU NIL


En cuanto a la pregunta del uso del AdsDisconnect() le tienes q pasar de parametro el handle de la conexion, por eso se usa AdsDisconnect(AdsGetConnectionHandle())

Q yo sepa la unica ayuda q puedes tener es ladel help de ADS en la q estan todas las funciones y/o algun anima caricativa en el foro

[MarioG]

Carles:
Gracias por la ayuda.

Respecto de AdsGetConnectionHandle()
Al compilar me devuelve el error de abajo. Por eso aclaré que uso ADS 6.2 y preguntaba si la función no será de una version posterior

Generating C source output to 'WAT_T01.c'...
Done.
Borland C++ 5.5.1 for Win32 Copyright (c) 1993, 2000 Borland
WAT_T01.c:
Turbo Incremental Link 5.00 Copyright (c) 1997, 2000 Borland
Error: Unresolved external '_HB_FUN_ADSGETCONNECTIONHANDLE' referenced from D:\FUEN32\WAT5\RELEASE\WAT_T01.OBJ
mgat - 0 error(es), 2 advertencia(s)

Por otro lado realicé una llamada a AdsDisconnect() y me devuelve verdadero; por lo supongo que desconecta. Es así?

Saludos

[fgondi]

Hola,

El error esta en la segunda consulta SQL. Al intentar generarla te dice que cierres la primer consulta

Code: Select all  Expand view

MsgMeter( {|oMeter,oTxt| oMeter:nTotal:= SQLarea->Expr   , ;
                               wqout({ ADSExecuteSQLDirect( cConsulta ) }), ;
                               ::aLocalidades:= ::CargaArr( oMeter, oTxt ) }, "", "Cargando Tabla..." )

Cada consulta SQL debe tener su propio ADSCreateSQLStatement, sino intenta reutilizar el alias anterior "SQLarea" que esta en uso.
Yo pondría lo siguiente:

Code: Select all  Expand view

MsgMeter( {|oMeter,oTxt| oMeter:nTotal:= SQLarea->Expr   , ;
                               dbSelectar(0),;
                               ADSCreateSQLStatement("SQLarea2", 2),;
                               wqout({ ADSExecuteSQLDirect( cConsulta ) }), ;
                               DbSelectar("SQLarea"),;
                               ::aLocalidades:= ::CargaArr( oMeter, oTxt ) }, "", "Cargando Tabla..." )

El motivo del DbSelectar(0), es para que no haya una tabla activa. Ya que sino perderas los datos de la consulta inicial y las dos tablas "SQLarea" y "SQLarea" tendrán los mismo valores, apuntarán a la misma tabla.

[rolando]

Buen día,

Retomo este hilo porque estoy intentando comenzar con las query en ADS ya que hasta ahora no lo he usado.

Uso DBF's con CDX's.

Dentro de estos post encontré un ejemplo de query que funciona correctamente pero la consulta es porque si la DBF a la cual le hago un query está siendo usada por otra terminal ( con un alias, como una DBF comunmente se usa), el query dá error y lo mismo pasa si uso el architect y ejecuto un query a una dbf abierta. Aclaro que uso ADS Server 9

necesitaría una ayuda en esto para poder avanzar. Gracias.

Rolando