[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

How to increase performance when saving dataset

Hi,

When I save my dataset it takes far to long. (my database is growing and it gets worse)
After analysing, I discovered that the OnScroll-event is called very often. The code in the OnScroll-event is used to set formlayout dependent on data in current record (f.e. show/hide options).
Even when I disable the code in this event, it will still be called and therefor consume valuable time.
When I also need to refresh other (related) tables after a post, you can imagine that it will take seconds before a record is saved.

Can anybody give me some directions or an example on how to implement browsing and editting data with fast performance?
I'm using: Delphi 7 Pro, MS Access database, TADOTable, DevExpress

Thank you a lot for your help.
Regards,
Stef
0
Stef Merlijn
Asked:
Stef Merlijn
  • 6
  • 4
  • 2
4 Solutions
 
DragonSlayerCommented:
Try this?

with TADOTable1 do
try
  DisableControls;
  DoMySavingMethod;
finally
  EnableControls;
end;
0
 
Stef MerlijnDeveloperAuthor Commented:
Disabling/Enabling Controls makes it even worse.
0
 
Stef MerlijnDeveloperAuthor Commented:
In the afterpost of the table1 is also a Table1.Refresh.
When I ommit this, then I gain some performance.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DragonSlayerCommented:
Try showing your code?
0
 
Stef MerlijnDeveloperAuthor Commented:
Hi DragonSlayer,

Here are some of the events that will be accessed. The AfterPost event is taking up a few milliseconds till I excecute DM.TRelaties.Refresh.
Then it goes numerous times through OnCalcFields-event and OnScroll-event (probebly for each record in the dataset).
Though the code in these events aren't executed because variable "RelatieGewijzigd" is set to True in OnBeforePost-event.
When I ommit the line DM.TRelaties.Refresh, then the code executes 2 seconds faster (with only 65 records in TRelaties).

TOTAL            Description
00:00:00:000  Save-button pressed
00:00:00:937  TRelatiesAfterPost - Before Refresh
00:00:02:750  TRelatiesAfterPost - After Refresh     >> 2 seconds extra time used


procedure TDM.TRelatiesAfterPost(DataSet: TDataSet);
begin
  If (ProgrammaOnderdeel <> poRelatiesAfdrukken) then
  begin
    If (Programmaonderdeel=poRelatieDetails) OR (ProgrammaOnderdeel=poRelatieToevoegen) then
    begin
      If DM.TRelaties.FieldByName('Relatienr').AsInteger = DM.TInstellingen.FieldByName('VolgendeRelatienr').AsInteger then
      begin
        DM.TInstellingen.Edit;
        DM.TInstellingen.FieldByName('VolgendeRelatienr').AsInteger := DM.TInstellingen.FieldByName('VolgendeRelatienr').AsInteger + 1;
        DM.TInstellingen.Post;
      end;
      FRelatieDetails.EmailOpbouw;
      FRelatieDetails.Email2Opbouw;
      FRelatieDetails.WebsiteOpbouw;
      FRelatieDetails.OntkoppelBedrijfKnop;
    end;
    If (DM.TRelaties.Recordcount = 0) then
    begin
      FilterTekst := FRelaties.GridRelatiesView.DataController.Filter.Root.Criteria.FilterCaption;
      FilterTekst := OmzettenAmpersand3(FilterTekst);
      Application.ProcessMessages;
      If ProgrammaOnderdeel <> poRelaties then
        FHoofdscherm.OpenParentScherm(FRelaties,poRelaties);
      MyMessageDlg(Format(cxGetResourceString(blcMessageInformatie)
                       , [FHoofdscherm.goChecker1.ApplicationName])
                 , Format(cxGetResourceString(blcRelatiesIngesteldeFilterLeeg)
                       , [TijdelijkeString])
                 , dkInformatie, NMV);
      FRelaties.GridRelatiesView.DataController.Filter.Root.Clear;
    end;
    FHoofdscherm.HoofdmenuOptiesBijwerken;
    DM.TRelatieLookup.Requery;
    DM.TBedrijfsRelaties.Requery;

    FHoofdscherm.ToevoegenAanLogbestand('TRelatiesAfterPost - Before Refresh');
    DM.TRelaties.Refresh;
    FHoofdscherm.ToevoegenAanLogbestand('TRelatiesAfterPost - After Refresh');

    FRelaties.MenuRelatiesBijwerken;
    RelatieGewijzigd := False;          // Nodig om te voorkomen dat AfterScroll-event doorlopen wordt.
  end else
  begin
    DM.TRelaties.Refresh;
    RelatieGewijzigd := False;          // Nodig om te voorkomen dat AfterScroll-event doorlopen wordt.
  end;
end;


procedure TDM.TRelatiesCalcFields(DataSet: TDataSet);
begin
  If (Not RelatieGewijzigd) then
  begin
    If (ProgrammaOnderdeel = poRelatieDetails) OR (ProgrammaOnderdeel = poRelaties) then
    begin
      If DateToStr(DM.TRelaties.FieldByName('Geboortedatum').AsDateTime) <> '30-12-1899' then
        DM.TRelaties.FieldByName('Leeftijd').asInteger := YearsBetween(DateOf(Date),DateOf(DM.TRelaties.FieldbyName('Geboortedatum').asdatetime))
      else
        DM.TRelaties.FieldByName('Leeftijd').asInteger := 0;
      If DateToStr(DM.TRelaties.FieldByName('Trouwdatum').AsDateTime) <> '30-12-1899' then
        DM.TRelaties.FieldByName('Getrouwd').asInteger := YearsBetween(DateOf(Date),DateOf(DM.TRelaties.FieldbyName('Trouwdatum').asdatetime))
      else
        DM.TRelaties.FieldByName('Getrouwd').asInteger := 0;
    end;
  end;
end;

procedure TDM.TRelatiesAfterScroll(DataSet: TDataSet);
begin
  If (Not RelatieGewijzigd) then
  begin
    If (ProgrammaOnderdeel = poRelaties) then
    begin
      FRelaties.MenuRelatiesBijwerken;
    end;
    If (ProgrammaOnderdeel=poRelatieDetails) then
    begin
      FRelatieDetails.EmailOpbouw;
      FRelatieDetails.Email2Opbouw;
      FRelatieDetails.WebsiteOpbouw;
      FRelatieDetails.OntkoppelBedrijfKnop;
      FRelatieDetails.PasFotoAan;
      FRelaties.MenuRelatiesBijwerken;
    end;
  end;
end;

Hopefully you have something to go with now?
If you have any question please feel free to ask.

Regards,
Stef
0
 
DragonSlayerCommented:
*gasp!* I don't understand Dutch! ;-)

1. Remove Application.ProcessMessages
2. I don't know if your Requery statements are necessary?
3. If you do DisableControls before saving, and EnableControls at the end of AfterPost, would it help?
4. Did you update your Delphi to 7.1?
0
 
Stef MerlijnDeveloperAuthor Commented:
1. Did remove it but did result in slower performance
2. These requeries doesn't seems to be the bottleneck. After removal performance was more or less the same.
3. Resulted in worse performance
4. Yes, I do have update 7.1

So that leaves me with no real ideas to do something with.
Any other ideas are welcome.
0
 
sun4sundayCommented:
Instead of refreshing, what is the response when you are closing and opening table
sun4sunday
0
 
Stef MerlijnDeveloperAuthor Commented:
Then I gain about half a second.
0
 
sun4sundayCommented:
That is a good news. half a second !!!

>>When I save my dataset it takes far to long.
Do you have lot of indexes in your tables you post?
Generally: only keep the necessary indexes. If there are lot of indexes it will increase the performacne of query but it will increase the time to insert/edit/delete. This will more visible once your database keep on increasing.
Look into this area and may be you can save lot of things.
Also how is your database server/'database computer- stand alone PC' ??
 Is it powerful enough to take care of the situations? Also check for the Hard disk space is enough for the operation.
One more thing..still may be the Hard disk space will be there. But make sure that the allocated space for the database is enough.(this applicable for the 'real database server').


sun4sunday



 
0
 
Stef MerlijnDeveloperAuthor Commented:
Thank you all for your help.
I've done a lot of Tracing after pressing the Save-button. And I've discoverred that there were a few problemareas.
1. The post into table Instellingen resulted in lot's of processing on behalf of that table, which wasn't neccessary for table TRelaties.
2. The OnScroll-event which I thought I had bypassed, was executed after all, this resulted in additional timeloss. Same goes for the OnCalcFields-event.
3. I added DisableControls/EnableControles to my code.
I will split point with you.
0
 
DragonSlayerCommented:
Glad it helped you ^^
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now