Optimizacion De Rendimiento

[juanlito]

Buenas a todos,

Soy nuevo en el foro y tengo un problema(como casi todos los nuevos)
Tengo una aplicacion que accede a una BD de access, he realizado las consultas y el codigo y me funciona sin errores, el problema es que en la tablas hay muchos registros, en una de ellas unos 114000 y en otra unos 270000, debido a esto la ejecuccion de mi codigo se eterniza hasta limites increibles, media hora para un codigo como el siguiente

 
Datamodule1.Query2.Active:=true;
  datamodule1.Query2.First;
  while not(datamodule1.Query2.Eof) do
  begin
   try
     datamodule1.Tbtic.Insert;
     datamodule1.Tbtic.FieldByName('telno').AsString:=datamodule1.Query2.fieldbyname('telno').AsString;
     datamodule1.Tbtic.FieldByName('duratime').AsString:=datamodule1.Query2.fieldbyname('duratime').AsString;
     datamodule1.Tbtic.FieldByName('address').AsString:=datamodule1.Query2.fieldbyname('address').AsString;
     datamodule1.Tbtic.FieldByName('rptime').AsDateTime:=datamodule1.Query2.fieldbyname('rptime').AsDateTime;
     datamodule1.Tbtic.FieldByName('sumcoin').AsFloat:=datamodule1.Query2.fieldbyname('sumcoin').AsFloat;
     datamodule1.Tbtic.FieldByName('dialno').AsString:=datamodule1.Query2.fieldbyname('dialno').AsString;
     cad:= copy(datamodule1.Query2.fieldbyname('starttime').AsString,7,2)+'/'+copy(datamodule1.Query2.fieldbyname('starttime').AsString,5,2)+'/';
     cad:=cad+ copy(datamodule1.Query2.fieldbyname('starttime').AsString,0,4)+' '+copy(datamodule1.Query2.fieldbyname('starttime').AsString,9,2)+':';
     cad:=cad+ copy(datamodule1.Query2.fieldbyname('starttime').AsString,11,2)+':'+copy(datamodule1.Query2.fieldbyname('starttime').AsString,13,2);
     fecha:=strtodatetime(cad);
     datamodule1.Tbtic.FieldByName('starttime').AsDateTime:=fecha;
     numero_largo:=datamodule1.Query2.fieldbyname('dialno').AsString;
     posicion:=(pos('F',numero_largo));
     if posicion<> 0 then
     begin
       numero:= copy(numero_largo,0,posicion-1);
     end
     else
     begin
       numero:=numero_largo;
     end;
     datamodule1.Tbtic.FieldByName('dialno').AsString:=numero;
     datamodule1.Tbtic.Post;
   except
     showmessage('No se ha podido guardar con exito');
   end;
     datamodule1.Query2.Next;
  end;

el codigo sql del query2 es una simple consulta como la siguiente

Código SQL [-]
SELECT PHONE.TELNO AS TELNO, PHONE.ADDRESS AS ADDRESS, TAG26.RPTIME AS RPTIME, TAG26.STARTTIME AS STARTTIME, TAG26.DURATTIME AS DURATIME, TAG26.SUMCOIN AS SUMCOIN, TAG26.DIALNO AS DIALNO
FROM PHONE, TAG26
WHERE PHONE.IDNO=TAG26.IDNO;

no se que hacer, si alguien me dice que migre todo de access a otro creo que lo hare, uso access porque es la BD que me encontre y en la que despues he ido agregando mas tablas y registros, pero ahora tengo este problema.

Me gustaria alguna respuesta en la que no tuviese que migrar los datos, pero Agradeceria cualquier ayuda.

Muchas Gracias

[[newtron]]

Hola.

El problema que estás teniendo es normal al usar bases de datos de escritorio que cuantos más registros tienen el rendimiento cae en picado.
Por aquí seguramente te van a recomendar que te pases a firebird. Es una buena opción pero con el problema de que seguramente tendrás que cambiar algo de código de tu aplicación porque la forma idonea de usar este tipos de bases de datos es con instrucciones sql y no hacer uso de componentes ttable.

