Mi Consulta SQL Funciona pero tarda demasiado

[webmasterplc]

Buenas estoy realizando un procedimiento para consolidar los datos de varias tablas en un sola, todas tiene la misma estructura, el problema es que el proceso tarda demasiado y mientras lo hace me cuelga la maquina y no se por donde va (uso tablas dbisam, no las uso por eleccion)
mi codigo es este

Código Delphi [-]
 
with sqnominadetalle do
  begin
    close;
    SQL.Clear;
    SQL.Add('SELECT * FROM a2NominaDetalle');
    ExecSQL;
    sqnominadetalle.First;
    while not sqnominadetalle.Eof do
    begin
      cedintegrante := sqnominadetalle.FieldByName('FNM_INTEGRANTE').AsString;
      codconstante := sqnominadetalle.FieldByName('FNM_CONSTANTE').AsString;
      fnmtipo := sqnominadetalle.FieldByName('FNM_TIPO').AsInteger;
      fnmvalorperiodo := sqnominadetalle.FieldByName('FNM_VALORPERIODO').AsCurrency;
      fnmvalordefault := sqnominadetalle.FieldByName('FNM_VALORDEFECTO').AsCurrency;
      fnmdescripcion := sqnominadetalle.FieldByName('FNM_DESCRIPCIONPERIODO').AsString;
      with sqverificardetalle do
      begin
        close;
        ParamByName('PINTEGRANTE').AsString := cedintegrante;
        ParamByName('PCONSTANTE').AsString := codconstante;
        ExecSQL;
        if not sqverificardetalle.IsEmpty then
        begin
          with squpdatenomina do
          begin
            ParamByName('PTIPO').AsInteger := fnmtipo;
            ParamByName('PCONSTANTE').AsString := codconstante;
            ParamByName('PINTEGRANTE').AsString := cedintegrante;
            ParamByName('PVALOR1').AsCurrency := fnmvalordefault;
            ParamByName('PVALOR2').AsCurrency := fnmvalorperiodo;
            ParamByName('PPERIODO').AsString := fnmdescripcion;
            ExecSQL;
            close;
          end

        end
        else
        begin
          with dbtsdetallenomina do
          begin

            tablename := 'a2NominaDetalle';
            Open;
            Insert;
            Append;
            FieldByName('FNM_TIPO').AsInteger := fnmtipo;
            FieldByName('FNM_CONSTANTE').AsString := codconstante;
            FieldByName('FNM_INTEGRANTE').AsString := cedintegrante;
            FieldByName('FNM_VALORDEFECTO').AsCurrency := fnmvalordefault;
            FieldByName('FNM_VALORPERIODO').AsCurrency := fnmvalorperiodo;
            FieldByName('FNM_DESCRIPCIONPERIODO').AsString := fnmdescripcion;
            Post;
            close;
          end;
        end;
        close;
      end;
      // ShowMessage('Net');
      sqnominadetalle.Next;
    end;

    with sqstatus do
    begin
      close;
      SQL.Clear;
      SQL.Add('UPDATE SEDES SET SED_STATUS = :PSTATUS');
      SQL.Add('WHERE ID_SEDE=:PIDSEDE');
      ParamByName('PIDSEDE').AsString := codsede;
      ParamByName('PSTATUS').AsInteger := 2;
      ExecSQL;
    end;
  end;

[Neftali [Germán.Estévez]]

Estaría bien que explicaras un poco lo que deseas hacer, facilita el que los demás puedan entenderlo, más que tener que revisar el código.
Por otro lado das pocos datos para saber dónde está la parte que "tarda demasiado".

Lo primero sería añadir un log de tiempo a ese código para saber qué parte está tardando.
También estaría bien saber de cuantos registros hablamos (en cada uno de los Datasets -sqnominadetalle, sqverificardetalle, squpdatenomina, dbtsdetallenomina,...-).

Una vez que añadas los de tiempos y sepas qué parte tarda, se pueden revisar si faltan índices en las tablas para las búsquedas (por ejemplo).

Por otro lado veo que primero ejecutas una consulta con sqnominadetalle y para cada registro que recorres con un while, realizas una nueva búsqueda con sqverificardetalle, utilizando los parámetros PINTEGRANTE y PCONSTANTE.

Código Delphi [-]
    while not sqnominadetalle.Eof do
    begin
      cedintegrante := sqnominadetalle.FieldByName('FNM_INTEGRANTE').AsString;
      codconstante := sqnominadetalle.FieldByName('FNM_CONSTANTE').AsString;
      fnmtipo := sqnominadetalle.FieldByName('FNM_TIPO').AsInteger;
      fnmvalorperiodo := sqnominadetalle.FieldByName('FNM_VALORPERIODO').AsCurrency;
      fnmvalordefault := sqnominadetalle.FieldByName('FNM_VALORDEFECTO').AsCurrency;
      fnmdescripcion := sqnominadetalle.FieldByName('FNM_DESCRIPCIONPERIODO').AsString;
      with sqverificardetalle do
      begin
        close;
        ParamByName('PINTEGRANTE').AsString := cedintegrante;
        ParamByName('PCONSTANTE').AsString := codconstante;
        ExecSQL; 
        ...

