Mejorar consulta en GENERO e INFORMIX

[Piro San]

Hola a todos, como estan?
Soy de México y llegue a estos foros como producto de google xD

Bueno, al grano ahora si, tengo el siguiente "issue":

Pasa que tengo que elaborar un reporte en texto plano con resultados de SELECT COUNT, son en total 12 sentencias de este tipo, donde varía el número de tablas que utilizo, van desde 1 o 2 hasta 6, pero en algunas tablas hay muchisimos registros (cerca de mas de 10,000 por tabla).

He intentado abriendo mi archivo destino solo una vez para escribir los resultados, así como cambiar mis consultas con INNER JOIN, ya que antes las tenia con el fomato normal de sentencia (SELECT COUNT(campo) from tabla1, tabla2 ... WHERE tabla1.col1=tabla2.col1 AND ...) pero aun así tarda mucho (1305 registros en mas de 1 hora O_O')

Como ya lo mencione en el título, el lenguaje de programación que utilizo es GENERO y el DBMS que utilizo es INFORMIX.

Agradeceria mucho me pudieran orientar debido a que esto me ha traido dos dias intentando cosas para hacerlo mas rápido pero nomas no se agiliza U_U

P.D. Si, las tablas estan indexadas xD

[^Tifa^]

Yo nunca he utilizado INFORMIX pero, despues de ver que internamente varios motores tienen un funcionamiento similar.

Puedes intentar realizar el COUNT a los indices (campos indices) de cada tabla dentro del JOIN????

Por ejemplo:

Código (sql) [Seleccionar] Expandir


mysql> select count(*) from ejemplo;
+----------+                       
| count(*) |                       
+----------+                       
|    15000 |                       
+----------+                       
1 row in set (0.00 sec)             

mysql> select count(*) from copia;
+----------+                     
| count(*) |                     
+----------+                     
|    20000 |                     
+----------+                     
1 row in set (0.00 sec)           

mysql> select count(numero), count(numerito) from ejemplo inner join copia on ejemplo.numero = copia.numerito;
+---------------+-----------------+
| count(numero) | count(numerito) |
+---------------+-----------------+
|         15000 |           15000 |
+---------------+-----------------+
1 row in set (0.34 sec)



Nada mal 0.34 segundos    ahora checa sino hago el COUNT a los indices:

Código (sql) [Seleccionar] Expandir


mysql> select count(nombres), count(apellidos) from ejemplo inner join copia on copia.codigo = ejemplo.codigo ; +----------------+------------------+                                                                                                             
| count(nombres) | count(apellidos) |                                                                                                             
+----------------+------------------+                                                                                                             
|          15000 |            15000 |                                                                                                             
+----------------+------------------+                                                                                                             
1 row in set (4 min 16.36 sec)                                                                                                                     



4 minutos y 17 segundos 

Intentalo el desempeno debe ser similar.

[Piro San]

Hola, gracias por responder.

Por campos indices te refieres a los campos indexados o que escriba el nombre de los indices dentro del COUNT?? 

Por que si es a los campos indexados ... esos son los que tomo para realizarlo 

Mira, esta es la consulta:

SELECT COUNT(a.cve_atencion)
    FROM llegada a INNER JOIN cuaderno_anotaciones b ON a.cve_atencion=b.cve_nota_atencion
    WHERE a.cve_unidad=1
    and b.cve_seccion=3
    AND MONTH(a.fecha_recep)=5
    AND YEAR(a.fecha_recep)=2009

Y el campo que contiene el indice es cve_atencion.

[^Tifa^]

disculpa he recreado un escenario como el tuyo;

Código (sql) [Seleccionar] Expandir


mysql> describe llegada;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cve_atencion | int(11)     | NO   | PRI | 0       |       |
| nombres      | varchar(20) | YES  |     | NULL    |       |
| fecha_recep  | date        | YES  |     | NULL    |       |
| cve_unidad   | tinyint(4)  | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> describe cuaderno_anotaciones;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| apellidos         | varchar(20) | YES  |     | NULL    |       |
| cve_nota_atencion | int(11)     | YES  | MUL | NULL    |       |
| cve_seccion       | int(11)     | YES  | MUL | NULL    |       |
+-------------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)



