[yunqa.de] Using API calls for speed
- From: "Michael Hooker" <michael.hooker@xxxxxxxxxxxxxxxxxxxxx>
- To: <yunqa@xxxxxxxxxxxxx>
- Date: Sun, 11 May 2008 00:13:48 +0100
Ralf and all,
I use Delphi 7 PE, and WinXP SP2. I need to write a function to look up a
value in a sqlite3 database table column and return some of the data in the
cells of the corresponding row. The value looked up ('Hex' in the program
below) is present in the table >95% of the time, and if it is there it is
one of 50,000 unique values. The column is indexed. The input parameter to
the function is the string "Hex", and the result will be a comma-delimited
string: nice and simple.
The key factor is speed, as huge amounts of data are always flowing in; this
data is processed by commercial software and certain elements of it saved
into the sqlite3 database "BaseStation.sqb". My program processes the same
data in parallel (the commercial application sends it out to Port 30003) and
in real time, picking up some of the information the application fails to
record and ignoring the data which is of no interest to me. There is a lot
of processing to do, whilst keeping up with the constant inflow of data. I
should end up with (a) a display of current relevant data on the screen and
(b) a csv disk file of all the relevant information for the day, ready to
e-mail to my correspondents at the end of the day. There are other programs
occasionally accessing the database. My function needs to extract
information from the database as quickly as possible to keep up with the
flow of data through the port and I can't stress enough that there is a lot
of it - several Gigabytes a day! So I decided to try using the native API
for speed, I found it very difficult but following some of the demo
examples, this is what I came up with for test purposes. Clicking the
FindBtn sends the contents of an edit control to the function "GetAcDetails"
and the csv string is returned in another edit control. And it works! My
queries follow the code.
===========================================
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DISQLite3Api, DISQLite3Database, StdCtrls;
type
TForm1 = class(TForm)
FindBtn: TButton;
HexEd: TEdit;
procedure FindBtnClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
function GetAcDetails(Hex: String): String;
private
public
{ Public declarations }
end;
var
Form1: TForm1;
Stmt_H: TDISQLite3StatementHandle;
DB_H: TDISQLite3DatabaseHandle;
DBName: AnsiString;
SQL: String;
implementation
{$R *.dfm}
procedure TForm1.FormCreate(Sender: TObject);
begin
DBName := ExtractFilePath(Application.ExeName) + 'BaseStation.sqb';
end;
procedure TForm1.FindBtnClick(Sender: TObject);
begin
ResEd.Text := GetAcDetails(HexEd.Text);
end;
function TForm1.GetAcDetails(Hex: String): String;
Var
Res: Integer;
begin
sqlite3_check(sqlite3_open(PAnsiChar(DBName), @DB_H), DB_H);
SQL := 'SELECT Registration, Type, SerialNo, RegisteredOwners FROM
Aircraft WHERE ModeS = "' + HexEd.Text + '";';
sqlite3_check(sqlite3_prepare_v2(DB_H, PAnsiChar(SQL), -1, @Stmt_H, nil),
DB_H);
if Assigned(Stmt_H) then begin
Res := sqlite3_check(sqlite3_step(Stmt_H), DB_H);
if Res = 101 then Result := 'Not found,,,' {is this safe?}
else begin {make the csv
output string}
Result := sqlite3_column_str(Stmt_H, 0) + ',' +
sqlite3_column_str(Stmt_H, 1) + ',' +
sqlite3_column_str(Stmt_H, 2) + ',' +
sqlite3_column_str(Stmt_H, 3);
end;
sqlite3_check(sqlite3_finalize(Stmt_H), DB_H);
end;
sqlite3_check(sqlite3_close(DB_H), DB_H);
end;
end.
==================================================
QUERIES
First, after a bit of investigation, I found that the result of
sqlite3_check(sqlite3_step(Stmt_H), DB_H) always seems to be 100 if the
item is found and 101 if not. Is it safe to hard-code this into the function
to quickly indicate whether or not the item was found, as I have done?
Second, does using "sqlite3_check" provide any real benefit in this
scenario? It may just be wasting precious time.
Third, is sqlite3_step the right function to use when you only expect one
(or no) row to be returned? There is no "stepping" to do, is there?
Fourth, this function will be running 24/7 and mostly unattended. The last
thing I want is a message that pops up saying that the database is locked
and waiting for me to press a button to continue! I need something that
tries again until the database is unlocked and success is achieved. I have
a feeling I should use a try...except statement and a way of catching the
error and using it to repeat the call to the function. I see there is a
class that returns error numbers and descriptions (ESQLite3). However, in
15 years of messing about with Delphi I have never had to do anything like
this and have no idea where to start (the Help is not very enlightening). As
the result code for a Locked table seems to be 6, then my first instinct is
to say:
Res := sqlite3_check(sqlite3_open(PAnsiChar(DBName), @DB_H), DB_H);
If Res := 6 then begin
Result := 'Locked';
Exit; {I presume Exit works with functions, as with procedures}
end;
I don't have any way of testing this until the error arises! And of course
it would only work if I could somehow avoid the error message popping up.
Many thanks for any help you or others can offer, especially if the function
can be made to run faster.
Michael Hooker
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
