PiensoPienso: Como resolver esta consulta sencilla?

Este es un pequeño problema, del cual no tengo una solución que me conveza.

Un sistema GeneXus, tiene la transacción CUSTOMER, a la cual se le aplica el Patrón OAV (Objeto/Atributo/Valor)

Para aquellos que no estén familiarizados con dicho patrón, es uno que nos permite agregar atributos dinámicos a una tabla, de forma que el usuario puede elegir en runtime mantener determinado atributo para todos sus clientes.

Por ejemplo, me puede interesar guardar en forma dinámica, el país y la ciudad de todos los clientes (suponiendo que la ciudad y el pais no estan definidos como campos en la tabla de clientes).

Las tablas van a quedar definidas de la forma (robo el ejemplo del wiki para que quede sencillo):


Customer // Original transaction
CustomerId*
CustomerName
CustomerPhone


Transacciones para guardar los atributos dinamicos y sus valores.

CustomerAttributeValue  // This is the OAV table
CustomerId*
CustomerAttributeId*
CustomerAttributeValue


CustomerAttribute // Defines the set of valid attributes for Customer
CustomerAttributeId*
CustomerAttributeName
CustomerAttributeType
CustomerAttributeCategory // used to order the attributes when many attributes are expected


Supongamos que para hacer mas interesante el problema, tenemos varios millones de customers y se almacenan unos 10 atributos variables por cada customer. 


El problema planteado es:


Como se puede resolver en forma eficiente, la consulta que trae todos los clientes de un pais y de una ciudad?


Por ejemplo, devolver todos los clientes que tienen
CustomerAttributeId='PAIS' AND CustomerAttributeValue='uy' 
y
CustomerAttributeId='CIUDAD' AND CustomerAttributeValue='MVD'


Las soluciones pueden ser hechas con GeneXus (9.0, X, Xev1) o directamente con SQL (o su lenguaje preferido) para los lectores que no usan GeneXus. 