Y ambas tienen miles de datos;

Código (sql) [Seleccionar] Expandir


mysql> select count(*) from llegada;
+----------+
| count(*) |
+----------+
|    40000 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from cuaderno_anotaciones;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.00 sec)



Una 40 mil otra 30 mil, he utilizado tu consulta y::::

Código (sql) [Seleccionar] Expandir


mysql> select count(cve_atencion) from llegada a inner join cuaderno_anotaciones b on a.cve_unidad = 1 and a.cve_atencion = b.cve_nota_atencion and b.cve_seccion = 3 and month(a.fecha_recep)=10 and year(a.fecha_recep)=2009;
+---------------------+
| count(cve_atencion) |
+---------------------+
|               18000 |
+---------------------+
1 row in set (0.36 sec)



36 Segundos 18 mil registros concordaron (Y esto sin tener activado el buffer cache del motor que reduce luego de la primera consulta 260% del tiempo inicial  ) Y de acuerdo al optimizador interno de MySQL los indices estan correctos uno es constante y el otro se iguala con otro indice de la otra tabla.

No te molestaria indicarme cuantos valores te retornan a ti cuando haces esa consulta, y parte de los registros de tu tabla (aunque no sean reales) hazme un escenario de ejemplo aca de como son tus tablas y que valores tienen.

[Piro San]

Ok, mira, las tablas son de la siguiente estructura:

Código [Seleccionar] Expandir


Table
LLEGADA

Column name          Type                                    Nulls

cve_llegada         serial                                  no
cve_paciente        integer                                 yes
cve_unidad          integer                                 yes
cve_cama            integer                                 yes
cve_examenes        integer                                 yes
cve_tpo_llegada     integer                                 yes
cve_doc             integer                                 yes
edad                integer                                 yes
fecha_recep         date                                    yes
hora_recep          datetime hour to second                 yes
motivo_llegada      char(2000)                              yes
ri                  char(2000)                              yes
folio               varchar(255)                            yes
medico_legal        smallint                                yes
conciente           smallint                                yes

Table
CUADERNO_ANOTACIONES

Column name          Type                                    Nulls

cve_nota_atencion    integer                                 no
cve_ingreso_llegada  integer                                 no
cve_seccion          integer                                 no
cve_egreso           integer                                 no
fecha_salida         date                                    yes
hora_salida          datetime hour to second                 yes


Ademas de 16 campos de tipo INT y CHAR, los cuales no importan tanto.
Hay muchos datos (si no es que todos... )  que no me sirven ya que solo me interesa el campo cve_nota_atencion de cuaderno_anotaciones ...

Algunos registros de LLEGADA son:

Código [Seleccionar] Expandir

   
---- ---  ---  ----  ----  ---  ----  ----  ----------  ------------  -------  -------   -------     ------------     ---------     -----------
30   29   1    36    44    3    5     55    18/11/2002  06:50:45 PM   (null)   (null)    (null)      (null)           (null)        (null)     
31   30   1    16    45    2    10    3     18/11/2002  07:54:45 PM   (null)   (null)    (null)      (null)           (null)        (null)     
32   31   1    17    46    2    10    1     18/11/2002  08:03:49 PM   (null)   (null)    (null)      (null)           (null)        (null)         


Y en total en esta tabla hay 30,000 registros.

En CUADERNO_ANOTACIONES tengo registros como estos:

Código [Seleccionar] Expandir


