FTP | CCD | Buscar | Trucos | Trabajo | Foros |
#1
|
|||
|
|||
SQL inventario
Hola amigos, alguna idea para mejorara esto...
Tengo una consulta SQL, que demora aproximadamente 15 segundo en devolver resultados. Esto empezo a ocurrir mientras mas registro tengo en mi base de datos. Este es el codigo que utilizo.
Es muy larga... alguna idea para hacerla mas eficiente. Lo que hace es: Busca los codigos en la tabla insumos (aproximadamente 1000 registros), utiliza estos codigo en la tabla inventario dado que la suma de todos los ingresos, menos la suma de todos los egresos. (calcula la cantidad de existe de cada uno de los insumos). Además para cada uno de los insumos calcula el precio pondera, segun la sumatoria de los ingresos por su precio, menos los egresos por su precio. UFFFF.... bueno, el tema es que esta funcionando, pero es muy relento.... unos 15 segundo en entregar la info. Que me dicen ustedes, valdra la pena mejorar esto? Como lo puedo hacer? Alguna idea? Gracias anticipadas. |
#2
|
|||
|
|||
Ya intentaste utilizar indices para las tablas, eso hace las busquedas mas rapidas, ademas por que no haces eso de la suma en un procedimiento por que veo que haces el mismo query como 20 por ejemplo este
SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text) eso lo podrias ejecutar en un solo query antes que lo demas[FONT=verdana,geneva,lucida,'lucida grande',arial,helvetica,sans-serif], guardarlo en una vaiable y despues utilizar la variable, igual con este [/font]SELECT COALESCE(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E') |
#3
|
||||
|
||||
Nunca habia visto una consulta taaaaaaan larga, me parece que el diseño de la misma es deficiente ya que utilizas al parecer varias consultas anidadas. Recordemos que las consultas anidadas se realizar por CADA consulta principal que la llame, de ahí el motivo de que mientras más registros más tarda la consulta.
Una consulta bien diseñada no debe de ser afectada por el número de registros que se tengan. Por otro lado, no siempre se puede solucionar todo con una sola consulta, a veces es necesario hacer 1 o 2 más y hacer el proceso directamente en nuestro sistema Delphi. Revisa los selects anidados que pones para saber que bodega es la que estas consultando, me parece que ahi tienes el error. Te recomiendo que primero hagas un solo select para obtener el id de la bodega que el usuario seleccionó y una vez obtenido, solamente sustitúyelo en todos los selects que pusiste. Algo así:
revisa el tema de parámetros también te puede simplificar la vida. No recuerdo si IBQUERY se comporta igual que TZQuery (de Zeos) que automáticasmente crea los parámetros de acuerdo con el código SQl que uno le ponga. Aún así todavía hay querys anidados que no les veo razón de ser.
__________________
AKA "El animalito" ||Cordobés a mucha honra|| |
#4
|
|||
|
|||
Yo tambien pienso que ese SELECT esta muy largo y con toda la pinta de deficiente; pero para ser honesto esta muy largo que no me puse a ver todo lo que pide y si realmente esa es la unica manera de hacerlo.
Pero veo que utilizas un tan solo WHERE con todas las condiciones (amarradas usando infinidad de ANDs) y select anidados para seguramente relacionar tablas, obtener y resumir datos, y todo lo que se necesite. Cita:
Lo primero que deves de dominar es enlazar las tablas usando indices esto se hace enlazandolas usando INNER JOIN, despues deves en UNA SOLA PASADA resumir la tabla de inventarios por codigo usando los criterios que necesites, y despues linkear ese resultado con la tabla de insumos para obtener el listado final. Última edición por Bpascal fecha: 17-01-2009 a las 03:00:37. |
#5
|
|||
|
|||
Esta medio complicado esto parece, les expongo otro ejemplo que me esta ocurriendo lo mismo.
Que es lo que me entrega. Un listado de las distintas area, con su respectivo sumatoria de montos de ordenes de compra y la cantidad de ordenes con presupuesto escedido. Como puedo mejorar esta consulta SQL para que sea mas eficiente. La verdada es que no entiendo muy bien como opera el iiner joi, ni menos si es que me puede servir en mi caso. Bueno los dejo aer si me ponene un ejemplo de como quedaría mi consulta agregando alguna mejor idea. Saludos |
#6
|
||||
|
||||
Selects anidados por doquier, proba metiendolos en un procedimiento para minimizar el numero de selects y poder utilizar mejor los planes (PLAN). Yo tenia una consulta muy exigente tambien y opte por generar tabla temporal para la consulta pase de 2min a 2seg en el rendimiento. te estoy hablando de una consulta que ataca a mas de 6millones de registros.
|
#7
|
|||
|
|||
La verdad es que no se como hacer lo otra manera que no sea con select anidados.
Alguien me puede sugerir un codigo que consulte lo mismo pero con otra estructura. gracias |
#8
|
||||
|
||||
Hola, prueba esto a ver. 1.- Relacionamos las tablas por campos comunes, porque si se hace un producto cartesiano de las tablas el nº de registros leidos se eleva exponencialmente, por lo que la demora de la obtención de los resultados también se dispara. where P.EMPRESA=C.EMPRESA AND P.ANO = C.ANO AND P.MEs=C.MES 2.- Para sumar o contar sólamente los registros que queremos y no todos, usamos el CASE COUNT ( -- Cuenta los registros que CASE -- cumplan esta condición WHEN (OFICINA =:SCOMBOBOX3.TEXT AND ESTADO ='F') --si la cumplen, THEN suma el campo MONTO_COMPR ( importe comprado) ELSE suma 0 END ) 3.- Ponemos los parámetros correspondientes AND P.EMPRESA =:EMPRESA AND P.ano=:ano AND P.mes=:mes AND P.OFICINA =:OFI Estoy suponiendo que :SCOMBOBOX3.TEXT y :OFI son parametros diferentes, y logicamente SCOMBOBOX3.TEXT se debe asignar como parámetro - es por la hora , que tengo frito el cerebelo - Ya tienes para romperte la cabeza un rato. Lo de las bodegas es parecido, pero más largo, y no me meto con ello porque no es hora de emborracharse Un saludo, .. ya nos dirás. PD: Hay algun error de sintaxis, pero lógicamente, te toca hacer algo de curro
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino - |
#9
|
|||
|
|||
Por mas que trato es no funciona para nada, me entrega datos que no tienen nada que ver con lo que necesito.
Explico un poco mas la situacion. Es una aplicacion de compras y presupuesto, la cual consta de 2 tablas, compras y presupuesto. La tabla presupuesto contiene los campos empresa, area, subarea, ano, mes y monto. El presupuesto esta ingresado como sigue: empresaareasubareaanomesmontoemp1apoyomantencion20091500emp1apoyomantencion20092200emp1apoyosueldo20091100emp1apoyootro2009250emp1admgasto2009180emp2apoyomantencion2009170emp2apoyomas2009155 asi para todos las area, subarea hasta el mes de diciembre 2009 Entoces, lo que necesito es que me arroje un listado de todos las distintas area de la empresa "emp1", en donde ademas muestre la suma de todos los montos para cierto mes y año en particular (primeras 2 columnas intervienen la tablas presupuesto) la Tabla compras es asi: empresaareasubareaanomesmonto_comprestadoemp1apoyomantencion2009120Femp1apoyomantencion2009110Femp1apoyosueldo200912Pemp1apoyootro200916Femp1admgasto2009112Pemp2apoyomantencion2009121Pemp2apoyomas200911F Puede ocurrir que no se genero ninguna compra para algun empresa, area, año y mes en particular. Las columnas 3 y 4 de mi consulta hacen referencia a la tablas compras. En la 3 columna, debe sumar todas la compras, asociadas al empresa, area, año y mes escojida y esten finalizadas (estado F), y la 4 columna la cantidad de compras asociadas a la empresa, area, año y mes que esten excedidas (estado = P) o que deberia arrojar seria para empresa = emp1, año=2009, mes=1 areamontocompraexcedidaapoyo600361adm8001 descarte el campo oficina, simplemente es otro parametro mas, que debe coincidir para ambas tablas. Ayudenme con esto, ya no se que hacer. Gracias |
#10
|
|||
|
|||
adjunto excel con ejmplo de tablas para una mejor comprension
Saludos Última edición por mjjj fecha: 19-07-2010 a las 20:17:40. |
#11
|
||||
|
||||
Hola,
Ok, me tomé un tiempito para leer detenidamente el querry original de los insumos y creo que tengo claro que es lo que hace. Principalmente tienes 2 tablas, INSUMOS es la tabla maestra donde estan los codigos, descripciones etc de los productos, y la tabla INVENTARIOS donde estan los movimientos de los productos tanto de entrada como de salida, la cual se diferencia por el campo tipo; "I" significa ingreso y "E" significa egreso. La meta es basicamente listar todos los codigos de INSUMOS con su saldo en unidades y valores resumidos desde la tabla INVENTARIOS, el resumen se puede condicionar por ejemplo de que solo sea de determinada empresa, bodega o incluso solo incluyendo hasta determinada fecha si se quiere, etc. En tu querry tu estas haciendo a la tabla de movimientos (que por naturaleza es grande) lo siguente: - 2 veces SELECT para obtener el data saldo en unidades - 2 veces SELECT para obtener el dato saldo en valores - 4 veces select par obtener el dato de costo unitario. En total 8 selects por la cada codigo de la tabla insumos, la cual tu dices tiene aprox 1000 registros, esto genera que se le apliquen aproximadamente 8,000 selects a una tabla llamada inventario que seguramente tiene 20 mil o 30 mil y creciendo por que son las transaciones. Vemos como podemos bajar un poco la cantidad de SELECTS aplicados. ----------------------------- Primero dejame explicarte el uso de INNER JOIN para relacionar 2 tablas, realmente es bien simple: La tabla INVENTARIOS tiene el campo "bodega" que es el numero Id de una bodega tambien tiene el campo "empresa" que es el numero Id de una empresa Tienes una tabla llamada BODEGA la cual tiene su campo "ID", su campo "empresa", y su campo "nombre" Si dedeas generar un listado de la tabla inventarios incluyendo una columna que diga el nombre de la bodega tu lo haces de la siguiente manera: Cita:
Cita:
Ahora tu te preguntaras "y que?, si es lo mismo entonces cual es la ventaja de hacerlo con WHERE ó hacerlo con INNER JOIN ?" Usar inner join te permite liberar el WHERE de las condiciones utilizadas para enlazar tablas, muchas veces es necesario utilizar 4 o 5 tablas al mismo tiempo y obtener informacion de ellas, escribir un WHERE donde estan todas esas relaciones mas las condiciones que son para selecionar los registros deseados harian un where ya muy complicado. Utiliza la clausula WHERE solo para escribir las condiciones que se requieren para seleccionar los registros deseados en la tabla principal; y utiliza la clausula INNER JOIN para especificar la relacion que hay entre las tablas involucradas, esto ayuda al SQL a saber la mejor manera de utilizar los indices de las tablas para hacer la relacion. ------------------------------ Ok, ahora a continuacion te paso a como haria yo el querry que genere el listado en discucion; Yo primero resumiria la tabla de inventarios utilizando UN tan solo SELECT creando una tabla temporal, esto reducira drasticamente la cantidad de select con tantas condiciones. De hecho la maneras mas optimia seria 2 tablas temporales, una por las entradas y otra por las salidas pero no quiero hacerte mas largo el post: Cita:
Ahora hagamos otro querry para generar el listado final enlazando la tabla INSUMOS y la tabla #temp_table: Cita:
--------------------------------------------- Te recomiendo que le des una ojeada a la informacion sobre tablas temporales, estas se borran solas cuando se cierra la sesion, pero deveras borralas manualmente mientras la seccion este activa antes de hacer una siguiente consulta porque el SELECT INTO fallará cuando encuentre que ya hay una tabla temporal con ese nombre. saludos. Última edición por Bpascal fecha: 17-01-2009 a las 23:52:36. |
#12
|
|||
|
|||
Mcuhas gracias por tu excelente y completa explicación, me quedo todo mucho mas claro, pero siempre surgen nuevas dudas.
El tema de la tablas temporales, es lo mismo que las talbas en memoria? Estuve leyendo sobre alguno componentes que puedes servir para esto, será necesario instalar algun componente especial, o simplemente se puede generar con algun comando SQL. Por otro lado, y gracias a la explicación del inner join, es que he tratado de implmentar esta idea a otra consultas dentro de mi aplicación y la verdad es que no me funciona nada de bien. Expongo mi código.
lo que funciona bien, pero es relativamente lento, el mismo problema anterior, Select anidados, pero no se como hacerlo de otra manera.... ayuda porfa!! intente utilizando esto:
Me arroja valores que no tienen nada que ver, obvimante no funciona, el porqe es el que no se. Lo que intento hacer es lo siguiente: Un distado de todas la distintas area (tabla presupuesto), además de la sumatoria de lo compra (tabla compra estado = 'F') y la cantidad de compras excedidas (tabla compras estado ='P') Ojala me puedan echar una manito con esta consulta. Gracias |
#13
|
||||
|
||||
Bueno,
aqui tienes tu código. Te faltaba meter la empresa en las condiciones del inner join No se de dónde sale el parámetro oficina, que he quitado. El resto, es lo mismo que tenías, sólo tienes que comparar las querys. El valor del parámetro empresa debe coincidir con algún valor de la tabla (emp1). Me he basado en el juego de ensayo de la hoja de cálculo que has incluido. Te aconsejo que juegues en una bañera primero, luego en una piscina y luego definitivamente te tires al mar. por qué? cuando hagas este tipo de consultas, hazlas sacando todos los valores, y de ahí vas filtrando poco a poco, es decir, intenta sacar los valores de las empresas primero, luego las empresas y las áreas, luego filtrando por empresa, area, .... hasta que salga lo que quieras.
Saludos, cuentanos cómo te queda.
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino - |
#14
|
|||
|
|||
Esto no funciona ni por mas empeño que le ponga...
Lo unico que concuerda son las distintas area que tengo, pero los montos y la cantidad estan disparados. A mi entender es que al preguntar a 2 tablas, esta se multiplican, lo que conlleva a un enorme numero de registros. Entonces al hacer una consulta sobre estas tablas, las sumas y cantidad son mayores. Ahora bien, eso creo que es lo que ocurre, pero no se como solucionarlo para obtener lo que ando buscando. Corrijanme si estoi equivocando. Gracias. |
#15
|
||||
|
||||
Qué motor de BBDD usas ?
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino - |
#16
|
|||
|
|||
Firebird 2.0
Saludos |
#17
|
||||
|
||||
Bueno, examina lo que te he marcado en negro. Tiene pinta de que la condicion de la empresa está mal, y por otro lado la oficina no pinta nada en esta guerra - al menos con las tablas de prueba que has aportado -. Por otro lado, haz lo que te comentaba en un post anterior. Quita el group by y las funciones de agregado SUM y COUNT. También los CASE. Una vez hecho esto, ¿ la consulta devuelve los registros que debe devolver para sumar y contar lo que debe ? Si no es así, está mal desde el origen. Revisala, y cuando devuelva los registros que tiene que devolver, vuelves a poner las funciones de agregado y el group by. Ya nos dirás. Por otro lado, tendrás alguna herramienta para ejecutar la query sin recompilar el programa no ? Me refiero la ibexpert o similar. Además, no estaría de más que busques información sobre la normalización de las tablas, ya que la estructura que pones no es muy ortodoxa - no lo digo yo, lo dice la teoría de las buenas prácticas - . Ya nos contarás. Saludos
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino - |
#18
|
|||||
|
|||||
Hola Amigo,
disculpa la tardanza y disculpa que no fui explicitamente claro con el INNER JOIN, inlcluso no te dije toda la historia, la cual es necesario que la domines de lo contrario no podras hacer querys eficientes. Por favor lee detalladamente las explicacion a continuacion del JOIN y al final del post te escribo el querry que ocupas. Primero debes saber que hay dos sabores de JOIN: INNER JOIN LEFT OUTER JOIN si tenemos tabla_A: Id, englishword -------------- 1 bird 2 house 3 dog 5 cat 6 black si tenemos tabla_B: Id, spanishword -------------- 1 pajaro 2 casa 3 perro 5 gato si hacemos el siguiente select:
el resultado seria: Cita:
Ahora, si queremos sacar TODOS los registros de la tabla_A con su respectivo dato de la tabla_B pero tambien incluyendo los que no pudieran tener equivalencia en tabla_B entonces harias:
el resultado seria: Cita:
Hasta aqui ya te estaras imaginando que lo que necesitas es usar LEFT OUTER JOIN, porque basicamente tu quieres repasar todos los registros de la tabla presupuesto la par con resumenes si (si los hubiera) de la tabla compras. Sin envargo en estos ejemplo la relacion es de 1 a 1, o almenos de muchos a 1 es decir, por un registro en la tabla A se espera un tan solo registro en la tabla B, pero el asunto se vuleve diferente cuando la relacion es uno a muchos, por ejemplo: si la tabla B tuviera: Id, spanishword -------------- 1 pajaro 1 ave 1 plumifero 2 casa 3 perro 5 gato como vez, el id=1 esta 3 veces si se aplica el utimo querry el resultado seria: Cita:
Por consiguinete, si tu haces el siguinete querry:
Los sum() y los count() de la tabla COMPRAS saldran bien, pero los de la tabla presupuesto saldran mal ya que ahora hay datos duplicados por cada ocurrencia encontrada en compras, de la misma manera como se duplica el dato "Bird" en el ejemplo que te puse tambien aqui el dato p.monto saldra duplicado. Entonces como se resuleve este tipo de problemas? bueno se resuelve resumiendo la tabla compras UNA TAN SOLA VEZ y enlazando el resultado a la tabla presupuesto usando LEFT OUTER JOIN, Es decir envez de escribir el nombre de la tabla despues del left outer join se puede perfectamente escribir un SELECT que resume ya la tabla antes de hacer el enlazado, ejemplo: Cita:
Ya terminamos?, no todavia, , c.Tot_compr y c.C_mon_compr ya tienen el total de la compra y la cuenta de la compra lista para enlazarla con una tabla mestra, pero tu todavia necesitas resumir la tabla presupuesto para sumar y contar el valor monto. La manera de sumarizar la tabla presupuesto e incluir los totales de la tabla compra sin duplicar se puede hacer usando el comando DISTINCT. creo que este es el querry final: Cita:
Espero que te funcione o por lo menos te haya dado la suficiente informacion para adaptar tus querrys. Última edición por Bpascal fecha: 19-01-2009 a las 22:49:00. |
#19
|
||||
|
||||
Haz dado en el clavo BPascal, lo que el amigo necesitaba es una explicación así de clara de lo joins...mis respetos señor.
Estaba analizando el caso y no se si me equivoque pero al parecer nuestro amigo atacó el problema con lo que él ya sabía sin investigar un poquito más a ver que otras cosas se pueden hacer. A todos nos pasó en su momento y más de uno regresamos a nuestros propios códigos a "optimizarlos" .
__________________
AKA "El animalito" ||Cordobés a mucha honra|| |
#20
|
|||
|
|||
Muchas gracias por tu tan clara explicación, pero ocurre algo, al tratar de hacer la consulta en el IBExpert, me arroja un error en la primera linea, No reconoce el campo "C.T_monto_compr", que es el campo de la sumatoria de la tabla compras "sum(Monto_compr) as T_monto_compr".
Al parecer no reconoce este campo ya que es la respuesta de la segunda consulta. porque ocurrira esto? utilizo Firebird 2.0, sera que en esta version no se puede realizar este tipo de consultas? y lo ultimo, donde agrego la considion del campo estado de la tabla compras? ya que son 2 condiciones distintas, 'F' para la suma y 'P' para el count. Les dejo mis dudas, y de ante mano muchas gracias por todo, me han ayuda muchisimo. Saludos |
|
|
Temas Similares | ||||
Tema | Autor | Foro | Respuestas | Último mensaje |
Facturación e Inventario | silver07 | Conexión con bases de datos | 49 | 22-10-2015 19:45:33 |
SQL inventario | mjjj | SQL | 7 | 12-12-2008 17:13:29 |
Aplicacion + Inventario | mjjj | Varios | 8 | 03-11-2008 15:58:54 |
Costo de Inventario | NickName | SQL | 4 | 09-10-2006 06:30:31 |
Inventario de Hardware | vichovi | API de Windows | 3 | 03-01-2005 15:35:10 |
|