Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Question on Performance Improvements to Code


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


Permlink Replies: 2 - Last Post: Oct 27, 2014 5:19 PM Last Post By: Mark Moss
Mark Moss

Posts: 7
Registered: 9/20/99
Question on Performance Improvements to Code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2014 1:42 PM
Help

I am working on upgrading a Delphi5 Application to Delphi7. When this is done I will then upgrade to Delphi2007, but one step at a time.

I have come across routines that are coded as follows, and I would like to rewrite them to get better performance. If you would compare the two and give your comments I would greatly appreciate it.

Existing Code

procedure TdtmMainUtils.SwitchWorkFlowStates(ApplicantId: Integer; ApplicationId: integer);
var
tmpSql: TQuery;
Current: String;
Previous: String;
begin
tmpSql:=TQuery.Create(self);
try
tmpSql.DatabaseName := UDodmerbDefinitions.Database_Name;
tmpSql.SessionName := dmMaster.seDBSS.SessionName;
tmpSQL.RequestLive := True;
.
tmpSQL.CachedUpdates := True;
tmpSql.SQL.Add('SELECT WORKFLOW_STATE, PREVIOUS_STATE, APPLICANT_ID, APPLICATION_ID ');
tmpSql.SQL.Add('FROM APPLICATION');
tmpSql.SQL.Add('WHERE APPLICANT_ID = ' + IntToStr(ApplicantID));
tmpSql.SQL.Add('AND APPLICATION_ID = ' + IntToStr(ApplicationID));
tmpSQL.open;
if ( not ( tmpSQL.eof ) ) then begin
Current := tmpSQL.FieldByName('workflow_state').AsString;
Previous := tmpSQL.FieldByName('previous_state').AsString;

tmpSQL.Edit;
dmMaster.dbDbssProduction.StartTransaction;

tmpSQL.FieldByName('workflow_state').AsString := Previous;
tmpSQL.FieldByName('previous_state').AsString := Current;

try
//save the cached changes to memory. newvalues
tmpSQL.Post;

// This writes the newvalues to the database, but not commited
tmpSQL.ApplyUpdates;

// HERE is where the data in the transaction is applied to the database.
dmMaster.dbDbssProduction.Commit;

// Update the tfields. This sets the oldvalues = newvalues
tmpSQL.CommitUpdates;
except
dmMaster.dbDbssProduction.Rollback;
end;
end;
finally
tmpSQL.close;
tmpSql.Free;
end;

end;

Proposed Code

procedure TdtmMainUtils.SwitchWorkFlowStates(ApplicantId: Integer; ApplicationId: integer);
var
tmpSql: TQuery;
Current: String;
Previous: String;
begin
tmpSql:=TQuery.Create(self);

try
tmpSql.DatabaseName := UDodmerbDefinitions.Database_Name;
tmpSql.SessionName := dmMaster.seDBSS.SessionName;

tmpSql.SQL.Add('UPDATE APPLICATION ');
tmpSql.SQL.Add('SET WORKFLOW_STATE = PREVIOUS_STATE, ' );
tmpSql.SQL.Add(' PREVIOUS_STATE = WORKFLOW_STATE ' );
tmpSql.SQL.Add('WHERE APPLICANT_ID = ' + IntToStr(ApplicantID));
tmpSql.SQL.Add('AND APPLICATION_ID = ' + IntToStr(ApplicationID));

tmpSQL.ExecSQL;

finally
tmpSQL.close;
tmpSql.Free;
end;


Any help would be greatly appreciated

Wayne Niddery

Posts: 791
Registered: 4/14/98
Re: Question on Performance Improvements to Code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2014 2:50 PM   in response to: Mark Moss in response to: Mark Moss
"Mark Moss" wrote in message news:699660 at forums dot embarcadero dot com...
Proposed Code

procedure TdtmMainUtils.SwitchWorkFlowStates(ApplicantId: Integer;
ApplicationId: integer);
var
tmpSql: TQuery;
begin
tmpSql:=TQuery.Create(self);

try
tmpSql.DatabaseName := UDodmerbDefinitions.Database_Name;
tmpSql.SessionName := dmMaster.seDBSS.SessionName;

dmMaster.dbDbssProduction.StartTransaction;

tmpSql.SQL.Add('UPDATE APPLICATION ');
tmpSql.SQL.Add('SET WORKFLOW_STATE = PREVIOUS_STATE, ' );
tmpSql.SQL.Add(' PREVIOUS_STATE = WORKFLOW_STATE ' );
tmpSql.SQL.Add('WHERE APPLICANT_ID = ' + IntToStr(ApplicantID));
tmpSql.SQL.Add('AND APPLICATION_ID = ' + IntToStr(ApplicationID));

tmpSQL.ExecSQL;

dmMaster.dbDbssProduction.Commit;

finally
tmpSQL.close;
tmpSql.Free;
end;

With the addition of the explicit transaction start and end (I always advise
this even for the BDE), this is exactly how it should have always been done.

As to using the BDE, it has been deprecated for many years already,
especially for use against SQL databases, it has problems with installation
on later versions of Windows and will never work with 64 bit software. If
your app needs to continue being maintained for some years then you would be
well advised to find a way off the BDE. There are other choices that can be
made that allow for minimal pain in converting away from the BDE. If you are
using Interbase then the least painless way to go would likely be the IBX
components.

I also have to ask: what value do you see in first migrating from Delphi 5
to Delphi 7 if you are then going to go to Delphi 2007 anyway? Why not just
go straight to D2007?

I also assume you already have a copy of D2007 but not a later version,
hence why that is a target, otherwise the same question applies as to why
you would not migrate to the latest version of Delphi. If the answer to that
is unicode, it is almost certainly a manageable issue - possibly in fact
requiring little to no change in your application (have you already
investigated this and found otherwise? If so, what issues?).

--
Wayne Niddery
"You know what they call alternative medicine that has been proven to work?
Medicine." - Tim Minchin

Mark Moss

Posts: 7
Registered: 9/20/99
Re: Question on Performance Improvements to Code  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2014 5:19 PM   in response to: Wayne Niddery in response to: Wayne Niddery
Wayne Niddery

I want to thank you so very much for your response to my question.

Now let me explain, the application that I am working on Started out in 1996 under Delphi2, it was then completed under Delphi3 in 1997, upgraded to Delphi4 in 1998 and finnaly to Delphi5 in 1999 and was last compiled in 2003 also under Delphi5.

Since 2007 there have been three different attempts to replace this one of a kind application, and three different failures. I originally worked on the codebase in 2010 but the powers that be would not implement the code changes that were made due to the impending release of the newest replacement software ( which we now know failed ). The Client currently owns several copies of Delphi7 so I am trying to upgrade the software to that version. As with most of these application there are Components that were used but they did not get the source code to them and the companies no longer exists.

In my expereance in using Delphi since version 1, its been best to upgrade preDelphi7 applications to Delphi7 and get them stabilized. Then upgrade to Delphi2007 (last nonunicode version of Delphi), which can be as simple as a recompile or as complicated as finding component vendors that still exists and upgrading all of the calls to there components which have changed over time.

Then there is the problem of the BDE as you alluded to in your comments. All of the BDE components will have to be changed out to use most likely Oracle or ADO components.

Again thanks for your help.

Mark Moss

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02