----  ----  ----  ---- ------------  ------------  -------  -------  -------  -------  -------  -------  -------  -------  --------  ---  --------  --  ---  ------------------------  -------
21    22    3     2    31/12/2002    07:13:50 PM   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)    0    (null)    0    4    1-FULANO PEREZ           (null)
31    37    3     2    12/12/2002    11:40:46 AM   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)    0    (null)    0    4    3-SULTANO MELEZ          (null)
40    51    3     2    31/12/2002    07:13:50 PM   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)    0    (null)    0    4    68-PERENGANO MARQUEZ     (null)
49    61    3     2    31/12/2002    07:13:50 PM   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)   (null)    0    (null)    0    3    96-ESQUIVEL DE POR ALLA  (null)
         

Y aquí tengo 44,000 registros...

Ahora ... si yo busco:

Código [Seleccionar] Expandir


SELECT COUNT(a.cve_atencion)
    FROM llegada a INNER JOIN cuaderno_anotaciones b ON a.cve_atencion=b.cve_nota_atencion
    WHERE a.cve_unidad=1
    and b.cve_seccion=3
    AND MONTH(a.fecha_recep)=5
    AND YEAR(a.fecha_recep)=2009


Tengo este resultado:

Código [Seleccionar] Expandir


(count)   
----------
4         

1 record(s) selected [Fetch MetaData: 16/ms] [Fetch Data: 0/ms]

[Executed: 12/02/10 02:48:34 PM CST ] [Execution: 3062/ms]


Ahora, se que no es mucho comparado con los tiempos que tu me has mostrado ... pero el problema viene aqui: tengo que hacer 12 consultas de este tipo (SELECT COUNT) para 1300 unidades, y algunas de esas consultas se realizan con 5 o 6 tablas ....

Mas que nada quiero saber si hay alguna remota posibilidad de hacer esto lo mas rapido posible  ...

[Piro San]

Sorry; en CUADERNO_ANOTACIONES, faltaron de verse estos campos despues de los ultimos ....  ¬_¬ :

Código [Seleccionar] Expandir


--  -------------------  -------
3   42-FULANO FEO        (null)  
4   54-sULTANO MAS FEO   (null)  
2   42-PERENGANO PEREZ   (null)  


[^Tifa^]

Tienes 1 campo (indice) comun en todas las tablas que relaciones (sean 5 o 6) hay 1 campo comun en todas????

No me preocupa mucho que cada tabla tenga 30 campos por ejemplo, lo que se analizaria aqui serian 2 o 3 campos por cada tabla.

Las otras 5 o 6 tablas tienen o no un campo en comun con las que se relaciona?

[Piro San]

Jajaja, no, es parte del problema ... la consulta por tantas tablas es por el hecho de que necesito sacar la clave de la unidad ...

Es un poco complejo de explicar ... necesitaria poner todas las tablas ... y estaria complicado de describir ... dejo mi msn por si alguien quiere ayudarme    piro_san_@hotmail.com

[^Tifa^]

Piro chico .... si te pregunte sobre las demas tablas es porque creo que otros preguntarian lo mismo para poder ayudarte 

Entiendo que a lo mejor no quieras postear tus demas tablas, pero como bien dices el COUNT en 2 tablas no provoca tantos inconvenientes ni lentitud de respuesta... el problema es cuando uso mas tablas (5 o 6) no me asustan de paso los examenes de MySQL te hacen JOINS de 4 y 5 y 7 tablas juntas 

Te preguntaba si todas las tablas tenian un indice en comun para usar ese indice en comun a la hora de hacer la consulta. Pero, te voy a ayudar..    no hay problema, accepta mi peticion.

[^Tifa^]

Vamos caminando 

No he podido recrear completamente todas tus tablas (me falta info  ) pero... recree 4 las primeras 4 que vi en tu mega consulta    (constate que me estoy basando en registros imaginarios). Estas son las 4 tablas basadas en la tuya:

Código (sql) [Seleccionar] Expandir


mysql> describe Agenda;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| hora        | time    | YES  | MUL | NULL    |       |
| agenda      | int(11) | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.04 sec)                               

