1. Technology

Speed Up Your FieldByName Expressions in Delphi Database Applications

By April 6, 2012

Follow me on:

in Delphi Database Programming :: Jens Borrisholt sent me his view on how one can speed up FieldByName calls in Delphi database applications.

By Jens: "I have my own little contribution to this, because if you had unlimited time to refactor code you can gain a lot of speed, doing things different .. But in a real life job, writing real lift Delphi code. You do not have unlimited time .. So therefor I written a small hack to the fieldByname, so you only add two lines of code to your application, and changed some small things then you would gain a lot of speed".

Read the full article to learn how to Fasten Up FieldByName

Related:

Comments
December 7, 2010 at 9:56 am
(1) Jens Borrisholt says:

Some real life tests :

ROWCOUNT = 100000

Test 1 : 4812 ms
Test 2 : 1204 ms
Test 3 : 7829 ms
Test 4 : 1984 ms

This was done on a Pentium 4 dual core 2,8 Ghz, 3 GB ram.

The project was compiled useing Delphi XE, so the string compare are slower compared to AnisiCode.

So YES Test 4 is 50% slower compared to Test 2, but who have the time to implement Test 2 ? And who cares about 0,01204 ms per loop vs 0,01984 ms per loop.?

December 7, 2010 at 10:51 am
(2) Jørn E. Angeltveit says:

By using persistent fields, you’re halfway to Test 2.

DB-aware components and some cut’n'paste + code-generation will take you all the way…

I really like your solution, though ;-)

December 7, 2010 at 10:59 am
(3) Supernova says:

Just to say. using a Memtable speed up a lot the fieldbyname test : . Test number 2 with 100000 in rowcount = 4 seconds with clientdataset, with kbmMemtable = 610 milisseconds!

December 7, 2010 at 8:12 pm
(4) Alexandre Machado says:

This is the best solution I’ve seen so far. But I still prefer my own home made solution, although it can’t be applied by everyone:
Replace AnsiCompareText and AnsiCompareStr in runtime by the FastCode CompareText and CompareStr functions (there is no need to consider current locale in my applications).
Zero refactoring! Zero change in your code and your app is doing FindField, FieldByName, TStringList.IndexOf, etc etc etc much faster, instantly!

December 8, 2010 at 2:16 am
(5) Jens Borrisholt says:

@Supernova by useing TKmbMemtable just makes it even more urgent to avoid Test 1 :
Here are some test result useing TkbmMemtable. The program was compiled in Delphi 2007.

ROWCOUNT = 1000000;

TEST 1 : 78780 ms
TEST 2 : 5163 ms
TEST 3 : 18128 ms
TEST 4 : 6740 ms

@Alexandre .. Code injection … I havnt thought of id .. So I tried it .

Test 1 useing CompareText and CompareStr 56456 ms
So this is not a solution to the fieldByName problem.

Jens B

December 8, 2010 at 3:39 am
(6) Alexandre Machado says:

@Jens:
I have a different result, Jens.
I did a simple benchmark application. A real world application table with 129 fields (!), in a 10000 pass loop, like that:

procedure FieldByNameTest(DataSet: TDataSet; FieldName: string);
var
Value: string;
i: Integer;
begin
for i := 1 to 10000 do
Value := DataSet.FieldByName(FieldName).AsString;
end;

Note that there is no cursor scrolling and the DataSet is already active, so there is no big difference with different DataSet descendants (ClientDataSet, ADODataSet, MemTable, etc.). Most of the time is really spent inside FieldByName calls.

Results in the worst case scenario (FieldName = Last field in the DataSet):
with original VCL AnsiCompareText: 5.8 seconds
with AnsiCompareText replaced by FastCode CompareText: 0.045 seconds!

This is more than 100 times faster.

What I did: Used the same FastCode approach (check FastCode.pas) and replaced AnsiCompareText by its own CompareText. I can’t see why it wouldn’t work.

December 9, 2010 at 12:16 am
(7) Jens Borrisholt says:

@Alexandre

Could you please be a little more specific ..

I forund the Fastcode.pas unit :

http://www.koders.com/delphi/fid9F30A62180C16DB5336200E91CC12454FD65C06F.aspx?s=pos#L36

But that didn’t help me.

You could also email me direct : Jens@_NOSPAM_borrisholt.dk

Jens Borrisholt

December 10, 2010 at 5:16 am
(8) Alexandre Machado says:

Hi jens, I sent you today the final version of my customized FastCode library, replacing AnsiCompareText and AnsiCompareStr by FastCode’s CompareText and CompareStr. Please let me know the results.
Regards

April 7, 2012 at 9:05 am
(9) EMB says:

Hey! What was updated in this article since 2011?
I’ve failed in find it.

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>

©2014 About.com. All rights reserved.