Otra opción sería cambiar a firebird y buscar componentes ttable para esta base de datos, que los hay, para que tengas que cambiar el mínimo de código posible.

Saludos

[juanlito]

Muchas gracias por tu respuesta Newtron.

Imaginaba el cambio por ti propuesto, muchas gracias.

una consulta.

Si cambio a Firebird, puedo usar los componentes de interbase que me trae mi delphi 7?? si pudiese usar esos componentes posiblemente casi que no tendria que cambiar mi codigo, solo enlazar dichos componentes con firebird.(no he usado nunca firebird y no se si puedo hacer esto)

A ver si alguien me da solucion con access sino me pondre a esto.

[[newtron]]

Empezado por juanlito

Si cambio a Firebird, puedo usar los componentes de interbase que me trae mi delphi 7??

Lo siento pero no tengo ni idea pero seguro que algún otro forero te contestará a esto que preguntas.

Saludos

[ecfisa]

Hola juanlito y bienvenido a los foros de Club Delphi.

Si cambio a Firebird, puedo usar los componentes de interbase que me trae mi delphi 7??

La respuesta es un rotundo SI.

Te pongo unos enlaces que te servirán. (Uno se refiere a Interbase pero sirve para Firebird igualmente)
. Delphi conectar Firebird con IBX
. Interbase e IBX

Un saludo.

[juanlito]

Muchas gracias.

Pues al lio me pongo, mientras esperaba respuestas he estado viendo un post en el que caral explicaba la instalacion y conexion basica con firebird.

Una ultima question, en el equipo donde tendria que ir instalado el servidor, tengo un MS SQL SERVER 2005 corriendo para una aplicacion externa de uso de mi trabajo.

Puedo instalar firebird sin ningun tipo de conflicto con MS SQL SERVER? tendre problema con los puertos o algo parecido?
Es que para probar que gane en rendimiento puedo instalar firebird en mi equipo local pero luego tendre que usarlo para todos los usuarios en el servidor, y no quiero migrar toda la bd y luego tener problemas de incompatibilidades.

Preguntareis porque no uso MS SQL SERVER en vez de firebird, posiblemente porque firebird es del que he recibido respuesta optima para mejor mi rendimiento y ademas visitando el foro antes de preguntar he observado que era el mas usado por aqui.

Gracias

[ecfisa]

Hola juanlito.

Puedo instalar firebird sin ningun tipo de conflicto con MS SQL SERVER?

No he tenido la oportunidad de probarlo por lo que no apostaría la cabeza a que sí.
Pero en principio, creo que no habría problemas ya que por defecto Firebird usa el puerto 3050 y leí por ahí que MS SQL Server el 1433.

Saludos.

[juanlito]

Muchas Gracias a todos por vuestras respuestas, ya consegui conectar con la base de datos de firebird, y importe tambien los datos sin problema.

Pero mi consulta sigue siendo lenta, se que usaba componentes tipo TTable y TQuery, y con esos componentes el resultado seria muy parecido con ambas bases de datos.

No se si para preguntar como solucionar esto tengo que abrir otro hilo, o puedo hacerlo en este mismo, voy a ponerlo aqui y si hay que cambiarlo me lo comentais y creo uno nuevo donde corresponda.

Aqui explicare un poco mas mi problema, tengo que unir los campos de 2 tablas en otra resultante, uniendo solo los campos necesarios, el unico gran problema es que el tipo de dato de una de las tablas es string y en la tabla resultante tiene que ser un timestamp(esto es necesario para siguientes consultas y funcionar correctamente con el valor), he probado con un stored procedure para que fuese el servidor firebird el que hiciese la conversion pero al invocar despues el stored procedure desde delphi me da un error, creo que con el dialect de la base de datos, aunque no estoy seguro.

Tambien he estado viendo por la documentacion que me aporto ecfisa que un stored procedure puedo ejecutarlo desde un IBSQL, pero no he entendido como se hace, por eso he usado el IBstoredproc.