Deberías intentar realizar eso utilizando una única consulta con una JOIN, De esa forma aunque la consulta será más costosa, pero te evitarás lanzar las consultas de dentro del WHILE.

Se puede seguir, pero es un poco dar palos de ciego sin saber más detalles...

[[kuan-yiu]]

También añadiría, de modo general, que si las tablas tienen muchos campos no hagas un " Select * ", siempre es mejor listar los campos usados y solo los usados.

[Casimiro Notevi]

Falta muchísima información para poder ayudar, como han comentado los compañeros, pero si se trata de pasar una tabla completa a otra, y que tiene la misma estructura, es más simple algo como:

Código SQL [-]
Insert into tabla2 (select * from tabla1);

[webmasterplc]

Buenas tengo las datas en diferentes capetas y en mi proyecto dos db origen y destino

Código Delphi [-]
//etsa consulta se hace en la tabla a2NominaDetalle de la sede o sucursal
with sqnominadetalle do
  begin
    close;
    SQL.Clear;
    SQL.Add('SELECT * FROM a2NominaDetalle');
    ExecSQL;
    sqnominadetalle.First;
    while not sqnominadetalle.Eof do
    begin
      cedintegrante := sqnominadetalle.FieldByName('FNM_INTEGRANTE').AsString;
      codconstante := sqnominadetalle.FieldByName('FNM_CONSTANTE').AsString;
      fnmtipo := sqnominadetalle.FieldByName('FNM_TIPO').AsInteger;
      fnmvalorperiodo := sqnominadetalle.FieldByName('FNM_VALORPERIODO').AsCurrency;
      fnmvalordefault := sqnominadetalle.FieldByName('FNM_VALORDEFECTO').AsCurrency;
      fnmdescripcion := sqnominadetalle.FieldByName('FNM_DESCRIPCIONPERIODO').AsString;

luego en la tabla a2NominaDetalle destino o casa matriz hago una consulta para verificar si el registro existe

Código Delphi [-]
 with sqverificardetalle do
      begin
        close;
        ParamByName('PINTEGRANTE').AsString := cedintegrante;
        ParamByName('PCONSTANTE').AsString := codconstante;
        ExecSQL;
        if not sqverificardetalle.IsEmpty then
        begin
          with squpdatenomina do
          begin
            ParamByName('PTIPO').AsInteger := fnmtipo;
            ParamByName('PCONSTANTE').AsString := codconstante;
            ParamByName('PINTEGRANTE').AsString := cedintegrante;
            ParamByName('PVALOR1').AsCurrency := fnmvalordefault;
            ParamByName('PVALOR2').AsCurrency := fnmvalorperiodo;
            ParamByName('PPERIODO').AsString := fnmdescripcion;
            ExecSQL;
            close;
          end

        end
        else
        begin
 //Este Caso de Insertar un registro es poco probable que exista        
 with dbtsdetallenomina do
          begin

            tablename := 'a2NominaDetalle';
            Open;
            Insert;
            Append;
            FieldByName('FNM_TIPO').AsInteger := fnmtipo;
            FieldByName('FNM_CONSTANTE').AsString := codconstante;
            FieldByName('FNM_INTEGRANTE').AsString := cedintegrante;
            FieldByName('FNM_VALORDEFECTO').AsCurrency := fnmvalordefault;
            FieldByName('FNM_VALORPERIODO').AsCurrency := fnmvalorperiodo;
            FieldByName('FNM_DESCRIPCIONPERIODO').AsString := fnmdescripcion;
            Post;
            close;
          end;
        end;
        close;
      end;
   
      sqnominadetalle.Next;
    end;

en el segundo bloque es donde tarda demasiado
y por ultimo cambio el status

[Casimiro Notevi]

Antes has dicho 2 tablas y ahora 2 bases de datos.

[webmasterplc]

disculpen no me supe explicar son dos tablas pero están en dos bd distintas

[Casimiro Notevi]

¿Pero exactamente qué quieres hacer? ¿pasar los datos de una a la otra? ¿y si existe un registro con la misma clave primaria entonces no lo pasa, lo actualiza o qué debe hacer?

[webmasterplc]

Si no existe lo inserto (Esto casi no ocurre) y si existe actualizar

[Casimiro Notevi]

Si fuese en la misma base de datos sería facilísimo:

Código SQL [-]
insert or update into table2 (select * from table1);

Si esa BD que usas puedes hacer eso, perfecto, en caso contrario.

Código Delphi [-]
select * from tabla1;
while not tabla1.eof  
  try
    insert into tabla 2...
  except
    update table 2...
  end;
  table1.next
end;

[mamcx]

Empezado por webmasterplc

el proceso tarda demasiado y mientras lo hace me cuelga la maquina y no se por donde va

