Solved

procedure Speed up

Posted on 2009-06-27
52
675 Views
Last Modified: 2013-11-23
Hello,
       
          Please find the below code, It takes around 20 Minutes to perform, Which makes my customers to complain about speed. If you have Ideas to speed it up , I would appreciate that too much.

BDE Database Engine, Paradox Tables.
Procedure SpeedMeUp;

begin

  DummyTable.DisableControls; 

  Query1.SQL.Clear;

  Query1.SQL.Add('Select Some fields from some tables using join');

  Query1.Open; // Note there is OnCalcFields Event

  While not Query1.EOF do

  begin

     A := Query1.Fields[0].AsFLoat;

     B := Query1.Fields[5]. AsString ++ 'Hello';

     .

     .

     .

     DummyTable.AppendRecord([A,B, Query1.Fields[4].AsString]);

     Query1.Next;

  end;

  DummyTable.EnableControls; // Show Dummy Table in a DBGrid

end;
 

procedure TFormX.Query1CalcFields(DataSet: TDataSet);

begin

    Query1QQQ.AsFloat := Fun1(Cmbx1.Text,Query1N.AsString,Query1S.AsString,0,Date);
 

    Query1CCC.AsFloat := Fun2(Query1N.AsString,Query1S.AsString,1,0,Date); 

end;
 

// Now The  Problem  (I Think) is in Func1 and Func2
 

function Func1(S1, S2, S3 : String; K : Integer; DD : Date);

begin

    Query1.SQL.Add('select some fields from table1');

    Query1.Open;

    X1 := Query1.Fields[0].AsFloat;
 

    Query2.SQL.Add('select some fields from table2');

    Query2.Open;

    X2 := Query2.Fields[0].AsFloat;
 

    Query3.SQL.Add('select some fields from table3');

    Query3.Open;

    X3 := Query3.Fields[0].AsFloat;

    .

    .

    .

    .

    .

    Query50.SQL.Add('select some fields from table50');

    Query50.Open;

    X50 := Query50.Fields[0].AsFloat;
 

    Result := X1 + X2 (X3 * X5) + X6 - X7 ... + X50;

end;

{*****************************}

function Func2(S1, S2, : String; K,J : Integer; DD : Date);

begin

    Query1.SQL.Add('select something else than selected in Queries in Func1 from table1');

    Query1.Open;

    XX := Some Calculations; 

    WW := Some Calculations

     While not Query1.EOF do

     begin

        DummyTable.AppendRecord[Query1.Fields[0].AsString, XX, WW]);

        Query1.Next;

     end;
 

    Query2.SQL.Add('select something else than selected in Queries in Func1 from table2');

    Query2.Open;

    XX := Some Calculations; 

    WW := Some Calculations

     While not Query2.EOF do

     begin

        DummyTable.AppendRecord[Query2.Fields[0].AsString, XX, WW]); //same dummy table

        Query2.Next;

     end;
 

    

    Query3.SQL.Add('select something else than selected in Queries in Func1 from table3');

    Query3.Open;

    XX := Some Calculations; 

    WW := Some Calculations

     While not Query3.EOF do

     begin

        DummyTable.AppendRecord[Query3.Fields[0].AsString, XX, WW]); // same dummy table

        Query3.Next;

     end;
 

    .

    .

    .

    .

    .

    Query60.SQL.Add('select something else than selected in Queries in Func1 from table60');

    Query60.Open;

    XX := Some Calculations; 

    WW := Some Calculations

     While not Query60.EOF do

     begin

        DummyTable.AppendRecord[Query60.Fields[0].AsString, XX, WW]);

        Query60.Next;

     end;
 

    

    {*******}
 

    Result := Do some calculation on Dummy table;

   

end;

Open in new window

0
Comment
Question by:i7mad
  • 17
  • 11
  • 9
  • +2
52 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24727173
Is there any chance that you could do the calculations in the query instead of in the code?  In other words, instead of selecting a set of records to work on and then processing them one at a time, do the INSERT something like below?  
Also, the BDE has been deprecated for quite a while now and SQL Server Express is free and a lot more powerful.  Paradox has had known issues for a long time, although it does still work if it isn't heavily loaded. ;-)

INSERT INTO DummyTable

( 

Field[0],

field[1],

field[2]

)

SELECT field[0], 

       Some Calculations, 

       Some Calculations]

FROM   table1

WHERE whatever);

Open in new window

0
 
LVL 6

Expert Comment