A continuacion pongo el codigo del stored procedure por si hay alguien que ve algo mal, posiblemente con la funcion de conversion de string a timestamp.

El error que me da al ejecutar es :
Project compara.exe raised exception class EIBInterBaseError with message 'conversion error from string "20/06/2011 19:37:17" -sql_dialect set database dialect n'

El codigo

CREATE OR ALTER PROCEDURE INSERTAR 
as
declare variable dn varchar(255);
declare variable sc double precision;
declare variable dt varchar(255);
declare variable st varchar(255);
declare variable cad varchar(255);
declare variable t timestamp;
declare variable rt timestamp;
declare variable ad varchar(255);
declare variable tn varchar(255);
begin
  /* Procedure Text */
FOR SELECT PHONE.TELNO AS TELNO, PHONE.ADDRESS AS ADDRESS, TAG26.RPTIME AS RPTIME, TAG26.STARTTIME AS STARTTIME, TAG26.DURATTIME AS DURATIME, TAG26.SUMCOIN AS SUMCOIN, TAG26.DIALNO AS DIALNO
FROM PHONE, TAG26
WHERE PHONE.IDNO = TAG26.IDNO
INTO :TN, :AD, :RT, :ST, :DT, :SC, :DN      DO
begin
    CAD = substring(:st  from 7 for 2) || '/' || substring(:ST from 5 for 2) || '/' || substring(:st from 1 for 4) || ' ' || substring(:st from 9 for 2) || ':' || substring(:st from  11 for 2) || ':' || substring(:ST from 13 for 2);
    T = cast (cad as timestamp) ;
end
    INSERT INTO tic(TELNO, ADDRESS, RPTIME, STARTTIME, DURATIME, SUMCOIN, DIALNO)
    VALUES (:TN, :AD, :RT, :T, :DT, :SC, :DN);
  suspend;
end^

Muchas gracias

[[fjcg02]]

A bote pronto, parece que el formato que traga es MM/DD/YYYY HH:MM:SS

y tu le estás pasando DD/MM/YYYY HH:MM:SS

Prueba a ver cambiando el orden.

por otro lado, no puedes hacer el cast de string timestamp directamente ?

Saludos

[juanlito]

Muchas gracias fjcg02
Acertaste de pleno con el problema, es que como yo ya tenia las consultas hechas y sobre access me iban bien pues no pense que el formato de la fecha me cambiase ahora, pero por lo visto he de tener mas cuidado.

Al igual que dije con la consulta anterior, si este hilo ya ha perdido su razon de ser y tengo que abrir otro me lo comentais y lo hago, pero mientras tanto sigo aqui porque en realidad es todo sobre lo mismo,(o al menos asi lo creo)

Sigo modificando todo mi codigo para que me funcione con firebird en vez de access y me he encontrado otro problema, tengo una consulta algo compleja (la terminaba con codigo en delphi sacando datos de varios querys ) que he decidido hacerla con un otro SP (le estoy cogiendo el gustito) pero me da error, a continuacion voy a poner el codigo y explicar lo que quiero que haga, quizas este mal pero viendo otros hilos en el foro he creido que era la forma adecuada.

Código SQL [-]
 
CREATE OR ALTER PROCEDURE COMP_GUAN_PLATA 
returns (
    CALLING varchar(255),
    CALLT timestamp,
    CALLED varchar(255))
as
declare variable LINEA varchar(255);
declare variable LON integer;
begin
  /* Procedure Text */
 FOR SELECT GUAN.callingnumber, GUAN.callednumber, GUAN.calltime FROM guan
 INTO :CALLING, :CALLED, :CALLT    DO
 BEGIN
   LON =  CHAR_LENGTH (:CALLED);
   if (:LON > 9) then
   begin
     CALLED = left(:CALLED,:LON-4);
     CALLED = :CALLED ||'XXXX';
   end
   CALLED = :CALLED ||'%';
   SELECT PLAT.LINEA FROM PLAT WHERE (((PLAT.LINEA) = :CALLING) 
AND ((PLAT.DESTINO) Like :CALLED) AND ((PLAT.FECHA_HORA_INICIO)>DateAdd(hour,-2,:CALLT) 
And (PLAT.FECHA_HORA_INICIO)hour,2,:CALLT)))
   INTO :LINEA ;
   if (:LINEA IS NULL) then
   begin
    EXIT;
    suspend;
   end
   else
   CALLED = null;
   CALLT = null;
   CALLING = null;
  suspend;
 END