mysql> describe Postoper
+-----------------+---------+------+-----+---------+-------+
| Field           | Type    | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| postop          | int(11) | YES  | UNI | NULL    |       |
| preop           | int(11) | YES  | MUL | NULL    |       |
+-----------------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> describe Preoper;
+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| nota_preop     | int(11) | YES  | UNI | NULL    |       |
| hora           | time    | YES  |     | NULL    |       |
+----------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe Quirofano;
+-------------+------+------+-----+---------+-------+
| Field       | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| hora        | time | YES  | MUL | NULL    |       |
| fecha       | date | YES  |     | NULL    |       |
+-------------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)



Y llene cada una con miles de registros:

Código (sql) [Seleccionar] Expandir


mysql> select count(*) from Agenda;
+----------+
| count(*) |
+----------+
|    22998 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from Postoper;
+----------+
| count(*) |
+----------+
|    56000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from Quirofanos;
+----------+
| count(*) |
+----------+
|    35000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from Preoper;
+----------+
| count(*) |
+----------+
|    35800 |
+----------+
1 row in set (0.00 sec)




Bien basandome un poco en el ejemplo que hablamos de tu consulta actual SQL tendria algo como esto:

Código (sql) [Seleccionar] Expandir


select count(a.postop) from Postoper a inner join Preoper b on a.preop = b.preop inner join agenda c on c.hora = b.hora inner join quirofano f on f.hora = b.hora;
+--------------------------+
| count(a.cve_nota_postop) |
+--------------------------+
|                        12000 |
+--------------------------+
1 row in set (45 min 52 sec)



Si tuve que esperarme todo eso (42 minutos casi 1 hora) para obtener 12 mil registros que concordaban... sin embargo ejecute el fabuloso EXPLAIN y que vi 

+----+-------------+-------+------+----------------+-------------+---------+---------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys  | key         | key_len | ref                       | rows  | Extra                    |
+----+-------------+-------+------+----------------+-------------+---------+---------------------------+-------+--------------------------+
|  1 | SIMPLE      | b     | ALL  | preop | NULL        | NULL    | NULL                      | 35800 |                          |
|  1 | SIMPLE      | a     | ref  | indice         | indice      | 5       | primaria.b.preop |     1 | Using where              |
|  1 | SIMPLE      | f     | ref  | hora    | hora | 4       | primaria.b.hora    |    12 | Using where; Using index |
|  1 | SIMPLE      | c     | ref  | hora    | hora | 4       | primaria.f.hora    |    12 | Using where; Using index |
+----+-------------+-------+------+----------------+-------------+---------+---------------------------+-------+--------------------------+
4 rows in set (0.00 sec)

La tabla Preoper.... se le escapa filtrar un indice    y analiza todos los registros... Asi que altere dicha tabla y le agregue una clave candidata :

Código (sql) [Seleccionar] Expandir


mysql> describe preoper;
+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| preop          | int(11) | YES  | UNI | NULL    |       |
| hora           | time    | YES  |     | NULL    |       |
| id             | int(11) | YES  | MUL | NULL    |       |
+----------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)


el nuevo campo ID es un indice y tiene un dato fijo = 1 Ahora reconstruyo la consulta:

Código (sql) [Seleccionar] Expandir


mysql> select count(a.postop) from postoper a inner join preoper b on a.preop = b.preop inner join agenda c on c.hora = b.hora inner join quirofano f on f.hora = b.hora and b.preop = (select count(*) from preoper where id = 1);
+--------------------------+
| count(a.cve_nota_postop) |
+--------------------------+
|                        12000 |
+--------------------------+
1 row in set (0.12 sec)



De 45 minutos a 12 segundos...    vaya lo que hace una clave candidata en estas cosas es increible    si activo el buffer cache la segunda vez en vez de 12 segundos deberian ser 0.01 segundos al menos .....

Modifique los nombres de los campos para proteger tu informacion como querias    detallame mas por msn, y seguro que podremos dar con cuales tablas se cuelan los indices para agregar mas claves candidatas.