Lazarus
Programming => Databases => Topic started by: Knipfty on August 30, 2012, 06:54:56 pm
-
Hi,
If I assign a DataSet.Fields[0] object to a TField object which then processes faster?
Procedure MyTest;
var
MyField: TField;
S: string;
begin
DataSet.Active := true;
MyField := DataSet.Fields[0];
DataSet.First;
while not DataSet.EOF do
begin
S: = DataSet.Fields[0].AsString;
// or
S: = MyField.AsString;
DataSet.Next
end;
DataSet.Active := false
end;
Or are they about the same?
Thanks
Knipfty
-
I would imagine the compiler could optimize that as you are using a fixed index number (0). Don't know whether it does.
Disclaimer: definitely not a compiler expert here ;)
-
The key here is that both are pointing to the same object. So which one resolves faster? I know FieldByName is much slower. I actually have code that looks like this right now:
LeftStr(DS.Fields[Fld[0]].AsString,L[0])
and am wondering if this would be any faster
LeftStr(MyField.AsString,L[0])
Assuming that I assigned MyField like the above example.
-
I'm guessing that the difference in execution time is neglectable because of the slowness of the disk I/O.
-
You save a call to GetField when doing S: = MyField.AsString; The compiler doesn't optimise it away (on win32 at least). So it is faster. You'll notice the difference when all the data are already in memory (TSQlQuery.PacketRecords=-1 or in memory datasets)
-
MyField.AsString is faster. Dataset.fields[0].AsString requires searching.
Another optimalisation: MyField.Value is faster than asString because asString require checking and converting. If you know if the field is string or integer, it's faster to use the content of the field as such.
-
@teos
If you know if the field is string or integer, it's faster to use the content of the field as such.
What would this look like? I am only familiar with .AsString or .AsInteger. Are you referring to .Text or .Value?
-
In short doing DataSet.Fields[0].AsString will execute the getter function to find and return the field at index 0 this is fast enough to not be noticed but it it faster if you do keep a reference of the field and not execute one more function inside the loop.
As for the .AsString or .AsInteger or .Value it is faster to use the method that returns the natural data directly so for a string field .AsString will always be faster than .Value since .Value converts the data in to a variant as well (ee calls a convertion function every time you do VariantVariable := StringVariable), for an integer field .AsInteger is faster etc..
-
Hi taazz,
I didn't think that .Value was the way to go. I need the data in String format, so .AsString will be just fine. I've converted all by .FieldByName that are in loops over to TField.
Knipfty
-
I tested those differnet approaches long time ago with delphi 7.
I agree than TField as Tdatatype is faster than DataSet.Fielda[index].AsDataType and the worst of them is using .Value, except in a special times, when is better option leave convertion tasks to the compiler.
Anyway if you are using remote databases, you will get slow access times.
But there are a couple things than really makes a difference:
1) Disable any TDataset controls before use TFields, and enable them after task are completed.
2) Mix wisely direct SQL commands with TFields tasks.
-
If you know the type of field, you can also map the fields to there proper persistent field type.
eg.
var
MyField:TStringField;
S:string;
begin
MyField := dataset.fieldbyname('somestringfield') as TStringField;
//some tight loop
..
S := MyField.Value; //here you can still use value, it's no longer a variant.
end;
Advantages here is that you can now use Value again, it maps directly to a String now.
Also handy for BlobFields, as you will also get LoadFromFile / LoadFromStream etc..
-
If you know the type of field, you can also map the fields to there proper persistent field type.
eg.
var
MyField:TStringField;
S:string;
begin
MyField := dataset.fieldbyname('somestringfield') as TStringField;
//some tight loop
..
S := MyField.Value; //here you can still use value, it's no longer a variant.
end;
Advantages here is that you can now use Value again, it maps directly to a String now.
Also handy for BlobFields, as you will also get LoadFromFile / LoadFromStream etc..
But it will cost you speed again, because of the search.
If I'm correct, fields are accessible as objects. Dataset1Stringfield1.Text is shorter.
-
But it will cost you speed again, because of the search.
There is no speed problems, how you assign to your persistent fields is up to you.
I assign all fields at the beginning, so it's only done once, my example showed you inside the procedure, but this was only for simplicity, but even here inside the tight loop you are going to be getting Full Speed, there is no search.
-
OK. So here is a stupid question. I've now seen in this thread, TFloatField and TStringField. Is there a T<fieldtype>Field defined for each ft<type>, as in ftFloat and ftString? And are all these documented on one page somewhere?
I've found this link http://www.freepascal.org/docs-html/fcl/db/tfieldtype.html (http://www.freepascal.org/docs-html/fcl/db/tfieldtype.html) but no summary of T<fieldtype>Field.
Thanks
-
All basic field classes are defined in the DB.pas unit, now how they are mapped to the TfieldType enum is something only the database specific components would know. In order to apply their own translation of TFieldType to TfieldClass they have to override the GetFieldClass method of TDataset and there is a default mapping which is defined as a const array inside the DB.pas unit with the name DefaultFieldClasses. It is already in the interface section of the unit so you have access to it from your code if that is required.
-
I've found this link http://www.freepascal.org/docs-html/fcl/db/tfieldtype.html but no summary of T<fieldtype>Field.
On that page click on [index] and you get all the T<fieldtype>Fields under the letter T (http://www.freepascal.org/docs-html/fcl/db/index-8.html)
-
OK. I've looked over db.pas. Very interesting. I've found this mapping:
const
DefaultFieldClasses : Array [TFieldType] of TFieldClass =
( { ftUnknown} Tfield,
{ ftString} TStringField,
{ ftSmallint} TSmallIntField,
{ ftInteger} TLongintField,
{ ftWord} TWordField,
{ ftBoolean} TBooleanField,
{ ftFloat} TFloatField,
{ ftCurrency} TCurrencyField,
{ ftBCD} TBCDField,
{ ftDate} TDateField,
{ ftTime} TTimeField,
{ ftDateTime} TDateTimeField,
{ ftBytes} TBytesField,
{ ftVarBytes} TVarBytesField,
{ ftAutoInc} TAutoIncField,
{ ftBlob} TBlobField,
{ ftMemo} TMemoField,
{ ftGraphic} TGraphicField,
{ ftFmtMemo} TBlobField,
{ ftParadoxOle} TBlobField,
{ ftDBaseOle} TBlobField,
{ ftTypedBinary} TBlobField,
{ ftCursor} Nil,
{ ftFixedChar} TStringField,
{ ftWideString} TWideStringField,
{ ftLargeint} TLargeIntField,
{ ftADT} Nil,
{ ftArray} Nil,
{ ftReference} Nil,
{ ftDataSet} Nil,
{ ftOraBlob} TBlobField,
{ ftOraClob} TMemoField,
{ ftVariant} TVariantField,
{ ftInterface} Nil,
{ ftIDispatch} Nil,
{ ftGuid} TGuidField,
{ ftTimeStamp} Nil,
{ ftFMTBcd} TFMTBCDField,
{ ftFixedWideString} TWideStringField,
{ ftWideMemo} TWideMemoField
);
What I also see is that even if you define a field as either TField or TStringField, they look like they are equivalent as far as these calls go, TField.AsString / TStringField.Value or TStringField.AsString. In that they all call the property "function GetAsString: string; override;"
So assuming that you are not using anything special in the T<fieldtype>Field, like extra properties or methods, then it really doesn't make any difference, except perhaps your code is a little easier to read.
-
OK. I've looked over db.pas. Very interesting. I've found this mapping:
That's the default mapping, If I remember correctly you are using advandage component set I would suggest to take a look on those component's GetFieldClass method. This will give you the correct mapping for your components.
What I also see is that even if you define a field as either TField or TStringField, they look like they are equivalent as far as these calls go, TField.AsString / TStringField.Value or TStringField.AsString. In that they all call the property "function GetAsString: string; override;"
That is by design if you take a closer look to other field types you will see the same pattern eg TIntegerField.Value uses the Set/GetAsLongint methods. It seems they have been designed with speed in mind.
So assuming that you are not using anything special in the T<fieldtype>Field, like extra properties or methods, then it really doesn't make any difference, except perhaps your code is a little easier to read.
True, if you use the type specific fields there is no difference if you use .Value or .As<NaturalType> methods.
-
@Knipfty, Yes,
But TField.Value <> TStringField.Value.
The main advantage to using the correct TField type is that you get proper type checking at compile time. So at compile time you couldn't say assign a TStringField to an an integer, if you use the .Value, but if you use the .AsInteger it will compile, but potentially give runtime errors. So if you stick to .Value and T(Type)Field then you have full type checking.
-
Yep, tweaked my code a little bit to use TStringField instead of TField.
I'm glad I asked my original question which then lead to this result.
Great thread everyone. Thanks!
Knipfty