PiensoPienso: Como resolver esta consulta sencilla?
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.
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.
ResponderBorrarselect *
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')
Marcos:
ResponderBorrarBuenas 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.
Si por eficiente se entiende que ser resuelva en el servidor, se me ocurre usar fórmulas en GX Ev1.
ResponderBorrarYo 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.
Hay que ver cuál es la definición de "eficiente".
ResponderBorrarDe 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).
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.
ResponderBorrarLas respuestas deben tener en cuenta que si se uso un OAV para definir esos campos, entonces las consultas no pueden ser explicitas.
Ja ja, ¿no decías que se podía dar la respuesta en SQL?
ResponderBorrarEn 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?
Marcos:
ResponderBorrarEl problema dice:
".. directamente con SQL o su lenguaje preferido, para los lectores que no usan GeneXus. "
Enrique
Anonimo:
ResponderBorrarEn 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.
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 )
ResponderBorrarDataselector es algo que podría en una de esas servir.
ResponderBorrarPero 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 :).
Anonimo:
ResponderBorrarEntendido. 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.
Si te vas a fijar en la letra chica...
ResponderBorrarAnó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.
jaja, ok, me lo imaginaba (en el fondo creo que termina haciendo un where al viejo estilo).
ResponderBorrarComo 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.
Marcos, la letra menciona...
ResponderBorrar"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.
David:
ResponderBorrarEn 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.
Se puede utilizar lucene(Full Text Search),
ResponderBorrarEso Saludo
Leonardo Zepeda A.
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 ;)
ResponderBorrarLa 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.
ResponderBorrarCon 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."
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.
ResponderBorrarel output de esto puede ser un sdt con customer.. o simplemente true-false de un customer en particular.
Hola Enrique, vengo medio atrasado con los blogs :) capaz que es tarde, pero una solución creo que podría ser la siguiente:
ResponderBorrarDefinir 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
Hola, se que ya es demasiado tarde para poner una opinión sobre la solución pero apenas leí el post.
ResponderBorrarMe 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
Luis:
ResponderBorrarTu 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...
Gracias.
ResponderBorrarQue 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