Comentarios

  1. Yo probaría una de las siguientes tres consultas SQL (T-SQL para ser precisos...), ordenadas de menor a mayor en el tiempo (que creo) que van a demorar.

    select *
    from Customer
    where CustomerId in (select CustomerId from CustomerAttributeValue where CustomerAttributeId = 'PAIS' and CustomerAttributeValue = 'UY')
    and CustimerId in (select CustomerId from CustomerAttributeValue where CustomerAttributeId = 'CIUDAD' and CustomerAttributeValue = 'MVD')

    select Customer.*
    from Customer
    left join CustomerAttributeValue P on P.CustomerId = Customer.CustomerId and P.CustomerAttributeId = 'PAIS'
    left join CustomerAttributeValue C on C.CustomerId = Customer.CustomerId and C.CustomerAttributeId = 'CIUDAD'
    where P.CustomerAttributeValue = 'UY' and C.CustomerAttributeValue = 'MVD'

    select *
    from Customer
    where exists (select * from CustomerAttributeValue P where P.CustomerId = Customer.CustomerId and CustomerAttributeId = 'PAIS' and CustomerAttributeValue = 'UY')
    and exists (select * from CustomerAttributeValue C where C.CustomerId = Customer.CustomerId and CustomerAttributeId = 'CIUDAD' and CustomerAttributeValue = 'MVD')

    ResponderEliminar
  2. Marcos:
    Buenas soluciones!, pero esperaba que vos me dieras una en GeneXus! :)

    En SQL yo usaría otra consulta que creo que puede ser mejor que las que planteas, aunque depende de los datos.

    ResponderEliminar
  3. Si por eficiente se entiende que ser resuelva en el servidor, se me ocurre usar fórmulas en GX Ev1.
    Yo no es usado casi nada, pero como en el encuentro me vendieron que eran all-mighty, pienso que se puede resolver así:

    For Each
    Where CustomerAttributeId='PAIS' And CustomerAttributeValue='uy'
    Where find(CustomerAttributeValue, CustomerId=CustomerId And CustomerAttributeId='CIUDAD')='MVD'
    ...
    EndFor

    No se si Genexus lo resolverá como se quiere, especialmente porque puede inferir condiciones que no queremos o no entender la condición CustomerId=CustomerId (capaz que se puede o debe omitir).

    Otra consulta que puede tener más chances es:

    For Each
    Where find(CustomerAttributeValue, CustomerAttributeId='PAIS')='uy'
    Where find(CustomerAttributeValue, CustomerAttributeId='CIUDAD')='MVD'
    defined by CustomerName
    ...
    EndFor

    Si alguna de estas te funciona me gustaría ver el SQL que genera :)

    Saludos.

    ResponderEliminar
  4. Hay que ver cuál es la definición de "eficiente".

    De la forma que lo haría sería mediante un corte de control sobre CustomerAttributeValue (La tabla OAV)

    El primer nivel del corte sería sobre CustomerId y el segundo sería sobre los atributos valores manejando las dos expresiones con un OR.

    Si hago un "count" cortando por cada cliente de cuántas de estas dos expresiones cumple, si la cantidad del conteo me da 2, significa que cumple con las dos, entonces es el cliente que quiero encontrar.

    Me imagino que Genexus lo resuelve en un solo golpe, como el resultado está ordenado por client id, puedo contar cuantos atributos/valor obtengo por cada uno de estos, con lo que cuando termino de procesar cada clientid, tengo cuantas expresiones cumplió (hay que asegurarse de manejar todas las expresiones con OR).

    Esto funcionaría en.. todos los GeneXus que conozco.

    La unica diferencia es que en el peor de los casos, ontengo clientes que solo cumplen con uno de los lados de la expresión.
    Pero si lo hago a mano, como manejo un solo golpe contra la base de datos, los tiempos pueden ser mucho mejores a si lo hago con "de un golpe obtengo un lado" y con ese "voy y me fijo si cumple el otro".

    Ahora bien, para que sea realmente eficiente, se debería de poder implementar el corte de control de forma eficiente en el DBMS de alguna forma declarada (en SQL me lo imagino como count de los valores obtenidos por un inner jpin)

    En GX me imagino algo como (propuesta de implementación?)

    For Each ClientID
    Where Count(*)=2

    For Each CustomerAttributeID CustomerAttributeValue
    Where (CustomerAttributeId='PAIS' AND CustomerAttributeValue='uy') OR
    (CustomerAttributeId='CIUDAD' AND CustomerAttributeValue='MVD'
    )

    EndFor
    Msg(ClientId)
    Endfor

    No se bien como expresarlo, pero la salida debería de ser algo como

    SELECT CustomerID, Count(CustomerAttributeValue)=2 From Custtomer as Cust inner join CustomerAttributValue CATV on Cust.CustomerId = CATV.CustomerID where (CustomerAttributeId='PAIS' AND CustomerAttributeValue='uy') OR
    (CustomerAttributeId='CIUDAD' AND CustomerAttributeValue='MVD'
    ) group by CustomerId

    seguramente tanto la sentencia SQL como la propuesta tengan errores (todo programado en el aire, disculpas mismo si no le pegué a algún nombre de algo).

    ResponderEliminar
  5. Todas sus respuestas se basan en que de ante mano se conoce la existencia del atributo "CIUDAD" y "PAIS" en cuyo caso pierde sentido el OAV.

    Las respuestas deben tener en cuenta que si se uso un OAV para definir esos campos, entonces las consultas no pueden ser explicitas.

    ResponderEliminar
  6. Ja ja, ¿no decías que se podía dar la respuesta en SQL?

    En GeneXus, lo que podrías hacer para que se comporte más o menos como la primera, es hacer los dos selects del "in" y cargarlos en SDTs, y después hacer un "in" en el for each. No es lo mismo, pero pienso que debería ser la mejor opción...

    for each
    where CustomerAttributeId = 'PAIS'
    where CustomerAttributeValue = 'UY'
    &sdtClientes1.add(CustomerId)
    endfor

    for each
    where CustomerAttributeId = 'CIUDAD'
    where CustomerAttributeValue = 'MVD'
    &sdtClientes2.add(CustomerId)
    endfor

    &sdtClientes = &sdtClientes1 "intersección" &sdtClientes2

    for each
    where CustomerId in &sdtClientes
    ...
    endfor

    ¿Cual sería la consulta que decís que puede ser mejor?

    ResponderEliminar
  7. Marcos:
    El problema dice:

    ".. directamente con SQL o su lenguaje preferido, para los lectores que no usan GeneXus. "

    Enrique

    ResponderEliminar
  8. Anonimo:
    En el problema, ya se conocen de antemano el valor de los atributos ciudad y pais.

    Una vez resuelto para estos, no es dificil generalizar para cualquier combinacion de atributos y valores.

    Una cosa interesante es pensar la solucion para un conjunto de cantidad de atributos variables, por ejemplo buscar por (PAIS=UY,CIUDAD=MVD,BARRIO=PRADO), pero es otro problema mas dificil.

    ResponderEliminar
  9. Enrique, me referia justamente al problema de no saber cuantos son los atributos ( obviamente el nombre del atributo puede ir en una variable, pero no puedes hacer variable facilmente la cantidad de 'Wheres' en forma eficiente )

    ResponderEliminar
  10. Dataselector es algo que podría en una de esas servir.
    Pero me queda la duda de cómo resolvería GX la siguiente sentencia

    For Each
    Where CustommerId IN DSGetValues("PAIS","UY")
    Where CustommerId IN DSGetValues ("CIUDAD","MVD")
    Msg(CustommerId)
    EndFor

    Como no tengo experiencia con DataSelector, mismo hasta esté planteando una burrada.
    Si es que realmente lo resuelve todo en una única sentencia, excelente :).

    ResponderEliminar
  11. Anonimo:
    Entendido. Es un problema mas interesante y tambien mas dificil.
    El predeterminar la cantidad de atributos fue para hacer el problema mas tratable.

    David:
    No lo probé, pero tal como estamos hoy, tu consulta no devuelve ningun registro, pues se traduce a un select que tiene Att=Ciudad and att=pais.... lo cual no va a traer ningun registro.

    ResponderEliminar
  12. Si te vas a fijar en la letra chica...

    Anónimo decía "me referia justamente al problema de no saber cuantos son los atributos".

    Con la solución que planteaba en el segundo comentario, en realidad estaría resuelto :) Puedo hacer un for each por cada uno de los atributos en un loop, e ir haciendo las intersecciones con cada uno.

    ResponderEliminar
  13. jaja, ok, me lo imaginaba (en el fondo creo que termina haciendo un where al viejo estilo).

    Como que nos falta un poder más de expresividad.
    Si pudiera hacer "sub Foreach's" y tener operadores sobre el resultado de los mismos en una de esas podría resolverse de forma eficiente.

    Hoy no sé como manejar GroupBy, Count y Sum o tener algo como un Average o similar desde el propio For Each.

    Estoy medio desactualizado.. ¿ya se pueden usar por fuera de las formulas cosas como el Find y el Count? En una de esas por ese lado y el poder de las formulas se puede hacer algunos GroupBy con Count que permita hacerlo todo en una sola sentencia.

    ResponderEliminar
  14. Marcos, la letra menciona...

    "Como se puede resolver en forma eficiente, la consulta que trae todos los clientes de un pais y de una ciudad?"

    Si implemento un loop y golpeo por cada cliente sobre país y otro por ciudad... no me suena muy eficiente . :P

    Yo me quedo con mi solución inicial que propuse de usar corte de control (compatible con todas las versiones de GX).
    En una de esas con el poder de expresión de la X/X Evo 1 alguien puede hacerme "el corte" con una sola sentencia que termine haciendo internamente el conteo y la agrupación.

    ResponderEliminar
  15. David:

    En realidad eso va a depender de la distribución de los datos...

    Fijate que en realidad no hace un for each a los clientes, sino que va a los atributos de los clientes. Esas consultas pueden ser eficientes si se tienen los índices adecuados.

    La consulta con el "in" puede ser mejor cuando se tienen pocos clientes que tengan los valores buscados. Es más eficiente todavía si los atributos son opcionales, y la cantidad de tuplas es menor a la cantidad de clientes.

    Por otro lado, el "join" va a ser mejor si muchos de los clientes tienen los atributos/valores buscados.

    En todo caso, lo mejor sería probar con distintas variantes con los datos más representativos posibles, y quedarse con la que de mejor.

    ResponderEliminar
  16. Se puede utilizar lucene(Full Text Search),

    Eso Saludo

    Leonardo Zepeda A.

    ResponderEliminar
  17. por eso me encanta leer este blog je je siempre temas reinteresantes de los que se puede aprender de gusto....lastima que ya no actualices tanto el blog de petroglifo ;)

    ResponderEliminar
  18. La solucion que mas me gusta hasta el momento con GeneXus es igual a la que planteo Marcos: Cargar un SDT con las selecciones y luego recorrerlas y quedarse con las comunes.

    Con SQL creo que se puede resolver con la sentencia:

    (select CustomerId from CustomerAtributeValue where CustomerAtributeId='CIUDAD' AND CustomerAtributeValue='MVD')
    INTERSECT
    (select CustomerId from
    CustomerAtributeValue where
    CustomerAtributeId='PAIS' AND CustomerAtributeValue='UY')

    Esto va a resolver la interseccion (los valores comunes) en el DBMS.

    Si las tablas son muy grandes va a demorar bastante, sobre todo si la cantidad de tuplas que cumplen con las condiciones son grandes.

    Me dejo pensando la solucion de Leonardo Zepeda usar un FullTextSearch para resolverlo, pues podria mejorar mucho la performance. Voy a probar si con GeneXus puedo lograr que la consulta solo muestre Customers.

    Gracias a todos por los comentarios.

    Publicar este tipo de problemas en el blog, me hace acordar a una frase de George Bernard Shaw

    "Si tú tienes una manzana y yo tengo una manzana e intercambiamos las manzanas, entonces tanto tú como yo seguiremos teniendo una manzana. Pero si tú tienes una idea y yo tengo una idea e intercambiamos ideas, entonces ambos tendremos dos ideas."

    ResponderEliminar
  19. Yo opino por la confección de un proceso recursivo que por customer realice la búsqueda de si se cumple cada una de las condiciones.. devuelve un true por todas o cuando una no se cumple, pasa al siguiente customer.
    el output de esto puede ser un sdt con customer.. o simplemente true-false de un customer en particular.

    ResponderEliminar
  20. Hola Enrique, vengo medio atrasado con los blogs :) capaz que es tarde, pero una solución creo que podría ser la siguiente:

    Definir un par de Data Selectors:

    - DS "CustomersFromCountry" que tiene:
    Parm : &Country
    Conditions: CustomerAttributeId = 'PAIS' and CustomerAttributeValue = &Country

    - DS "CustomerFromCity" que tiene:
    Parm : &City
    Conditions: CustomerAttributeId = 'CIUDAD' and CustomerAttributeValue = &City


    Luego el For Each quedaría:

    For each
    Where CustomerId in CustomersFromCountry('UY')
    Where CustomerId in CustomersFromCity('MVD')
    ...
    Endfor

    Notar que se podría resumir en un único DataSelector con los dos parámetros (CustomerAttributeId y CustomerAttributeValue), pero dejo dos para que quede más claro el ejemplo.

    Te dejo para analizar el tema performance con tu el caso real. :)

    un abrazo
    Pablo Mazzilli

    ResponderEliminar
  21. Hola, se que ya es demasiado tarde para poner una opinión sobre la solución pero apenas leí el post.
    Me funcionó de la siguiente forma:

    For each Order CustomerId
    defined by CustomerAttributeId
    For each
    defined by CustomerAttributeValue
    if CustomerAttributeId = 'pais' and CustomerAttributeValue = 'Mexico'
    &CustomerId = CustomerId
    do 'verificar'
    endif
    Endfor
    Endfor

    sub 'verificar'
    For Each
    Where CustomerId = &CustomerId
    Defined by CustomerAttributeValue
    if CustomerAttributeId = 'ciudad' and CustomerAttributeValue = 'Cuernavaca'
    print pbCustomer
    endif
    Endfor
    endsub

    Se que no es una solución muy elegante por el poor performance que se puede notar, pero podria mejorar usando los adecuados indices para que el segundo for each filtre mejor.
    Si esto es incorrecto por favor comentar.
    Saludos
    atte
    Luis Europa

    ResponderEliminar
  22. Luis:
    Tu solucion no es incorrecta, pero puede tener problemas graves de performance como vos bien lo decis.
    El problema de tu enfoque si hay muchos CustomersId en Mexico y pocos en Cuernavaca, puede demorar muchisimo...

    ResponderEliminar
  23. Gracias.
    Que tal con el sub de esta forma?
    sub 'verificar'
    &CustomerAttributeId = 'ciudad'
    &CustomerAttributeValue = 'CUERNAVACA'
    For Each Order CustomerId,CustomerAttributeId,CustomerAttributeValue
    Where CustomerId = &CustomerId
    Where CustomerAttributeId = &CustomerAttributeId
    Where CustomerAttributeValue = &CustomerAttributeValue
    Defined by CustomerAttributeValue
    Print pbCustomer
    Endfor
    endsub
    Es probable que mejore el performance?

    Me quedó una duda al respecto.
    Quise implementar esto:

    For each Order CustomerId
    Defined by CustomerAttributeId
    &CustomerId = CustomerId
    For each
    Where CustomerAttributeId = 'pais'
    Where CustomerAttributeValue = 'MEXICO'
    Defined by CustomerAttributeValue
    For Each
    Where CustomerAttributeId = 'ciudad'
    Where CustomerAttributeValue = 'CUERNAVACA'
    Defined by CustomerAttributeValue
    Print pbCustomer
    exit
    Endfor
    exit
    EndFor
    Endfor

    En el reporte de navegación de gx ev1 aparece Loop while: CustomerId = @CustomerId para el segundo y tercer for each, pero me doy cuenta de que no solo esta filtrando por identidad de customerid, sino también CustomerAttributeId ya que no vuelve a buscar en la tabla filtrando solamente por custumerid sino tambien donde CustomerAttributeId sea igual a 'pais'. Creo que por eso el reporte salia vacio jeje.
    atte.
    Luis Europa

    ResponderEliminar

Publicar un comentario

1) Lee el post
2) Poné tu opinión sobre el mismo.
Todos los comentarios serán leidos y la mayoría son publicados.

Entradas más populares de este blog

El Sordo

Paleta de colores en GeneXus