Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: D10, FireDAC, MySQL


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 0 Threads: [ Previous | Next ]
Jim Sawyer

Posts: 214
Registered: 1/3/10
D10, FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 11, 2017 6:57 AM
I am converting from Delphi 7, VFoxPro to Delphi 10, MySQL. I have converted a set of FoxPro data to
MySQL data and am using it as I develop the MySQL product. I have developed the following routine
to prepare data for a report and it takes about 5 minutes to run compared to about 20 seconds with
the old FoxPro. Can someone take a look at the following routine and tell me how to make it faster?
If you need additional information, please ask.

  dStart := Date - 100;
  dEnd := Date + DataMod.qMaster.FieldValues['Mind1Days'];
  dEndShort := Date + DataMod.qMaster.FieldValues['Mind1Days'] + 7;
  dEndLong := Date + DataMod.qMaster.FieldValues['Mind1Days'] + 31;
  AConnection.ExecSQL( 'Update Minder set MindSel = false' );
  AConnection.ExecSQL( 'Update Patient set MindSel = false' );
  AConnection.ExecSQL( 'Update Client set MindSel = false' );
  patQuery := TFDQuery.Create( AConnection );
  DataMod.qMaster.Open();
  with patQuery do
  begin
    Connection := AConnection;
    SQL.Add( 'Select Patient.Patno, Patient.Clino, Patient.Patient, Minder.Service, Minder.Remno, Minder.Due, Minder.Minded, Patient.Active, Patient.Health ' );
    SQL.Add( 'from Patient join Minder on Patient.Patno = Minder.Patno ' );
    SQL.Add( 'where Patient.Active = true and Patient.Health = ''A'' and Minder.Due between :S and :E and Minder.Minded < 1' );
    SQL.Add( 'order by Patient.Patient' );
    ParamByName('S').AsDate := dStart;
    ParamByName('E').AsDate := dEnd;
    Open;
    while not EOF do
    begin
      AConnection.ExecSQL( 'Update Patient set MindSel = true where Patno = :P', [patQuery.FieldValues['Patno']] );
      AConnection.ExecSQL( 'Update Client set MindSel = true where Clino = :C', [patQuery.FieldValues['Clino']] );
      AConnection.ExecSQL( 'Update Minder set MindSel = true where MindSel = false and Minded < 1 and Patno = :P and Period > 6 and Due between :S and :d',
        [patQuery.FieldValues['Patno'], dStart, dEndLong] );
      AConnection.ExecSQL( 'Update Minder set MindSel = true where MindSel = false and Minded < 1 and Patno = :P and Period < 6 and Due between :S and :d',
        [patQuery.FieldValues['Patno'], dStart, dEndShort] );
       Next;
    end;
  end;


Thanks,
Jim Sawyer
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02