tengo 2 tablas, guan y plat, tengo que comparar si todos los registros de guan se encuentra en plat, y mostrar los que no se encuentren, hasta ahi todo simple, el problema es que tengo que modificar un campo de guan para que coincida con plat (siempre tienen la misma diferencia) y luego quiero que esos registros seo se produzca casi en la misma fecha y hora, he usado un intervalo de 2 horas arriba - 2 horas abajo.

como el SP me devuelve solo un registro, luego uso un IBquery con sql: select * from COMP_GUAN_PLATA , para que me muestre todos los registros resultantes.

Me da un error cuando lo ejecuto en el IBExpert. diciendo: "multiples rows in singleton select", esto sera debido porque puede darse el caso de que en el intervalo que uso de 4 horas pueda repetirse el registro, pero a mi solo me interesa que no se de nunca, si se da una o varias me da igual, no se mostraria en ninguno de los 2 casos, solo cuando no exista.

Espero despues de este parrafon haberme explicado con claridad.

Gracias

[[fjcg02]]

Código SQL [-]
   SELECT PLAT.LINEA FROM PLAT WHERE (((PLAT.LINEA) = :CALLING) 
AND ((PLAT.DESTINO) Like :CALLED) AND ((PLAT.FECHA_HORA_INICIO)>DateAdd(hour,-2,:CALLT) 
And (PLAT.FECHA_HORA_INICIO)hour,2,:CALLT)))
   INTO :LINEA ;
   if (:LINEA IS NULL) then
   begin
    EXIT;
    suspend;
   end

Cambia estas sentencia por

Código SQL [-]
   SELECTcount( PLAT.LINEA) FROM PLAT WHERE (((PLAT.LINEA) = :CALLING) 
AND ((PLAT.DESTINO) Like :CALLED) AND ((PLAT.FECHA_HORA_INICIO)>DateAdd(hour,-2,:CALLT) 
And (PLAT.FECHA_HORA_INICIO)hour,2,:CALLT)))
   INTO :VECES ;
   if (:VECES IS NULL)  then
   begin
    EXIT;
    suspend;
   end

Preguntas cuantos registros cumplen, y ya está. Tengo la duda de que si no hay registros te devuelve null o 0. Compruebalo y nos cuentas.
No puedes devolver en una variable varios valores, que es lo que ocurre cuando hay más de un registro.

Saludos

[juanlito]

Muchas Gracias,

Creo que me funciona, he tenido que poner IF (:LINEA = 0) y quitar la linea EXIT (realmente no se porque, ni porque la he quitado ni porque tenia que ir), en este caso el IBExpert me muestra los datos, pero me muestra muchas lineas con null, con lo cual he intentado en mi apliacion no mostrarlo,en vez de usar el codigo para el IBQUERY:

Código SQL [-]
 
SELECT * FROM COMP_GUAN_PLATA

uso

Código SQL [-]
 
SELECT * FROM COMP_GUAN_PLATA
WHILE (CALLING IS NOT NULL) AND (CALLED IS NOT NULL)

Con esto me muestra los datos, creo que correctos, pero el problema es que tanto en el IBExpert, como en el Grid que tengo enlazado al IBQuery, cuando intento moverme por los registros necesitan segundos para refrescar, en el grid me va algo mejor que en el IBExpert, hay alguna forma de mejorar esto.

Podria alguien explicarme porque no he tenido que usar el EXIT? o en que casos se suele usar.( esto no es que me haga falta para mi codigo ahora, sino por aprender para un futuro, para entenderlo mejor)

