Clarion y SQL (III)

SQL, mySQL, postgreSQL y otros motores
Responder
Avatar de Usuario
Mauricio
Desarrollador de Clarion
Mensajes: 1125
Registrado: Dom Feb 06, 2011 9:34 am
Ubicación: España
Contactar:

Clarion y SQL (III)

Mensaje por Mauricio » Vie Ago 12, 2011 9:09 am

La idea no es hacer un sistema completo pero aún así necesitaremos algunas tablas. Si quieren ver distintos modelos pueden ir a esta página http://www.databaseanswers.org/data_models/index.htm que tiene muchos ejemplos.
Los modelos de esa página difieren, sin embargo, con la forma en la que suelo definir mis tablas (no quiero decir que mi método sea mejor, solo que es diferente). Si se fijan en este modelo http://www.databaseanswers.org/data_mod ... /index.htm observarán que la tabla CUSTOMERS tiene un campo Customer_ID pero no un Customer_Number o Customer_Code. El Customer_ID es el que se usará como Clave externa en las relaciones y yo prefiero que sea intocable, por eso siempre agrego el Customer_Number. Por ejemplo:

CLIENTES
CLI_ID INT (Identity)
CLI_NUMERO INT
CLI_NOMBRE VARCHAR(30)
CLI_APELLIDO VARCHAR(30)

¿Cuál es la ventaja de trabajar así? Si cambio el número de un cliente no tengo que trasladar ese cambio a ninguna de sus tablas relacionadas ya que estas lo están por el CLI_ID que NUNCA cambiará. Imaginen en el primer caso que un Cliente tiene 100.000 Facturas (hay que ponerlo en un pedestal a un cliente así :) ) y que cambiamos su número. Si usamos las tablas del modelo, el cambio se debe propagar a los 100.000 registros de facturas que, aún cuando esto SQL lo haga en segundos, consume un poco de tiempo. En mi modelo esta propagación no se lleva a cabo dado que no es necesaria.

Aprovecho para hacer un comentario: habrán notado que a los campos les puse un prefijo, en este caso CLI_. La razón de esto es que siempre intento que los nombres de los campos sean únicos ya que de esta manera me evito tener que anteponer el nombre de las tablas en las consultas. Por ejemplo, supongamos que queremos saber quienes son los clientes que han tenido más de 10 facturas en el año 2010. Primero me gustaría que piensen cómo resolverían esto con Clarion y TPS. ¿Qué se les ocurre? ¿Recorrer con un LOOP las facturas del 2010 (ordenadas por fecha) e ir contando cuántas facturas tuvo cada cliente? Por supuesto necesitaríamos una queue para ir guardando el número de cliente, su nombre y la cantidad de facturas. Suena un poco complicado, ¿no?.
En SQL eso se resuelve mucho más fácil:

SELECT CLI_NUMERO, CLI_NOMBRE, COUNT(*)
FROM CLIENTES, FACTURAS
WHERE CLI_ID = FAC_CLI_ID AND FAC_FECHA BETWEEN '2010-01-01 00:00:00' AND '2010-12-31 23:59:59'
GROUP BY CLI_NUMERO, CLI_NOMBRE
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC

Con la ayuda de la tabla estúpida (si alguien no sabe cómo usarla me lo dice y lo explico) enviamos esta sentencia al motor y lo único que tenemos que hacer es recorrerla para luego llenar una queue con SOLO los registros que cumplen la condición ya que el motor se encargó de resolver la consulta, contar cuántas facturas tuvo cada cliente y devolvernos esos datos ordenados de mayor a menor. Una sola sentencia, menos tráfico yendo y viniendo del cliente al servidor y problema resuelto.
Y fíjense que en la relación entre las tablas CLIENTES y FACTURAS no usé en ningún momento el prefijo de las facturas o su alias dado que, al ser los campos únicos, no fue necesario.

Por si alguno no lo leyó, es interesante comprender cómo el motor resuelve el SELECT anterior. Pueden verlo aquí: viewtopic.php?f=6&t=81

Seguimos luego.
Mauricio, básicamente usando Clarion 6.3
www.tdcsoftware.com y www.clarioneros.com/blog


Avatar de Usuario
Mauricio
Desarrollador de Clarion
Mensajes: 1125
Registrado: Dom Feb 06, 2011 9:34 am
Ubicación: España
Contactar:

Re: Clarion y SQL (III)

Mensaje por Mauricio » Vie Ago 12, 2011 9:39 am

Pido disculpas porque está quedando algo desordenado pero es que voy escribiendo a medida que las ideas me vienen a la cabeza. El problema es que no vienen muy seguido.
Mauricio, básicamente usando Clarion 6.3
www.tdcsoftware.com y www.clarioneros.com/blog

EstebanTrajtenberg
Ayudante de Santa
Mensajes: 62
Registrado: Mar Feb 15, 2011 10:57 am
Contactar:

Re: Clarion y SQL (III)

Mensaje por EstebanTrajtenberg » Vie Ago 12, 2011 11:10 am

Excelente....!

