[[gluglu]]

Y todo esto en código Delphi era algo así como ...

Código Delphi [-]
Aux_PType := -1;
Aux_BType := -1;
 
with pDataSetBDM do begin
  SelectSQL.Clear;
  SelectSQL.Add('Select BPT.PAXTYPE, BPT.BOARDTYPE,');
  SelectSQL.Add('case when BK.OPERATORTYPE = 1 then 0 else');
  SelectSQL.Add(' case when BK.FULLCREDIT = 1 then 1 else');
  SelectSQL.Add(' case when BK.ANTIFULLCREDIT = 1 then 0 else');
  SelectSQL.Add(' BPT.OPERATORDEBIT end end end as OPERATOR_DEBIT');
  SelectSQL.Add('from BOOKINGS_PAXTYPE BPT');
  SelectSQL.Add('left join BOOKINGS BK');
  SelectSQL.Add(' on BK.BOOKINGNO = BPT.BOOKINGNO');
  SelectSQL.Add('where BPT.BOOKINGNO = :Txt1');
  SelectSQL.Add('group by PAXTYPE, BOARDTYPE, OPERATOR_DEBIT');
  ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  Prepare;
  Open;
end;
 
pDataSetBDM.First;
while not pDataSetBDM.Eof do begin
  if pDataSetBDM.FieldByName('PAXTYPE').AsInteger = 1 then
    Aux_PType := 1
  else if Aux_PType = -1 then
    Aux_PType := pDataSetBDM.FieldByName('PAXTYPE').AsInteger;
  if not pDataSetBDM.FieldByName('BOARDTYPE').IsNull then begin
    if Aux_BType = -1 then
      Aux_BType := pDataSetBDM.FieldByName('BOARDTYPE').Value
    else if pDataSetBDM.FieldByName('BOARDTYPE').Value <> Aux_BType then
      Aux_BType := -2;
  end;
  pDataSetBDM.Next;
end;
 
with pDataSetBDM2 do begin
  SelectSQL.Clear;
  SelectSQL.Add('Select OPERATORTYPE, FULLCREDIT, ANTIFULLCREDIT');
  SelectSQL.Add('from BOOKINGS');
  SelectSQL.AdD('where BOOKINGNO = :Txt1');
  ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  Prepare;
  Open;
end;
 
if pDataSetBDM2.FieldByName('OPERATORTYPE').AsInteger = 1 then
  Aux_Operator_Debit := 0
else if pDataSetBDM2.FieldByName('FULLCREDIT').AsInteger = 1 then
  Aux_Operator_Debit := 1
else if pDataSetBDM2.FieldByName('ANTIFULLCREDIT').AsInteger = 1 then
  Aux_Operator_Debit := 0
else begin
  with pDataSetBDM2 do begin
    SelectSQL.Clear;
    SelectSQL.Add('Select Count(distinct OPERATORDEBIT), Max(OPERATORDEBIT)');
    SelectSQL.Add('from BOOKINGS_PAXTYPE');
    SelectSQL.Add('where BOARDTYPE is not null');
    SelectSQL.Add('and BOOKINGNO = :Txt1');
    ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
    Prepare;
    Open
  end;
  if pDataSetBDM2.FieldByName('Count').AsInteger = 2 then
    Aux_Operator_Debit := -1
  else
    Aux_Operator_Debit := pDataSetBDM2.FieldByName('Max').AsInteger;
end;
 
Aux_INo := 0;
 