Creo que ya con esto ya podre migrar la aplicacion entera, tengo mucho mas codigo que cambiar pero casi todo es consultas bastante similares a esta ultima.

Muchisimas gracias a todos y a ver si voy mejorando para poder yo tambien colaborar ayudando a otros.

[juanlito]

Buenas, se que quizas no pueda pedir mas, ya que las tablas tienen bastantes registros unos 200000 cada una, pero este ultimo SP del que he hablado, me tarda 15 minutos en mostrarme datos, se que antes era mas de 2 horas, y que he Optimizado mucho, pero aun asi para que el cliente decida, ahora veo datos, y necesite 15 minutos pues..... ya saben se aburre de esperar.

Podria hacerlo de alguna otra forma que mejorase mas aun, las pruebas ya las estoy haciendo con la BD en otro equipo remoto, para que salga como el resultado final.

Ganaria velocidad si reduzco el numero de columnas de las tablas, en realidad las tablas tienen muchos campos que no uso, con menos campos en las tablas seria mas rapido o eso influye poco?

Gracias de antemano.

[[fjcg02]]

Hola Juanlito:

yo intentaría varias cosas:
- hacer todo en una sola query, es decir, con 'CASE' intentar hacer en una sola select lo que haces en el procedimiento almacenado. Si lo consigues, podrías dejarlo en una vista en lugar de un procedimiento almacenado.
- Revisar que los campos por los que preguntas, cruzas y filtras tienen indices. Si no tienen, añadirlos y ver si afecta al rendimiento cuando se hacen las inserciones, ya que al motor le costará algo más.
- Intentar cambiar de la condición ((PLAT.DESTINO) Like :CALLED) el 'like' y poner '=' ( si puedes ).

Ya nos contarás. Si tienes alguna dificultad, ponnos la bbdd con información para que podamos probar.

Saludos

[juanlito]

Ante todo disculparme por el retraso en responder, me han surgido otras cosas que me tienen muy ocupado y no he podido avanzar con mi problema, ni probar cosas nuevas.

Muchas gracias por tus sugerencias, en cuanto tenga un rato las probare como es debido.

Lo de los indices, deberia de estar indexado pero lo comprobare, como los datos vienen importados de access quizas algunos indices no sean los mejores, vi que se habian creado indices y por eso no les presté la debida atencion.

Con respecto a hacer una sola query con CASE, realmente no se como hacerlo, si hubiera sabido ten por seguro que no tendria un codigo tan complejo, pero viendo que me lo das como opcion( creia que no se podia hacer), me documentare de como podria hacer que mi codigo se redujese a un query.

Sobre el ultimo punto, hice una prueba rapida, y cambiando el 'like' por '=' la verdad es que no note mejora, no calcule exactamente pero estara en el mismo tiempo.

Un saludo y seguire informando de los avances cuando pueda ir probando.

Muchas Gracias

[juanlito]

Buenas Tardes,

consegui hacer todo y que mi aplicacion fuese rapida, pero pensando en el futuro he observado que si uso

Código SQL [-]
 
SELECTcount( PLAT.LINEA) FROM PLAT WHERE ((PLAT.LINEA) = :CALLING) 
   INTO :VECES ;
   if (:VECES = 0)  then
   begin
    EXIT;
    suspend;

El procedimiento cada vez que la tabla vaya creciendo se ira ralentizando porque tendra que finalizar la consulta y a mi con que encuentre un campo me vale.

He estado consultando y he encontrado que con el FIRST podria hacer lo que prentendo, en cuanto encuentre un campo parar la consulta porque ya es distinto de 0 y con eso me sirve, el principal problema es que he puesto el siguiente codigo

Código SQL [-]
  SELECT FIRST 1 ( PLAT.LINEA) FROM PLAT WHERE ((PLAT.LINEA) = :CALLING)
  INTO :VECE;
   if (:VECE IS NULL ) then