Bueno, igual que los demás compañeros falta información, pero estos puntos son generales en cualquier proceso de carga de datos (ELT).

Asi que:

1. Cuanto es "demasiado"?
2. Para que no se "cuelgue" la app lo pones dentro de un Thread o en un proceso aparte y vas comunicando el progreso
3. Para "saber por donde va" es critico que tengas integrado un Log con el resultado de cada paso.
4. Los procesos de ELT se hacen en batch. Algo asi como "cargar cada 500 registros"

Si tienes una base de datos no muy moderna o limitada (que supongo es el caso con dbisam), un método que uso, ya que me toca integrar con muchas fuentes de datos, es cargar los IDs de lo que quiero checar en un hasmap y usarlo para comparar de forma rápida. Algo asi como (pseudo-codigo):

Código Delphi [-]

mi_indice := SELECT id FROM db1.Customer

for row in SELECT * FROM db2.Customer 
begin
   if row.id in mi_indice 
   begin
   // Existe
  end else begin
  //No existe
  end;
end

Asi debería ser muy rapido, mas que hacer un select en cada ciclo.

[webmasterplc]

1. Cuanto es "demasiado"?
Tarda mas de 20 minutos con una tabla de 120 registros.
2. Para que no se "cuelgue" la app lo pones dentro de un Thread o en un proceso aparte y vas comunicando el progreso
Voy a Informar,e de esto ya que no lo manejo
3. Para "saber por donde va" es critico que tengas integrado un Log con el resultado de cada paso.
Voy a Hacerlo
4. Los procesos de ELT se hacen en batch. Algo asi como "cargar cada 500 registros"
Perfecto

Voy a probar lo que propones

[Casimiro Notevi]

Empezado por webmasterplc

Tarda mas de 20 minutos con una tabla de 120 registros.

Tardo menos en teclear manualmente los registros

[mamcx]

Empezado por Casimiro Notevi

Tardo menos en teclear manualmente los registros

Como estaba de despistado que no note que eran 120 registros!.

Asi, todo lo que dije es "overkill". La forma mas eficiente entonces es mucho mas simple:

Carga todos los registros al inicio en un DataSet en memoria y haz los procesos contra ese.

Código Delphi [-]
var original: TClientDataset = SELECT * FROM db1.source
var destino: TClientDataset = SELECT * FROM db2.dest

// procesas todo en memoria

// Guarda los resultados de destino

[webmasterplc]

Buenas ae ido trabajando siguiendo las recomendaciones, ese procedimiento esta en el evento onclick de un boton, estoy tratando de hacer un procedure para luego llamarlo e un thread pero al hacer el procedure no veo los objetos query y table.
por Ejemplo asi veo los objetos

Código Delphi [-]
procedure TformMenu.btn2Click(Sender: TObject);

begin
     sqsedes.Active:=False;
     sqsedes.Active:=True;
end;

pero si intento hacerlo

Código Delphi [-]
procedure consolidar;

begin
     sqsedes.Active:=False;
     sqsedes.Active:=True;
end;

no veo los query

[Casimiro Notevi]

¿Y eso qué tiene que ver con lo que te hemos aconsejado?
Creo que te estás liando, o puede que no te hayamos entendido lo que quieres hacer realmente.

[Casimiro Notevi]

Es que no tiene sentido que te compliques la vida con hilos y cosas raras para unos pocos registros, eso debe tardar segundos.
Por ejemplo, una simple prueba para que te hagas una idea, tengo una tabla con más de 250.000 registros (y unos 90 campos) y los paso a otra tabla igual, tiempo: 2,6 segundos.
En tu caso, con BD distintas y comprobando los que ya existen para actualizarlos, por código Delphi, etc. calcúlale unos 3 a 5 segundos... como mucho.

[webmasterplc]

En el procedimiento la mayoria son update porque verifico que si existe lo actualice y si no existe lo inserte

[[kuan-yiu]]

Yo tengo un proceso de migración que trabaja sobre tres tablas de la misma BD (una con 25.000 tuplas y las otras con menos de 500) haciendo varias selecciones parciales con sus respectivos bucles, comparaciones, cálculos, inserciones, actualizaciones (de saldos)... y en el peor de los escenarios tarda 3 minutos.

[Neftali [Germán.Estévez]]

Empezado por webmasterplc

procedure TformMenu.consolidar;
begin
sqsedes.Active:=False;
sqsedes.Active:=True;
end;

no veo los query

Ese procedimiento consolidar no es de clase (como el otro). No tiene la parte en rojo para que me entiendas.
Tienes 2 opciones:

1) Definirlo dentro de la clase (parte privada).
2) Pasarle los objetos necesarios como parámetros.

Por otro lado, trabajar con Threads y Base de datos no es trivial. Las conexiones no suelen ser Threadsafe, así que al thread ademas de pasarle toda la información necesaria para trabajar, deberás dotarle de lo necesario (ConnectionString, por ejemplo) para crear una nueva conexión sobre la Base de Datos.