SDigitales
Novato
Mensajes: 26
Registrado: Mar Feb 08, 2011 2:50 pm
Contactar:

Re: Clarion y SQL (III)

Mensaje por SDigitales » Vie Ago 12, 2011 1:17 pm

Muy buen aporte....
Muchas gracias!!!!

LeoPalomares
Ayudante de Santa
Mensajes: 53
Registrado: Lun Feb 07, 2011 10:50 pm
Contactar:

Re: Clarion y SQL (III)

Mensaje por LeoPalomares » Vie Ago 12, 2011 7:27 pm

Muy buena la serie Mauricio!!!

Un aporte desde el lado de SQL, concretamente para SQL Server que es el que estás usando como base para los ejemplos.

La siguiente consulta puede arrojar datos no esperados por un tema de redondeo de las fechas.

SELECT CLI_NUMERO, CLI_NOMBRE, COUNT(*)
FROM CLIENTES, FACTURAS
WHERE CLI_ID = FAC_CLI_ID AND FAC_FECHA BETWEEN '2010-01-01 00:00:00' AND '2010-12-31 23:59:59'
GROUP BY CLI_NUMERO, CLI_NOMBRE
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC

SQL Server convertirá, internamente, el valor '2010-12-31 23:59:59' como '2011-01-01 00:00:00.000' por lo que si hubiera registros correspondientes a esa hora estos serán retornados por la consulta y no es lo esperado.

Para evitar estos problemas lo más seguro es escribir la consulta como sigue:


SELECT CLI_NUMERO, CLI_NOMBRE, COUNT(*)
FROM CLIENTES, FACTURAS
WHERE CLI_ID = FAC_CLI_ID AND FAC_FECHA >= '20100101 00:00:00.000' AND FAC_FECHA < '20110101 00:00:00.000'
GROUP BY CLI_NUMERO, CLI_NOMBRE
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC

También se puede escribir así:
SELECT CLI_NUMERO, CLI_NOMBRE, COUNT(*)
FROM CLIENTES, FACTURAS
WHERE CLI_ID = FAC_CLI_ID AND FAC_FECHA >= '20100101' AND FAC_FECHA < '20110101'
GROUP BY CLI_NUMERO, CLI_NOMBRE
HAVING COUNT(*) >= 10
ORDER BY COUNT(*) DESC
dado que por defecto SQL Server asumirá las '00:00:00.000' en la parte horaria del DateTime.

NOTA: las fechas las he escrito como '20100101' (sin separadores) y no como '2010-01-01' dado que la primera forma (yyyyymmdd) sin separadores es el formato universal y no depende de la configuración del sistema mientras que si usamos separadores el motor aplicará las reglas de interpretación de fechas según la localización y en algunos casos funcionará correctamente y en otros no.
Saludos!!!

Leo Palomares

Avatar de Usuario
Mauricio
Desarrollador de Clarion
Mensajes: 1125
Registrado: Dom Feb 06, 2011 9:34 am
Ubicación: España
Contactar:

Re: Clarion y SQL (III)

Mensaje por Mauricio » Vie Ago 12, 2011 7:32 pm

Perfecto! Gracias por la correción, Leo.
Mauricio, básicamente usando Clarion 6.3
www.tdcsoftware.com y www.clarioneros.com/blog

LeoPalomares
Ayudante de Santa
Mensajes: 53
Registrado: Lun Feb 07, 2011 10:50 pm
Contactar:

Re: Clarion y SQL (III)

Mensaje por LeoPalomares » Sab Ago 13, 2011 8:18 am

y ya que estamos una recomendación general en cuanto al "rendimiento" de la consulta.

Siempre que el resultado de una select se cargue en una queue evitar el order by en la consulta y realizar el ordenamiento de la queue una vez que esté cargada.

el porque de esta recomendación? simple, el motor nunca prioriza el uso de un índice para resolver la consulta en función del order by sino que lo hace en función de los filtros y de las agrupaciones (tampoco hay una regla sobre cual de estos dos casos pesa más a la hora de determinar que índice usar, depende de factores propios de la consulta y los datos a procesar) por lo que una vez que obtiene el conjunto de datos a retornar los ordena y se los envía a la aplicación pero... para ordenar el conjunto de datos resultantes (result set en inglés, es más corto!!) primero tiene que almacenarlos temporalmente en algún lado y ese lugar será en memoria, si es que hay espacio suficiente y el result set es pequeño, o en la TempDB (esto último implica uso de disco!!!) por lo que los tiempos de respuesta de una misma consulta pueden variar hasta en un orden de magnitud tranquilamente y sin motivo aparente.

Otro efecto colateral es que para poder aplicar el order by se necesita el result set completo lo cual significa que no se podrán retornar datos a la aplicación hasta que no se haya completado el ordenamiento y no se podrá realizar este hasta que no se hayan guardado en el almacenamiento temporal todos los registros del result set. En cambio, si no estuviese el order by presente en la consulta el motor puede enviar registros a la aplicación ni bien los vaya obteniendo del último paso paso de procesamiento (en lugar de almacenarlos temporalmente).
Saludos!!!

Leo Palomares


Responder

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 6 invitados