// Locate In Rooming-List
with pDataSetBDM do begin
  SelectSQL.Clear;
  SelectSQL.Add('Select RL.*, BO.OBJECTNO, BO.BUILDINGNO, BO.DATEFROM, BO.DATETO, BO.INTERNALNO,');
  SelectSQL.Add('BPD.PAXTYPE1_QUANT as BPD_P1Q, BPD.PAXTYPE2_QUANT as BPD_P2Q, BPD.PAXTYPE3_QUANT as BPD_P3Q,');
  SelectSQL.Add('BPD.BOARDTYPE1 as BPD_B1, BPD.BOARDTYPE2 as BPD_B2, BPD.BOARDTYPE3 as BPD_B3,');
  SelectSQL.Add('BPD.ROOMINGLISTNO as BPD_NUM,');
  SelectSQL.Add('BPD.OPERATORDEBIT,');
  SelectSQL.Add('BPD.CREATETIMESTAMP as BPD_CTS, BPD.CREATEUSER as BPD_CUS, BPD.CREATEPOSITION as BPD_CPS,');
  SelectSQL.Add('BPD.MODIFYTIMESTAMP as BPD_MTS, BPD.MODIFYUSER as BPD_MUS, BPD.MODIFYPOSITION as BPD_MPS');
  SelectSQL.Add('from BOOKINGS_ROOMINGLIST RL');
  SelectSQL.Add('left join BOOKINGS_OBJECTS BO');
  SelectSQL.Add(' on BO.ROOMINGLISTNO = RL.ROOMINGLISTNO');
  if Modus < 10 then
    SelectSQL.Add(' and BO.DATEFROM = RL.DATEARRIVAL');
  if (Modus > 10) and (Modus < 20) then
    SelectSQL.Add(' and BO.DATETO = RL.DATEDEPARTURE - 1');
  if (Modus > 20) and (Modus < 30) then
    SelectSQL.Add(' and BO.DATEFROM <= :Txt3 and BO.DATETO >= :Txt2');
  if (Modus > 30) and (Modus < 40) then begin
    SelectSQL.Add(' and ((RL.DATEARRIVAL between :Txt2 and :Txt3 and');
    SelectSQL.Add(' BO.DATEFROM = RL.DATEARRIVAL) or');
    SelectSQL.Add(' (RL.DATEDEPARTURE between :Txt2 and :Txt3 and');
    SelectSQL.Add(' BO.DATETO = RL.DATEDEPARTURE -1))');
  end;
  if (Modus > 40) and (Modus < 50) then begin
    SelectSQL.Add(' and (((RL.DATEARRIVAL < ''TODAY'') and (BO.DATEFROM = RL.DATEARRIVAL)) or');
    SelectSQL.Add(' ((RL.DATEARRIVAL >= ''TODAY'') and (''TODAY'' < RL.DATEDEPARTURE) and (BO.DATEFROM <= ''TODAY'') and (''TODAY'' <= BO.DATETO)) or');
    SelectSQL.Add(' ((RL.DATEDEPARTURE <= ''TODAY'') and (BO.DATETO = RL.DATEDEPARTURE)))');
  end;
  if (Modus > 50) and (Modus < 60) then begin
    SelectSQL.Add(' and ((RL.DATEDEPARTURE = :Txt2 and BO.DATETO = RL.DATEDEPARTURE - 1) or');
    SelectSQL.Add(' (RL.DATEDEPARTURE <> :Txt2 and BO.DATEFROM <= :Txt2 and BO.DATETO >= :Txt2))');
  end;
  SelectSQL.Add('left join BOOKINGS_PAXDETAIL BPD');
  SelectSQL.Add(' on RL.ROOMINGLISTNO = BPD.ROOMINGLISTNO');
  SelectSQL.Add('where RL.BOOKINGNO = :Txt1');
  ParamByName('Txt1').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  if (Modus > 20) and (Modus < 40) then begin
    ParamByName('Txt2').AsDate := StayOverFrom;
    ParamByName('Txt3').AsDate := StayOverTo;
  end;
  if (Modus > 50) and (Modus < 60) then
    ParamByName('Txt2').AsDate := StayOverFrom;
  Prepare;
  Open;
end;
 
