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

x
?
Solved

How to speed up OnCalcFields containing lookups

Posted on 2006-05-30
8
Medium Priority
?
862 Views
Last Modified: 2008-02-01
Hi all,

I have a (simplified) table setup like this:

TABLE A
  ID
  SIZE
  VAL1
  VAL2

TABLE B
  ID
  VALUE

TABLE C
  ID
  VALUE

where A.VAL1 and A.VAL2 are lookups into B and C, respectively.

Now in my 3-tier app, I have a TClientDataSet for each table and A should
also contain two calculated fields CALC1 and CALC2.
Expressed as Pseudo-SQL those fields should have the following values:
CALC1 = A.SIZE * B.VALUE where A.VAL1=B.ID
CALC2 = A.SIZE * C.VALUE where A.VAL2=C.ID

So, for my TClientDataSet cdsTableA I wrote the following OnCalcFields (also somewhat simplified):

procedure OnCalcFields( DataSet: TDataSet );
begin
  with DataSet do
  begin
    cdsTableB.Locate( 'ID', FieldValues['VAL1'], [] );
    FieldValues['CALC1'] := cdsTableB.FieldValues['VALUE'] * FieldValues['SIZE'];

    cdsTableC.Locate( 'ID', FieldValues['VAL2'], [] );
    FieldValues['CALC2'] := cdsTableC.FieldValues['VALUE'] * FieldValues['SIZE'];
  end;
end;

So far this works quite fine for few records, but as soon as TABLE A contains about
6000 records, calculating those values for all records is painfully slow.
And I have to show all records, due to application requirements, so there is no way
to execute OnCalcFields only for a subset of all records at once.

It's definitely the Locate() operations that take so much time, when using static values
it gets lightning fast.
Is there any way to speed this up considerably or should I take a completely other approach here?
I thought about including the lookup values in the query for cdsTableA in the
first place, but that gets ugly when it comes to more than 2 lookup values (and I have more than 2).
Also, the users wouldn't see new calculated values until they applied their changes to the database.

thx in advance
Wyverex
0
Comment
Question by:Wyverex
8 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 16790128
Is there no way you can just change your query instead behind the scenes to add another column calculated?

SELECT A.ID, A.SIZE, (A.SIZE*B.VALUE) AS CALC1, (A.SIZE*C.VALUE) AS CALC2
FROM A
INNER JOIN B ON A.VAL1 = B.ID
INNER JOIN C ON A.VAL2 = C.ID

Im assuming ALL ID values exist in BOTH B AND C otherwise you will have to change it slightly to use a LEFT JOIN and possibly default NULL values

SELECT A.ID, A.SIZE, ISNULL((A.SIZE*B.VALUE),0) AS CALC1, ISNULL((A.SIZE*C.VALUE),0) AS CALC2
FROM A
LEFT JOIN B ON A.VAL1 = B.ID
LEFT JOIN C ON A.VAL2 = C.ID

If you do it this way you can let the database do the work and not your application
0
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16790230
If you don't want to include the lookup fields to your select query then use FindKey method of TClientDataset it is very fast since it uses Indices to find record.
0
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16790299
Your OnCalcFields should look like the following. But sure it requires that the ID field on the cdsTableB and cdsTableC is indexed and set it as active Index

procedure OnCalcFields( DataSet: TDataSet );
begin
  with DataSet do
  begin
    cdsTableB.FindKey([FieldValues['VAL1']);
    FieldValues['CALC1'] := cdsTableB.FieldValues['VALUE'] * FieldValues['SIZE'];

    cdsTableC.FindKeu([FieldValues['VAL2'] );
    FieldValues['CALC2'] := cdsTableC.FieldValues['VALUE'] * FieldValues['SIZE'];
  end;
end;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Wyverex
ID: 16790755
Thanks for the fast answers.

@mikelittlewood:
That't the last option for me because I have about 6 calculated fields in A. The resulting query would neither have a good performance nor be nice for maintenance. But the worst is that users wouldn't see the new calculated values until they have applied all changes to the database.

@atul_parmar:
That already gave me a little performance boost. I reduced my OnCalcFields to one lookup per row and got about 2800 ms for the whole calculation with Locate and about 2000 ms with FindKey. (The test machine is quite slow with 800 MHz). But that's still not quite satisfying the more so as this was only one lookup instead of all 6.
Is there anything else I can do?
0
 
LVL 10

Expert Comment

by:atul_parmar
ID: 16790821
Yes,

Instead of selecting it from the query, add the cdsTableB.FieldValues['VALUE'] and cdsTableC.FieldValues['VALUE'] as Lookup column to your cdsTableA.  This way you need not to search the cdsTableB and cdsTableC

e.g.
procedure OnCalcFields( DataSet: TDataSet );
begin
  with DataSet do
  begin
    FieldValues['CALC1'] := FieldValues['VALUE_b'] * FieldValues['SIZE'];

    FieldValues['CALC2'] := FieldValues['VALUE_c'] * FieldValues['SIZE'];
  end;
end;

This will reduce the time taken by the OnCalcFields.
0
 
LVL 27

Accepted Solution

by:
BigRat earned 2000 total points
ID: 16791042
If table B and C are considerably smaller than A and don't change during the calculation, why notn cache them? Eg: when tables B and C are prices indexed by item number, you could use a string list.

If the number of items in B or C is less than a couple of hundred, I'd read them all out before starting the calculation.

0
 
LVL 7

Expert Comment

by:kfoster11
ID: 16794594
That's a nice thought BigRat, I was thinking the same thing.  Why not just load them into a TClientdataset and disconnect it that way all of the lookups are in memory and won't go back to the server.

0
 

Author Comment

by:Wyverex
ID: 16892370
Thanks for the feedback.
I cached the data but instead of a string list I used a hash table, since it is usually faster than a binary search.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 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