by:FactorB
ID: 24727180
Of course it is slow. If you are using large tables or queries then you need to try to avoid loops (for, while, repeat, until) as much as you can. Simply do all calculations with SQL.
SQL example for Funct1:

Select Sum (Table_Sum) as Total_Table_Sum From (SELECT Sum(Field1) AS Table_Sum
FROM Table1 Union Select Sum(Field2)  AS Table_Sum From Table2);

Also make all While(s) disappear from the code (switch to SQL), you will notice instant gain in speed.

Regards,
B
0
 

Author Comment

by:i7mad
ID: 24727307

This is the real source code of Function1

any improvements should be done to speed it up?
function CalcQTY(PPPStore   : String;PPPNumber : String;PPPSource : String; WhatEx : Integer; Exclude : String; EndDate : TDate) : Real;

var

  GOrders, LOrders, First, LDInvoices, DInvoices, AdjustPlus, AdjustMinus, GroupMake, GroupMakeMinus, MI, TIN,TOUT : Real;

  SDate : String;

  CalcQTYQuery : TQuery;

begin

  SDate := DateToStr(EndDate);

  CalcQTYQuery := TQuery.Create(Form1);

  CalcQTYQuery.DatabaseName := 'MyDataBase';

  { Starting the job }

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "GlobalOrderList.db" d, "GlobalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '")  AND (D1."Date" <= "' + SDate + '") AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    GOrders := CalcQTYQuery.Fields[0].AsFloat

  else

    GOrders := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "LocalOrderList.db" d, "LocalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' +  PPPSource + '") AND (D1."Date" <= "' + SDate + '")  AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    LOrders := CalcQTYQuery.Fields[0].AsFloat

  else

    LOrders := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '")  AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "1.0")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    LDInvoices := CalcQTYQuery.Fields[0].AsFloat

  else

    LDInvoices := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(d.QTY) FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "0.0")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    DInvoices := CalcQTYQuery.Fields[0].AsFloat

  else

    DInvoices := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "Adjust.DB"  WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND ("Adjust.DB"."Date" <= "' + SDate + '")  AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND (QTY > "0")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    AdjustPlus := CalcQTYQuery.Fields[0].AsFloat

  else

    AdjustPlus := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "Adjust.DB"  WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND ("Adjust.DB"."Date" <= "' + SDate + '")  AND (QTY < "0")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    AdjustMinus := CalcQTYQuery.Fields[0].AsFloat * -1

  else

    AdjustMinus := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "FirstTime.DB"  WHERE (Number = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    First := CalcQTYQuery.Fields[0].AsFloat

  else

    First := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(D1.QTY) FROM "MI.db" d, "MI List.DB" D1 WHERE (d.Store = "' + PPPStore + '") AND (D1."MI Number" = d."MI Number") AND (D1."Part Number" = "' + PPPNumber + '") AND (D1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    MI := CalcQTYQuery.Fields[0].AsFloat

  else

    MI := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(d1.QTY) FROM "Move.db" d, "MoveList.db" d1 WHERE (d."Out Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    TOUT := CalcQTYQuery.Fields[0].AsFloat

  else

    TOUT := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(d1.QTY) FROM "Move.db" d, "MoveList.db" d1 WHERE (d."In Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    TIN := CalcQTYQuery.Fields[0].AsFloat

  else

    TIN := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(QTY) FROM "GroupMaker.DB"  WHERE ("GroupMaker.DB"."GroupNumber" = "' + PPPNumber + '") AND ("GroupMaker.DB"."Source" = "' + PPPSource + '") AND ("GroupMaker.DB"."Store" = "' + PPPStore + '") AND ("GroupMaker.DB"."Date" <= "' + SDate + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    GroupMake := CalcQTYQuery.Fields[0].AsFloat

  else

    GroupMake := 0;

  CalcQTYQuery.Close;

  CalcQTYQuery.SQL.Clear;

  CalcQTYQuery.SQL.Add('SELECT SUM(D.QTY) FROM "GroupMakerList.DB" D, "GroupMaker.DB" D1 WHERE (D."Part Number" = "' + PPPNumber + '") AND (D."Part Source" = "' + PPPSource + '") AND (D1.Number = D."Maker Number") AND (D1.Store = "' +  PPPStore + '") AND (D1."Date" <= "' + SDate + '")');

  CalcQTYQuery.Open;

  if not CalcQTYQuery.IsEmpty then

    GroupMakeMinus := CalcQTYQuery.Fields[0].AsFloat

  else

    GroupMakeMinus := 0;
 

CalcQTYQuery.Free;
 

  Result := (GOrders + LOrders + AdjustPlus + First + MI + TIN + GroupMake) - (LDInvoices + DInvoices + AdjustMinus + TOUT + GroupMakeMinus);

  

end;

Open in new window

0
 
LVL 6

Accepted Solution

by:
FactorB earned 200 total points
ID: 24727549
I don't have proper tables and forms and I was unable to compile this code. It is pretty simple SQL statement, so it should work. I am more concerned about string length. If receive error like sting too long, then just chop it into smaller strings:

function CalcQTY(PPPStore   : String;PPPNumber : String;PPPSource : String; WhatEx : Integer; Exclude : String; EndDate : TDate) : Real;
var
  SDate : String;
  CalcQTYQuery : TQuery;
ssql:string;
  begin

  SDate := DateToStr(EndDate);
  CalcQTYQuery := TQuery.Create(Form1);
  CalcQTYQuery.DatabaseName := 'MyDataBase';
  ssql:='Select (sum(calc_GOrders)+sum(calc_LOrders)+sum(calc_AdjustPlus)+sum(calc_First)+sum(calc_MI)+sum(calc_TIN)+sum(calc_GroupMake))-(sum(calc_LDInvoices) + sum(calc_DInvoices) + sum(calc_AdjustMinus) + sum(calc_TOUT) + sum(calc_GroupMakeMinus)) as Total_Table_Sum From (';
  ssql:=ssql+' SELECT SUM(d.QTY) as calc_GOrders, 0 as calc_LOrders, 0 as calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "GlobalOrderList.db" d, "GlobalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '")  AND (D1."Date" <= "' + SDate + '") AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '") UNION ';
  ssql:=ssql+' SELECT SUM(d.QTY) as 0 as calc_GOrders, calc_LOrders, 0 as calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "LocalOrderList.db" d, "LocalOrders.db" d1 WHERE (d."Part Number" = "' + PPPNumber + '") AND (d.Source = "' +  PPPSource + '") AND (D1."Date" <= "' + SDate + '")  AND (d1."Order Number" = d."Order Number") AND (d1."Store Number" = "' + PPPStore + '") UNION ';
    ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, SUM(d.QTY) as calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '")  AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "1.0") UNION ';
    ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices,SUM(d.QTY) as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "InvoicesList.db" d, "Invoices.DB" D1 WHERE (d.Number = "' + PPPNumber + '") AND (d.Source = "' + PPPSource + '") AND (D1."Date" <= "' + SDate + '") AND (D1.Number = d.INumber) AND (D1.Store = "' + PPPStore + '") AND (D1.ICase = "0.0") UNION ';
    ssql:=ssql+' SELECT  0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus,SUM(QTY) as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "Adjust.DB"  WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND ("Adjust.DB"."Date" <= "' + SDate + '")  AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND (QTY > "0") UNION';
      ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, SUM(QTY) as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "Adjust.DB"  WHERE ("Adjust.DB"."Part Number" = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") AND ("Adjust.DB"."Date" <= "' + SDate + '")  AND (QTY < "0") UNION';
      ssql:=ssql+' SELECT   0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, SUM(QTY) as calc_First, 0 as calc_MI,0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "FirstTime.DB"  WHERE (Number = "' + PPPNumber + '") AND (Source = "' + PPPSource + '") AND (Store = "' + PPPStore + '") UNION ';
      ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, SUM(D1.QTY) as calc_MI, 0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "MI.db" d, "MI List.DB" D1 WHERE (d.Store = "' + PPPStore + '") AND (D1."MI Number" = d."MI Number") AND (D1."Part Number" = "' + PPPNumber + '") AND (D1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '") UNION';
      ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI, SUM(d1.QTY) as calc_TOUT, 0 as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "Move.db" d, "MoveList.db" d1 WHERE (d."Out Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '") UNION ';
      ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI, 0 as calc_TOUT , SUM(d1.QTY) as calc_TIN,0 as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "Move.db" d, "MoveList.db" d1 WHERE (d."In Store" = "' + PPPStore + '") AND (d1."Move Number" = d.Number) AND (d1."Part Number" = "' + PPPNumber + '") AND (d1.Source = "' + PPPSource + '") AND (D."Date" <= "' + SDate + '") UNION';
      ssql:=ssql+' SELECT   0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI, 0 as calc_TOUT , 0 as calc_TIN, SUM(QTY) as calc_GroupMake, 0 as calc_GroupMakeMinus FROM "GroupMaker.DB"  WHERE ("GroupMaker.DB"."GroupNumber" = "' + PPPNumber + '") AND ("GroupMaker.DB"."Source" = "' + PPPSource + '") AND ("GroupMaker.DB"."Store" = "' + PPPStore + '") AND ("GroupMaker.DB"."Date" <= "' + SDate + '")';
      ssql:=ssql+' SELECT 0 as calc_GOrders, 0 as calc_LOrders, 0 as  calc_LDInvoices, 0 as calc_DInvoices, 0 as calc_AdjustPlus, 0 as calc_AdjustMinus, 0 as calc_First, 0 as calc_MI, 0 as calc_TOUT , 0 as calc_TIN,0 as calc_GroupMake, SUM(D.QTY) as calc_GroupMakeMinus FROM "GroupMakerList.DB" D, "GroupMaker.DB" D1 WHERE (D."Part Number" = "' + PPPNumber + '") AND (D."Part Source" = "' + PPPSource + '") AND (D1.Number = D."Maker Number") AND (D1.Store = "' +  PPPStore + '") AND (D1."Date" <= "' + SDate + '") ';
  ssql:=ssql+' );'
  { Starting the job }
  CalcQTYQuery.Close;
  CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add(ssql);
  CalcQTYQuery.Open;
  Result:=CalcQTYQuery.FieldByName['Total_Table_Sum'].AsFloat;
  CalcQTYQuery.Free;
  end;
 
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24727553
Anyway, consider switching to some kind of database server. I guess that you plan, if you have spare time it will take you a week of reading and a week of coding.

Regards,
B.
0
 

Author Comment

by:i7mad
ID: 24727599
I upgraded to Absolute Database, so now I have two copies of my App.

one using BDE, Paradox
another using Absolute Database

 Guess what?

as for the slow function; nearly same execution time for both databases (paradox & absolute)

BDE Paradox:  21 Minutes , 31 Seconds
Absoulte Database : 22 Minutes , 17 Seconds

 okay

 I used ImMemory Tables for both functions in Absolute Database copy of my Application, and all functions and tables I used in this calculations changed to Inmemory Tables and queries, imagine the result??

 a slight performance improvment for Absolute Database

BDE Paradox:  20 Minutes , 43 Seconds
Absoulte Database : 19 Minutes , 22 Seconds
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24727628
I know Absolute Database is not too fast, but evidently has it's advantages. I used to get speed increase in it when I deleted excessive indexes on the table. But anyway I am surprised I expected ABSDB to be significantly faster than Paradox. It can also mean that there is a slow part in the code. If above function is working then try to get rid of the others While and For (s) messing anywhere where messing with the queries.

B.
0
 
LVL 22

Expert Comment

by:senad
ID: 24727663
remove this :

DummyTable.DisableControls;
 DummyTable.EnableControls;
0
 

Author Comment

by:i7mad
ID: 24727674

senad: removed and same performance

   Actualy i just used them yesterday only, before that I didn't call these methods and same slow performance
0
 

Author Comment

by:i7mad
ID: 24727682

FactorB

  What you suggest for a database which migration from BDE will not take much coding?
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24727685
Enabled controls update screen and waste processor power. Looks better as it is. Theoretically I am right, practically need to be tested.
0
 
LVL 22

Expert Comment

by:senad
ID: 24727713
How about changing field type from AsFloat to Integer ??
0
 

Author Comment

by:i7mad
ID: 24727717

fields should be a floating values, because it is Items costs
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24727751
Rtwoolf wrote the best review about migrating from Paradox, it was really long and substantial. The end point was it you make something for a smaller market consider Firebird or Postgre, I would add Firebird over Postgre as smaller footprint and really light, plus there is embed version that works with dll and without classic server. Postgre over Firebird as more mature, feature full and better documented...  If I was choosing, I would take Firebird. I simply like them. :)
0
 
LVL 22

Expert Comment

by:senad
ID: 24727754
Integer (small integer) should do just fine.
If they are item costs then it is perhaps best to change the field type to currency.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24728032
Note: As a passing comment, your column name selections are less than
          desirable in that they include spaces and one of them ("Date") is a reserved
          word.  This will cause you now end of grief and additional maintenance
          issues.
Question:
          Is there an index on each of the tables on the columns used in the WHERE
          clause (except for the cases or qty columns) preferably in the order that they
          appear in the WHERE clause?

Passing observation:
          You can change the code so that you don't do more work than necessary by
          changing:
CalcQTYQuery.SQL.Clear;
  CalcQTYQuery.SQL.Add(

to  

CalcQTYQuery.SQL.Text := (
Also, if you set up the queries as Constants with %s where the parameters are, you can use a statment like
CalcQTYQuery.SQL.Text := FORMAT(YourSQLConstant, [PPPNumber, SDate, etc.]);
If this were a SQL Server database, I would recommend using a stored prcoedure to accomplish these calculations and, in fact, I would probably feel comfortable in refactoring your SQL so that all of the calculations are, effectively, accomplished in significantly fewer (if not a single) SQL statements.  However, my memories of Paradox are that complex SQL chokes, so I am leary of refactoring the SQL.
0
 

Author Comment

by:i7mad
ID: 24728681

8080_Diver: answering the question: most of columns appear in the WHERE clause are not indexes BUT some of them such as Master-Detail tables like in:

(d1."Order Number" = d."Order Number")

d1."Order Number" is an index in d1 Table.

Do you mean that having field names with spaces or a keywords makes the Query slow to execute?
0
 
LVL 22

Expert Comment

by:senad
ID: 24728750
field names should not contain spaces.
kewords are also prohibited.
0
 

Author Comment

by:i7mad
ID: 24728756

yes senad, but after all, they are working just put the field name between " " and it will work
0
 
LVL 22

Expert Comment

by:senad
ID: 24729312
you wouldn't beleive what is 'supposed' to work and why it 'doesn't work'...
I had nothing but trouble with quotes so I try to avoid them.
:-)
0
 

Author Comment

by:i7mad
ID: 24730598
Why I got this error here?


SQLError.jpg
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24742305
either you should give a name to the subselect or it can't do subselects

select X from (select X, Y, Z from table) as A

note the as A

if you want performance then Absolute is definetly not the way to go

you queries would take 0.2 sec on MSSQL or Oracle ...
That's what you get with a server database, it's designed for speed !

Absolute is designed for embedding, not speed
0
 
LVL 22

Expert Comment

by:senad
ID: 24742353
remove the first 'from' and 'select':
SELECT XXX,XXX,XXX FROM XXX ORDER BY XXX,XXX,XXX;

0
 
LVL 22

Expert Comment

by:senad
ID: 24742360
don't agree with you geert,absolute is very fast too ....
0
 

Author Comment

by:i7mad
ID: 24742658
Greet so you don't agree that the code is also slow? same code will take 0.2 sec on MS SQL or Oracle?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24743214
senad, o ?
would you equip a desktop pc with
the processing power of a server with 4 cpu and ultra fast scsi drives ?

absolute is limited by the money you spend on a the desktop pc, or a notebook
in other words slow drive, and 1 processor

the network is the bottleneck with client/server



0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24743277
you wouldn't do it like this with sql server or oracle.

you would write a stored procedure which does all the queries on the server

the way you do this in the procedure is a other scope
seems like you want the sum of the qty column over several tables

you just need to call the function to get the result
using a storedproc component

or use a snapshot (materialized view) to select from
the materialized view gets rebuilt when a insert/update/delete happens
a select is instantanious

ah, but it doesn't have procedures and functions
and neither materialized views

so you are very limited for tuning options
create function totalsum return number is 

  temp number(53,20);

begin

  select sum(calc_GOrders)+sum(calc_LOrders)+sum(calc_AdjustPlus)+sum(calc_First)+sum(calc_MI)+sum(calc_TIN)+sum(calc_GroupMake))-(sum(calc_LDInvoices) + sum(calc_DInvoices) + sum(calc_AdjustMinus) + sum(calc_TOUT) + sum(calc_GroupMakeMinus)) as Total_Table_Sum 

    into temp

  From 

  -- rest of query from FactorB
 

  return temp;

end;

Open in new window

0
 
LVL 6

Expert Comment

by:FactorB
ID: 24743360
I agree that Absolute has nice features and is pretty SQL-92 compliant, but is also slow, maybe faster from some other file-system databases (this is where most people are impressed when switching from Paradox to Absolute for example), but no close to a speed of a server. Sorry to say that, but server drives in tenth gear compared to old school databases (no offenses I still like and use them).
0
 

Author Comment

by:i7mad
ID: 24743681
Well guys,

 even if my Application is a desktop application and no need for server database, SO using a server database for a desktop application, will make a huge difference in performance?

if yes, even on a single proccessor pc?
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 200 total points
ID: 24743706
i have run into some limitations for speed now and then ...

1 solution for your problem:

you need to run a calculation which delivers a result for sum of some tables
when you update/insert/delete column qty from one of the records
at the end of the batch start a thread for calculation
this calculation then updates the total in a extra tabel for calculations

then just select from this extra table

it will off course have to be inside your exe

for a server database this can be a stored proc executed by the database exe
or a separate process doing all the calculations outside of your exe

there is a lot of restrictions when having all contained into 1 file ...

it would like having all of windows functionality into 1 exe
my idea -> a nice dream, but will allways be a dream

absolute may have come close to the dream but at what cost ?


0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24743713
besides all of this, have you ever considered what would happen if
some customer were to ask for a restore of the data from last week
and you had allready altered your exe ...
and forgot to take a backup of the exe then
0
 

Author Comment

by:i7mad
ID: 24743802
what Data has to do with exe?

backup and restore is a database engine issue

I know paradox doesnt have data backup option
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24743935
To backup/restore Paradox, I used to disconnect database and then copy from and to all paradox related files.

Extensions listed from:
http://www.delphi32.com/info_facts/faq/faq_89.asp

.DB       Paradox table?
.FAM       listing of related files (like a table's .TV file)?
.LCK      Lock file?
.MB       Blob file?
.PX       Primary index?
.TV       Table view settings (not used by BDE)?
.VAL       Validity checks and referential integrity?
.Xnn       Secondary single-field index, numbered?
.Ynn       Secondary single-field index, numbered?
.XGn       Composite secondary index ?
.YGn       Composite secondary index ?

Maybe there is another method ...
0
 

Author Comment

by:i7mad
ID: 24744014
if it is like this, then make your data files and Folder1 and when need a back copy the whole folder to a backup folder and use a good naming method to name a back folders with date, or use ini files

and copy backup folder to data folder when a restore needed.
0
 

Author Comment

by:i7mad
ID: 24744032
is there an exact answer for this

Using a server database engine such as MSSQL , Oracle, MySQL for a desktop application, will make a huge difference in performance? even if my application using single-user mode only?

if yes, even on a single proccessor pc?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24744073
yes, there will be better performance
especially when taking into account stored procedures, triggers, snapshots etc

with ordinary flat tables you need to move the data from the database to the exe, then process

with a server database some tables may be in cache (memory) thus skipping this step

do you want to focus on writing a fast app or writing code to process the data fast ?
it looks like you have a problem with processing the data fast, because of the limited capabilities of absolute
this is allready a specialisation of server databases, so why reinvent the wheel

i'm not saying absolute is bad, wrong or slow for what it is designed for
i think you have actually bumped into the limitations of absolute and you obviously need more than they can offer
so i would suggest: take it to the next level (or 2 levels)

a stress test usually points this out

your question should be:
can my program/database process my requests for data/processing at the speed i want on a given system
did you write a test for this for absolute ?
if not, then you just ran into the failure of such a test
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24744091
Yes, checked with Absolute DB/Firebird (2.2 GHz Pentium IV 1 GB of RAM), plus Firebird was coded with stored procedures that gave extra speed. Just don't use embedded version of Firebird as I read somewhere that is slower than Classic or Super Server.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24744130
just look at it like this (with rough maths):
on a windows system every exe gets a slice of cpu time based on priority

lets say we have on your system 20 exe running, equally divided is 1/20 per sec of CPU time

now you have 1 exe to do all so that's 1/20 of a sec

now change that to 1 server database (1 service) + your exe
that's allready 2/20 of cpu time per sec (= 2 times as fast)

and normally a server database gets higher priority so 3/20 of cpu time per sec



0
 

Author Comment

by:i7mad
ID: 24744169


<<<
Geert_Gruwez:

<<<with ordinary flat tables you need to move the data from the database to the exe, then process>>>

  And the same when using Delphi with Direct Access components to Oracle for example? or proccessing data with oracle enging will be on Database executable?

<<<do you want to focus on writing a fast app or writing code to process the data fast ?>>>

  A code to proccess the data fast.. But when I asked this question , I got from answeres here that I have two problems:
1- database engine I use is slow
2- my code accessing the data and doing calculation is also slow (because Absolute database give slower result than BDE , Paradox)

I have two copies of my application now, one using BDE, Paradox, and the other is Absolute databse, and both are slow, BUT Absolute database is slower by 30% for the above procedure. thats why ppl here told me something is slow also in your code.


<<<a stress test usually points this out
your question should be:
can my program/database process my requests for data/processing at the speed i want on a given system
did you write a test for this for absolute ?
if not, then you just ran into the failure of such a test>>>

  I considered my slow procedure as a stress test, so Paradox and Absolute faild :/
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 100 total points
ID: 24746382
Have you considered SQL Server Express (which is free and distributable).  You can put the code in a stored proc in SSExpress if you want.
I have to confess that I took one look at the code that you posted and then didn't look any more to see if I could make it more efficient.  Why?  Because it was virtually unreadable, as far as I am concerned.  It too closely resembled what happens to my SQL code when I use it to generate a view in most databases . . . the formatting of the code, or lack there of, makes it hard to read.  There are probably improvements that could be made to the code (there usually are, even to mine ;-) but I would have to copyit and reformat it before I could begin to analyze it and, I'm sorry, I just don't have the time.
When I asked about the indexes and the columns in your where clause, that was because you may be causing the SQL to not use any index (and, instead, simply scan the whole table) because of the complexity of your where clause and the sparcity of your index usage.  That could easily result in your 30% increase in time rquired with the database you chose over the BDE (the BDE was not terribly sophisticated and one could control performance, to some degree, by the order in which you presented the joins and the WHERE clause constraints ;-).  
With a more sophisticated database (e.g. SSExpress) you could use more sophisticated SQL statements and move much of the calculation effort into the SQL statements.  The more you can minimize touching each record, the faster your processing will be accomplished.
 
0
 

Author Comment

by:i7mad
ID: 24747631

well 8080 driver, what about Oracle XE then? free and limited to 4GB, 1 processor, 1024 RAM, same as SSExpress.

the migration should be done anyway, so why not choosing #1 database engine !
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24747704
@i7mad,
Obviously presented from a person with Oracle experience. ;-)  Since I haven't worked with Oracle for some time now (last time was in about 2000 ;-), I am not up on the Oracle offerings, so I offered what I do know. ;-)  I suppose I could throw Teradata into the mix as well because I know that there is a "Demo" version available and it can do some really serious stuff . . . I rather enjoyed working with Teradata. ;-)
In any case, there are free versions of stronger databases out there and moving to one (almost any one of them will do) will provide enhanced performance . . . assuming that the database is designed well and the SQL, Indexes, SP's, etc., are well designed.
0
 

Author Closing Comment

by:i7mad
ID: 31597226
Thanks guys! you were very helpful
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24748998
the XE is a magnifisant free database and nearly no limitations (yeah, the 4Gb, but for a desktop app, this is more than sufficient)
i use it on my notebook
at work, i have the full oracle, the same queries work

great tool
0
 
LVL 22

Expert Comment

by:senad
ID: 24749066
nice answer....(it was an answer,right?)
however for desktop DB nothing compares to Absolute database.
access is also good choice.
sql server is used by those who need a server.Not desktop database.
Oracle ?
In my opinion (just my opinion) is a lousy database.
For the reason that you must take time (and I mean a lot's of time) to learn it.
And in the end you find out that the SQL server from MS is just as good only taking you 20% of the time you need to get worked up with Oracle.Oracle is for masochists who like to use F keys to get an orgasm.Dr. Freud would label is as an obsessive need to press on mothers tit.
On work we use Oracle enterprise...I can tell you first hand SQL 2008 MS outfarts it all the way...
F... the database where you are required to go to school to mess with it.
I did try all and I am telling you my experience.Nothing I cant do on MS SQL 2008 that I can do on Oracle.Oracle is ripe for junkyard in my opinion.Sales tell the tale.Well see in 5 years time the market shares...
Please don't debate this as this is just my opinion...


0
 
LVL 22

Expert Comment

by:senad
ID: 24749089
...Oracle  will follow Paradox fate...
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24749413
@senad,
sql server is used by those who need a server.Not desktop database
Have you worked with SS2005Express or SS2008Express?  They far exceed Access as a desktop database.  (I use Access for prototyping but then convert to something else for real use.)
As for Oracle being a lousy databse . . . well, I have worked with Oracle and many others and, in the end, they all have their strengths and their weaknesses.  In some cases, you need a lot of training to get started but that usually means that you have enough training so that you don't screw things up as much.  On some  (e.g. Access), virtually anyone can start doing database work . . . and they do, with the end result that people create these god-awful "databases" that are not normalized, have weird columns as indexes, and are a pain in the a** to work with because they don't have any knowledge about what a relational database actually is.
Condemning a database engine out of hand is like saying that being or not being a vegetarian is the only way to go . . . it is a sign of a religious attitude toward or a religious bias against a database.  Beware of religious fanatics who would constrain your choices based upon their beliefs! ;-)
0
 
LVL 22

Expert Comment

by:senad
ID: 24750965
I have worked with 2005 and must agree with you.Only thing is that it is a server database.I am about to test the 2008 one now.Just undecided weather to use developer or the free one....On the other hand Access is contrary to what many think a very good database indeed.A surprisingly good one.It lacks many things of course but the JET engine rocks.
As for Oracle I do not think it is a lousy database in terms of the performance or the security.
What bothers me is that you really have to do some hard study before you can effectively use it.And I mean some real hard paper work.However Oracle has also some beautiful tutorials to get you going but that takes time.Also the SQL needs revisiting too.As for the database engine itself,Oracle rocks when it comes to large databases and more than 600 PC's connected.But judging from what I have seen in the SQL 2008 Microsoft has dangerously narrowed the gap.The only thing now is to see which will bite the market share more.SQL 2008 is much cheaper so that is going to be an important factor.
For the desktop database in my opinion Absolute database is the number 1 choice.Really good database.As for religious freaks,I am not that kind of a fanatic.I do test the stuff.
My biggest fault is that I expect programs to be at least 50 % intuitive to use.I hate manuals.I hate manuals for the video recorders,TV's...you name it.If I must use a manual to get something going then ...That is actually my only bias towards Oracle.Silly I know,not worthy of Oracle 'reputation' but that is how I feel.I just don't feel like learning i.e reinventing the wheel.Others can use whatever they like of course...Free choice,ha,ha...
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24754246
@senad,
I have worked with 2005 and must agree with you.Only thing is that it is a server database.
Read my responses again carefully.  SQL Server 2005/2008 Express is NOT a server database.  Yes, SQL Server 2005/2008 Standard/Developer/Enterprise are server databases but not the Express editions.
The SQL Server Express editions, as you put it, rock as local databases and far exceed the power of Access.  The only things Access really has going for it, IMHO, as far as a (semi-)proffesional developer doing work are:
  • It has a decent reporting capability built in;
  • It has a reasonable forms development capability built in;
  • It has a nice database diagramming tool.
However, SQL Server Express has:
  • A nicer diagramming tool because, although it looks pretty much the same, you can create multiple diagrams, one each for sections of the database;
  • Stored Procs;
  • UDFs;
  • Much stronger SQL capabilities;
  • Standard datatypes;
  • The ability to work with its Big Brother SQL Server databases for replication, etc.;
  • Geographic, Date, and Time datatypes, if you go with SS2008Express.
As for needing to be intuitive rather than needing to use the manual . . . well, IMHO, the "intuitive" nature of VB and Access get more people introuble and leads too many people to think that getting something working is the same as designing and developing a good product.  I have worked with too many "self taught" "programmers" who taught themselves extremely bad habits while working in VB and with Access.  I have also seen some real garbage code and databases that were created by these people who have worked with "intuitive" products and never read manuals or learned about application and database design.
Intuitive works great for a VCR or DVD . . . but it is, IMHO, extremely questionable as a design criteria for professional development tools such as databases, programming languages, and, even, data transmission tools.  If you learn from an intuitive tool, you may think like the man who only has a hammer . . . to a man with a hammer, every problem looks like a nail whether it is or not.  Unless you go to the manuals and textbooks, you may try to design all databases the same way and an OLAP, OLTP, and an embedded database are all very different animals.
0
 

Author Comment

by:i7mad
ID: 24754319
what about oracle XE? is it a server database?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24754409
@i7mad,
I don't know for sure . . . but I would think that it probably is not.  
Neither is the Teradata Demo Database. ;-)  Oh, yeah, by the way, the CD that the Teradata Demo Database comes on has the coolest query tool!  It is my Weapon of Choice for ad hoc queries because it saves teh query, the named connection, the success/failure status of the query, the number of rows, the run date and run time, the execution time, the user ID, the connection type, the DBMS time, the fetch time, AND lets you enter some notes about the query.  (All of that is saved in a nice little Access database, by the way, which means that you can link to the table(s) and do some reports and such ;-).  It also lets you search those columns (which is handy of you remember accessing a table in one version of an ad hoc query but you can't remember when that was . . . you can search for the table name and find all the queries in the database that have accessed it ;-).
That query tool lets me switch between projects in order to fight a "fire" and then I can get back to where I was before the fire alarm.
0
 
LVL 22

Expert Comment

by:senad
ID: 24755476
well,express editions run sql server service so in my opinion they are server databases (on PC's).On a single PC they behave like server databases.
perhaps you mean 'running on the sever itself' ? well that's  true.They are not built for server structure...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now