Y no me funciona, he probado poniendo order by al final, comparando en el IF en vez de NULL si cadena vacia '' pero no consigo que me funcione como quiero, solo funciona cuando uso el count, pero me gustaria no tener que hacer el select entero y poder quedarme tranquilo de que aunque mis tablas crezcan ( que creceran bastante) no se va a empezar a ralentizar de manera grande.

Pruebo directamente en un view de firebird.

Muchas gracias y si llevo tiempo con el hilo un poco parado es porque siempre procuro solucionar yo los problemas.

Un saludo

[[fjcg02]]

Hola,
PLAT.LINEA es del mismo tipo que VECE ?
VECE será entero, que es el mismo tipo de dato que devuelve COUNT. Por ahí podría darse un conflicto...

Prueba y nos dices

Saludos

[juanlito]

Ya pense en esa opcion,

vece es del mismo tipo que plat.linea VARCHAR(20), por eso no lleva la 's' que llevaba en el codigo del count, para diferenciarlo fue una de las pruebas que hice.

Pero gracias, todas las pruebas son validas, probe con IS NOT NULL, o con cadena vacia '' o con ' ' pero nada siendo un VARCHAR.

[[fjcg02]]

Hola,
he estado echando un vistazo al problema, y creo que lo que te ocurre es que la tabla GUAN tiene muchos registros.

Puedo equivocarme, pero creo que deberías limigtar el nº de registros de la consulta a la tabla GUAN, para que el retardo no se excesivo. Quiero decir, que si estás mirando llamadas, siempre serán las limitadas entre unas horas, minutos, o incluso un día, pero seguramente no se preguntará por todas las llamadas de la tabla.

Respecto al problema de la limitación de registros, en principio la consulta con FIRST es correcta. Has probado a ejecutarla fuera del SP ? Creo que tu idea es correcta respecto a cómo agilizar las consultas, pero a veces es lo que hay. Lo que no sé es si es posible que añadas un campo a tus tablas que se calcule en base al timestamp, para que puedas ponerle un indice y poder cruzar las tablas por él, que sería lo más óptimo. Ten en cuenta que preguntas por un rango de +-2 horas, que es lo que te está rompiendo los tiempos de respuesta.

Un saludo

[juanlito]

Hola

Aunque ha pasado un tiempo voy a intentar explicarlo mejor, porque puse solo una parte del codigo del SP, que era la que queria modificar pero quizas he inducido a error, asi que voy a explicarlo.

El SP consegui que mostrase los datos que queria y que fuese bastante rapido en la respuesta, pero me di cuenta observando en el SQL MANAGER LITE, que podia tener un problema en un futuro, para realizar mis aplicacion introduje en la base de datos los datos solo de un mes, y vi que habia muchas consultas o que se recorrian muchos indices, asi que si ahora solo tengo un mes, cuando tenga datos de un año volvere a tener problemas de Rendimiento.

voy a intentar explicar todo lo que hago, con texto, para que quede claro, si quereis o veis mejor os pongo todo el codigo pero como en realidad el codigo me funciona lo unico que quiero es cambiar una consulta por eso mostre solo esa consulta.

Para cada dato de la tabla que leo que se encuentra entre las fechas introducidas por el usuario,en la primera consulta compruebo si ese dato se encuentra en otra tabla 'GUAN' (en mi pregunta anterior usaba la tabla PLAT, pero realmente es que tengo 2 SP que hacen lo mismo pero inverso, asi que como hoy he estado mirando este pues consulto por este pero el tema es el mismo) este es la consulta que puse porque claro si uso el count, recorro la tabla entera cuando en realidad estoy preguntando solo si ese dato se encuentra en la tabla, realmente solo quiero saber eso no el numero de veces que se repite, si se se encuentra en la tabla pues ya ejecuto el resto del codigo del SP sino pues a siguiente registro.

Pues ese es el tema de este problema, he puesto ese codigo quitando en INTO en un VIEW y funciona correctamente, o me muestra el valor si pertenece o muestra NULL, pero en el SP no funcionan.

Espero haberme explicado con claridad, sino por favor preguntarme y os lo explicare mejor, o pondre el SP completo

Gracias