Hello,Why didn't you select a modern, robust, reliable, more flexible and faster solution such as Firebird embedded, SQLite, etc?
I am programming a fluid properties calculator, which interpolates from a lookup table, and [b[I selected xBase as database system for storing the data[/b].
The original data is based on a 2-D(pressure and temperature) lookup table. I re-arranged it to a long list and saved it to a DBF file.
In general cases, a state with pressure P & temperature T would be enveloped by 4 neighbor points: PLTL,PLTH,PHTL, and PHTH.(L for lower, H for higher)You should pick faster options than xBase.
I write the following code for test:I can get the correct result from the above code. The only issue is that the execution speed. On my laptop it takes more than 1 second(T2-T1) to perform once.
var DataDBF:TDBF; P,T,PL,PH:Double; //Fluid states PLTL,PLTH,PHTL,PHTH:Integer; //Record number of neighbor points T1,T2:Integer; //For executing time measurement begin DataDBF:=TDbf.Create(Nil); DataDBF.FilePathFull:=ExtractFilePath(ParamStr(0)); DataDBF.ReadOnly:=True; DataDBF.TableName:='Sample.DBF'; DataDBF.TableLevel:=4; DataDBF.Open; P:=37.945; T:=-62.066; T1:=GetTickCount(); DataDBF.Filter:='P>='+floattostr(P); DataDBF.Filtered:=True; DataDBF.First; //This line relies on the raw data sequence. I feel that it's not so ideal, but I don't know how to get the minimum P from the search result.... :( PH:=DataDBF.FieldByName('P').AsFloat; DataDBF.Filter:='P<='+floattostr(P); DataDBF.Filtered:=True; DataDBF.Last; PL:=DataDBF.FieldByName('P').AsFloat; DataDBF.Filter:='P='+floattostr(PL)+' and T<='+floattostr(T); DataDBF.Filtered:=True; DataDBF.Last; PLTL:=DataDBF.PhysicalRecNo; DataDBF.Filter:='P='+floattostr(PL)+' and T>='+floattostr(T); DataDBF.Filtered:=True; DataDBF.First; PLTH:=DataDBF.PhysicalRecNo; DataDBF.Filter:='P='+floattostr(PH)+' and T<='+floattostr(T); DataDBF.Filtered:=True; DataDBF.Last; PHTL:=DataDBF.PhysicalRecNo; DataDBF.Filter:='P='+floattostr(PH)+' and T>='+floattostr(T); DataDBF.Filtered:=True; DataDBF.First; PHTH:=DataDBF.PhysicalRecNo; T2:=GetTickCount(); DataDBF.Close; DataDBF.Free; end;
I tried using index by P(Primary), but the result is not correct. Is there any suggestion for improving it?Yes, use Integer number as Primary Key.
The sample DBF file can be downloaded here:Sorry, It's been a long time that I just migrate xBase to reliable solutions.
https://drive.google.com/file/d/1bGrwKyE5oMHsmdxnpDyHh4507-c_QSW1/view?usp=sharing
BTW, does anyone have recommend website for Tdbf tips? I just know the official site and Lazarus tutorial.
Thanks!
Chen, Yu-Chih
(Lazarus 1.8.4 official release)
OMG!! How could it happen? :oAgain, please, forget about xBase and MS Access and move to something better.
Before your example, I deeply believed that the benefit of database manage system is to search data corresponding to the expression efficiently through some internal special algorithm or structure. If it is (much) slower than the whole file scanning, the database becomes just a storing media.
Could anyone make a brief explanation? I must misunderstand something...Still talking about xBase, Primary Key should be of an Integer type.
Thank you Handoko. Your post changed my concept.
Regards,
ChenYuChih
Okay, it seems that I selected a database which is not so fit my application.There are better Pascal in-memory database alternatives such as TBufDataset or ZMSQL, among others:
The reason why I choose Tdbf is just because it's full sourced in pascal and doesn't need any other binary library, no matter linking statically or dynamically. This feature is very attractive to me.
Thanks for your advise, valdir.marcos. I will evaluate another one.
Sincerely,
ChenYuChih
You use a filtering process to find the neighboring records; this returns a lot of unneeded records, and I think copying these records to the result set is the reason why your code is so slow.
To decide how this can be avoided please answer these questions:
- Are the pressure and temperature values of the table always equally spaced and ordered like in the sample dbf file, and do you know the spacing?
- Is the point for which you seek the neighbors an element of the table or somewhere between the grid points?
the 4 neighbor points on P-T plane may even not be a rectangle.Then the problem is different. You need to find the three (or four) P-T points among the given ones which have the closest distance to the given P-T point. Because pressure and temperature have different units and cover different ranges I'd normalize them at first to the range 0..1. Then I'd calculate the "distance" of each record point from the P-T point, add a column for that and sort the table by this distance column. The first three (or four) points will be the ones that you seek.
I am programming a fluid properties calculator, which interpolates from a lookup table, and I selected xBase as database system for storing the data.
The original data is based on a 2-D(pressure and temperature) lookup table. I re-arranged it to a long list and saved it to a DBF file.