while not pDataSetBDM.Eof do begin
 
  Dec(Aux_INo);
 
  Aux_Name := Trim(pDataSetBDM.FieldByName('SURNAME1').AsString);
  If Trim(pDataSetBDM.FieldByName('SURNAME2').AsString) <> '' then Aux_Name := Aux_Name + ' ' + Trim(pDataSetBDM.FieldByName('SURNAME2').AsString);
  If Trim(pDataSetBDM.FieldByName('NAME').AsString) <> '' then Aux_Name := Aux_Name + ', ' + Trim(pDataSetBDM.FieldByName('NAME').AsString);
 
  pDataSet1.Append;
  pDataSet1.FieldByName('RANDOMNO').Value := Aux_R1;
  pDataSet1.FieldByName('RANDOMNO2').Value := Random_No;
  pDataSet1.FieldByName('BOOKINGNO').Value := pDataSetBook.FieldByName('BOOKINGNO').Value;
  if pDataSetBDM.FieldByName('BPD_NUM').IsNull then
    pDataSet1.FieldByName('INTERNALNO').Value := Aux_INo
  else
    pDataSet1.FieldByName('INTERNALNO').Value := pDataSetBDM.FieldByName('ROOMINGLISTNO').Value;
  pDataSet1.FieldByName('ROOMINGLISTNO').Value := pDataSetBDM.FieldByName('ROOMINGLISTNO').Value;
  pDataSet1.FieldByName('BUILDINGNO').Value := pDataSetBook.FieldByName('BUILDINGNO').Value;
  pDataSet1.FieldByName('DATEARRIVAL').Value := pDataSetBDM.FieldByName('DATEARRIVAL').Value;
  pDataSet1.FieldByName('DATEDEPARTURE').Value := pDataSetBDM.FieldByName('DATEDEPARTURE').Value;
  if (Modus > 50) and (Modus < 60) then
    pDataSet1.FieldByName('DATECALC').Value := StayOverFrom;
  pDataSet1.FieldByName('CATEGORYNO').Value := pDataSetBDM.FieldByName('CATEGORYNO').Value;
  pDataSet1.FieldByName('SUBCATEGORYNO').Value := pDataSetBDM.FieldByName('SUBCATEGORYNO').Value;
  pDataSet1.FieldByName('DATEFROM').Value := pDataSetBDM.FieldByName('DATEFROM').Value;
  if not pDataSetBDM.FieldByName('DATETO').IsNull then
    pDataSet1.FieldByName('DATETO').Value := pDataSetBDM.FieldByName('DATETO').Value;
  if not pDataSetBDM.FieldByName('CLIENTNO').IsNull then
    pDataSet1.FieldByName('CLIENTNO').Value := pDataSetBDM.FieldByName('CLIENTNO').Value;
  if not pDataSetBDM.FieldByName('INTERNALNO').IsNull then
    pDataSet1.FieldByName('OBJECTINTNO').Value := pDataSetBDM.FieldByName('INTERNALNO').Value;
  pDataSet1.FieldByName('BOOKINGNAME').Value := Aux_Name;
  pDataSet1.FieldByName('SURNAME1').Value := pDataSetBDM.FieldByName('SURNAME1').AsString;
  pDataSet1.FieldByName('SURNAME2').Value := pDataSetBDM.FieldByName('SURNAME2').AsString;
  pDataSet1.FieldByName('NAME').Value := pDataSetBDM.FieldByName('NAME').AsString;
  if not pDataSetBDM.FieldByName('OBJECTNO').IsNull then
    pDataSet1.FieldByName('OBJECTNO').Value := pDataSetBDM.FieldByName('OBJECTNO').AsString;
  if not pDataSetBDM.FieldByName('PAXQUANTITY').IsNull then begin
    if Aux_PType = 1 then begin
      pDataSet1.FieldByName('PAXTYPE1_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
      if Aux_BType > 0 then
        pDataSet1.FieldByName('BOARDTYPE1').Value := Aux_BType;
    end;
    if Aux_PType = 2 then begin
      pDataSet1.FieldByName('PAXTYPE2_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
      if Aux_BType > 0 then
        pDataSet1.FieldByName('BOARDTYPE2').Value := Aux_BType;
    end;
    if Aux_PType = 3 then begin
      pDataSet1.FieldByName('PAXTYPE3_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
      if Aux_BType > 0 then
        pDataSet1.FieldByName('BOARDTYPE3').Value := Aux_BType;
    end;
  end;

  if pDataSetBDM.FieldByName('BPD_NUM').IsNull then begin
    if not pDataSetBDM.FieldByName('PAXQUANTITY').IsNull then begin
      if Aux_PType = 1 then begin
        pDataSet1.FieldByName('P_TYPE1_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
        if Aux_BType > 0 then
          pDataSet1.FieldByName('B_TYPE1').Value := Aux_BType;
      end;
      if Aux_PType = 2 then begin
        pDataSet1.FieldByName('P_TYPE2_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
        if Aux_BType > 0 then
          pDataSet1.FieldByName('B_TYPE2').Value := Aux_BType;
      end;
      if Aux_PType = 3 then begin
        pDataSet1.FieldByName('P_TYPE3_QUANT').Value := pDataSetBDM.FieldByName('PAXQUANTITY').Value;
        if Aux_BType > 0 then
          pDataSet1.FieldByName('B_TYPE3').Value := Aux_BType;
      end;
    end;
    pDataSet1.FieldByName('OPERATORDEBIT').Value := Aux_Operator_Debit;
  end
  else begin
    if not pDataSetBDM.FieldByName('BPD_P1Q').IsNull then begin
      pDataSet1.FieldByName('P_TYPE1_QUANT').Value := pDataSetBDM.FieldByName('BPD_P1Q').Value;
      if not pDataSetBDM.FieldByName('BPD_B1').IsNull then
        pDataSet1.FieldByName('B_TYPE1').Value := pDataSetBDM.FieldByName('BPD_B1').Value;
    end;
    if not pDataSetBDM.FieldByName('BPD_P2Q').IsNull then begin
      pDataSet1.FieldByName('P_TYPE2_QUANT').Value := pDataSetBDM.FieldByName('BPD_P2Q').Value;
      if not pDataSetBDM.FieldByName('BPD_B2').IsNull then
        pDataSet1.FieldByName('B_TYPE2').Value := pDataSetBDM.FieldByName('BPD_B2').Value;
    end;
    if not pDataSetBDM.FieldByName('BPD_P3Q').IsNull then begin
      pDataSet1.FieldByName('P_TYPE3_QUANT').Value := pDataSetBDM.FieldByName('BPD_P3Q').Value;
      if not pDataSetBDM.FieldByName('BPD_B3').IsNull then
        pDataSet1.FieldByName('B_TYPE3').Value := pDataSetBDM.FieldByName('BPD_B3').Value;
    end;
    pDataSet1.FieldByName('CREATETIMESTAMP').Value := pDataSetBDM.FieldByName('BPD_CTS').Value;
    pDataSet1.FieldByName('CREATEUSER').Value := pDataSetBDM.FieldByName('BPD_CUS').Value;
    pDataSet1.FieldByName('CREATEPOSITION').Value := pDataSetBDM.FieldByName('BPD_CPS').Value;
    if not pDataSetBDM.FieldByName('BPD_MTS').IsNull then begin
      pDataSet1.FieldByName('MODIFYTIMESTAMP').Value := pDataSetBDM.FieldByName('BPD_MTS').Value;
      pDataSet1.FieldByName('MODIFYUSER').Value := pDataSetBDM.FieldByName('BPD_MUS').Value;
      pDataSet1.FieldByName('MODIFYPOSITION').Value := pDataSetBDM.FieldByName('BPD_MPS').Value;
    end;
    if Aux_Operator_Debit = -1 then
      pDataSet1.FieldByName('OPERATORDEBIT').Value := pDataSetBDM.FieldByName('OPERATORDEBIT').Value
    else
      pDataSet1.FieldByName('OPERATORDEBIT').Value := Aux_Operator_Debit;
  end;
  pDataSet1.Post;
  pDataSetBDM.Next;
end;

... menudo tostón, no ???

Y estas son sólo 200 y poco líneas de las 1800 que tiene el procedimiento completo y que quiero optimizar con sentencias SQL en vez de bucles de programación, y además meterlo todo en un SP que se ejecute en el servidor, y que no tenga que haber tráfico de datos en la red para poder ejecutar el código necesario.

Probablemente así, logre optimizar un proceso que al pedir ahora un mes de datos, tarda unos 30 o 40 segundos, por todos los cálculos internos que debe de realizar, a conseguirlo en un 'abrir y cerrar de ojos'.

Ya lo he hecho con algún que otro proceso diferente también del programa. Lo que antes tardaba 10 - 15 segundos, ahora es inmediato .... 'ya ta !!'.

Con esto se puede observar que como había leido ya alguna vez en alguna parte .... que las sentencias SQL pueden ser todo lo complejas que el programador sea capaz de programar !

Ya pondré los resultados definitivos cuando esté todo hecho !